From: Richard Broersma Jr <rabroersma@yahoo.com>
To: Markus Juenemann <markus@juenemann.net>
To: pgsql-sql@postgresql.org
Subject: Re: Setting boolean column based on cumulative integer value
Date: Fri, 1 Dec 2006 06:48:02 -0800 (PST)
Message-ID: <847705.487.qm@web31801.mail.mud.yahoo.com> (raw)
In-Reply-To: <9d8bdcf30612010550o3a5721lefc366e4984fd145@mail.gmail.com>
> 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???
Well there are two ways that I can think of:
The first option is probably the best. But the second is a good mental exercise.
1) a trigger that checks to insure that a new record doesn't exceed your max.
2) instead of inserting passenger weight you could insert begin/end weight range for each
passenger. i.e.:
CREATE TABLE passenger_queue (
id serial NOT NULL,
name character varying(40) NOT NULL,
plane_start_weight integer NOT NULL,
plane_end_weight integer not null,
constraint
plane_max_wieght
check( plane_end_weight <= 200 ),
constraint
sanity_check
check( plane_end_weight > plane_start_weight)
)
insert into passenger_queue values (1,"Peter",
(select max(end_weight) from passenger_queue),
(select max(end_weight) from passenger_queue) + 75);
insert into passenger_queue values (2,"Mary",
(select max(end_weight) from passenger_queue),
(select max(end_weight) from passenger_queue) + 50);
insert into passenger_queue values (3,"John",
(select max(end_weight) from passenger_queue),
(select max(end_weight) from passenger_queue) + 70);
insert into passenger_queue values (4,"Steve",
(select max(end_weight) from passenger_queue),
(select max(end_weight) from passenger_queue) + 80);
once you try to insert a record that exceeds your max weight the insert will fail.
ofcourse if you have to delete a passenger record because he/she wishes to get off early you will
need to have an additional update statement to shift down higher valued records insure that the
range does not have any gaps.
Regards,
Richard Broersma Jr.
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: rabroersma@yahoo.com, markus@juenemann.net
Subject: Re: Setting boolean column based on cumulative integer value
In-Reply-To: <847705.487.qm@web31801.mail.mud.yahoo.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