From: Aaron Bono <postgresql@aranya.com>
To: Markus Juenemann <markus@juenemann.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: Setting boolean column based on cumulative integer value
Date: Fri, 1 Dec 2006 18:43:05 -0600
Message-ID: <bf05e51c0612011643u5fbdbfa3p3ea8ea4d97e614c4@mail.gmail.com> (raw)
In-Reply-To: <9d8bdcf30612010550o3a5721lefc366e4984fd145@mail.gmail.com>
References: <9d8bdcf30612010550o3a5721lefc366e4984fd145@mail.gmail.com>
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.comhttp://codeelixir.com
==================================================================
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: postgresql@aranya.com, markus@juenemann.net
Subject: Re: Setting boolean column based on cumulative integer value
In-Reply-To: <bf05e51c0612011643u5fbdbfa3p3ea8ea4d97e614c4@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox