Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id F381C9FA136 for ; Fri, 1 Dec 2006 20:43:13 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-new, port 10024) with ESMTP id 77544-07 for ; Fri, 1 Dec 2006 20:43:04 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.170]) by postgresql.org (Postfix) with ESMTP id 0BF899F942F for ; Fri, 1 Dec 2006 20:43:08 -0400 (AST) Received: by ug-out-1314.google.com with SMTP id k3so2322832ugf for ; Fri, 01 Dec 2006 16:43:07 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:references:x-google-sender-auth; b=rxmXW4Bi0Qr2oCYuFdYTc+YxkyaVFYEG6V6NpKHrvJlnfq53yu2kaHn/rWm82hGCFTi8bI+Qkb8IwlKdnjIjMyqBS3XrIvX+T5EtuzGemVSA+xJzZpCn6yJqbeJo7u7gKc92aKnEjWQ/ZJsO2J97N9XNG4wn3NCb31zRlyD0YTY= Received: by 10.78.20.13 with SMTP id 13mr5453675hut.1165020186166; Fri, 01 Dec 2006 16:43:06 -0800 (PST) Received: by 10.78.136.6 with HTTP; Fri, 1 Dec 2006 16:43:05 -0800 (PST) Message-ID: Date: Fri, 1 Dec 2006 18:43:05 -0600 From: "Aaron Bono" To: "Markus Juenemann" Subject: Re: Setting boolean column based on cumulative integer value Cc: pgsql-sql@postgresql.org In-Reply-To: <9d8bdcf30612010550o3a5721lefc366e4984fd145@mail.gmail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_6728_16915390.1165020185747" References: <9d8bdcf30612010550o3a5721lefc366e4984fd145@mail.gmail.com> X-Google-Sender-Auth: 18e5f5790774cfd4 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200612/16 X-Sequence-Number: 26972 ------=_Part_6728_16915390.1165020185747 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline On 12/1/06, Markus Juenemann wrote: > > Hi (again!) > > [stupid email program sent my message before I finished it!!!] > > I've got a bit of a tricky (for me!) problem. The example below is > completely ficticious but > describes my real problem in a way which might be easier to understand. > > Imagine the table contains a list of passenger wanting to get on a > small(!) plane. > The plane can carry at most 200kg of passengers and will be filled > strictly on a first-come > first-serve basis - well, check-in staff is a bit stupid ;-). So what > needs to be done is to set the 'gets_seat' column to true until the > weight limit is reached. > > CREATE TABLE passenger_queue ( > id serial NOT NULL, > name character varying(40) NOT NULL, > weight integer NOT NULL, > gets_seat boolean default false > ) > > insert into passenger_queue values (1,"Peter",75,false) > insert into passenger_queue values (2,"Mary",50,false) > insert into passenger_queue values (3,"John",70,false) > insert into passenger_queue values (4,"Steve",80,false) > > According to the specifications given above Peter, Mary and John would > have 'gets_seat' > set to true because their cumulative weight is 195kg while Steve misses > out. > > The big question is: How can I do this in a nice SQL query??? I would ditch the gets_seat column and instead create a view that calculates the value when you need it. This helps eliminate redundant data. CREATE VIEW passenger_queue_vw ( id, name, weight, gets_seat ) AS SELECT queue.id, queue.name, queue.weight, CASE sum(others.gets_seat) <= 200 FROM passenger_queue queue INNER JOIN passenger_queue others ON ( others.id <= queue.id -- There should really be a create date used here -- but for example purposes I assume the id column -- is an increasing sequence ) GROUP BY queue.id, queue.name, queue.weight If you have performance concerns you can create a materialized view. Of course if you don't want the record to even be allowed (cause an error on insert), you should use a constraint as mentioned in one of the other responses to your question. -Aaron -- ================================================================== Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com http://codeelixir.com ================================================================== ------=_Part_6728_16915390.1165020185747 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline On 12/1/06, Markus Juenemann <markus@juenemann.net> wrote:
Hi (again!)

[stupid email program sent my message before I finished it!!!]

I've got a bit of a tricky (for me!) problem. The example below is
completely ficticious but
describes my real problem in a way which might be easier to understand.

Imagine the table contains a list of passenger wanting to get on a
small(!) plane.
The plane can carry at most 200kg of passengers and will be filled
strictly on a first-come
first-serve basis - well, check-in staff is a bit stupid ;-). So what
needs to be done is to set the 'gets_seat' column to true until the
weight limit is reached.

CREATE TABLE passenger_queue (
id serial NOT NULL,
name character varying(40) NOT NULL,
weight integer NOT NULL,
gets_seat boolean default false
)

insert into passenger_queue values (1,"Peter",75,false)
insert into passenger_queue values (2,"Mary",50,false)
insert into passenger_queue values (3,"John",70,false)
insert into passenger_queue values (4,"Steve",80,false)

According to the specifications given above Peter, Mary and John would
have 'gets_seat'
set to true because their cumulative weight is 195kg while Steve misses out.

The big question is: How can I do this in a nice SQL query???

I would ditch the gets_seat column and instead create a view that calculates the value when you need it.  This helps eliminate redundant data.

CREATE VIEW passenger_queue_vw (
    id,
    name,
    weight,
    gets_seat
) AS
SELECT
    queue.id,
    queue.name,
    queue.weight ,
    CASE sum(others.gets_seat) <= 200
FROM passenger_queue queue
INNER JOIN passenger_queue others ON (
    others.id <= queue.id
    -- There should really be a create date used here
    -- but for example purposes I assume the id column
    -- is an increasing sequence
)
GROUP BY
    queue.id,
    queue.name,
    queue.weight

If you have performance concerns you can create a materialized view.  Of course if you don't want the record to even be allowed (cause an error on insert), you should use a constraint as mentioned in one of the other responses to your question.

-Aaron

--
==================================================================
   Aaron Bono
   Aranya Software Technologies, Inc.
   http://www.aranya.com
   http://codeelixir.com
================================================================== ------=_Part_6728_16915390.1165020185747--