pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSetting boolean column based on cumulative integer value
4+ messages / 4 participants
[nested] [flat]
* Setting boolean column based on cumulative integer value
@ 2006-12-01 13:50 Markus Juenemann <markus@juenemann.net>
2006-12-01 14:44 ` Re: Setting boolean column based on cumulative integer value A. Kretschmer <andreas.kretschmer@schollglas.com>
2006-12-01 14:48 ` Re: Setting boolean column based on cumulative integer value Richard Broersma Jr <rabroersma@yahoo.com>
2006-12-02 00:43 ` Re: Setting boolean column based on cumulative integer value Aaron Bono <postgresql@aranya.com>
0 siblings, 3 replies; 4+ messages in thread
From: Markus Juenemann @ 2006-12-01 13:50 UTC (permalink / raw)
To: pgsql-sql
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???
Thanks
Markus
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Setting boolean column based on cumulative integer value
2006-12-01 13:50 Setting boolean column based on cumulative integer value Markus Juenemann <markus@juenemann.net>
@ 2006-12-01 14:44 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2 siblings, 0 replies; 4+ messages in thread
From: A. Kretschmer @ 2006-12-01 14:44 UTC (permalink / raw)
To: pgsql-sql
am Sat, dem 02.12.2006, um 0:50:37 +1100 mailte Markus Juenemann folgendes:
> 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.
With your example, i wrote a little function for this:
---%<------
create or replace function check_wight( out id int,
out name text,
out weight int,
out gets_seat boolean ) returns setof record as $$
declare rec record;
sum int;
begin
sum = 0;
for rec in select * from passenger_queue order by id LOOP
id = rec.id;
name = rec.name;
weight = rec.weight;
sum = sum + weight;
if sum < 200 then
gets_seat='t'::bool;
else
gets_seat='f'::bool;
end if;
return next ;
end loop;
end
$$ language plpgsql;
---%<------
test=# select * from passenger_queue;
id | name | weight | gets_seat
----+-------+--------+-----------
1 | Peter | 75 | f
2 | Mary | 50 | f
3 | John | 70 | f
4 | Steve | 80 | f
(4 rows)
test=# select * from check_wight();
id | name | weight | gets_seat
----+-------+--------+-----------
1 | Peter | 75 | t
2 | Mary | 50 | t
3 | John | 70 | t
4 | Steve | 80 | f
(4 rows)
HTH, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47215, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Setting boolean column based on cumulative integer value
2006-12-01 13:50 Setting boolean column based on cumulative integer value Markus Juenemann <markus@juenemann.net>
@ 2006-12-01 14:48 ` Richard Broersma Jr <rabroersma@yahoo.com>
2 siblings, 0 replies; 4+ messages in thread
From: Richard Broersma Jr @ 2006-12-01 14:48 UTC (permalink / raw)
To: Markus Juenemann <markus@juenemann.net>; pgsql-sql
> 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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Setting boolean column based on cumulative integer value
2006-12-01 13:50 Setting boolean column based on cumulative integer value Markus Juenemann <markus@juenemann.net>
@ 2006-12-02 00:43 ` Aaron Bono <postgresql@aranya.com>
2 siblings, 0 replies; 4+ messages in thread
From: Aaron Bono @ 2006-12-02 00:43 UTC (permalink / raw)
To: Markus Juenemann <markus@juenemann.net>; +Cc: pgsql-sql
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
==================================================================
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2006-12-02 00:43 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-12-01 13:50 Setting boolean column based on cumulative integer value Markus Juenemann <markus@juenemann.net>
2006-12-01 14:44 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2006-12-01 14:48 ` Richard Broersma Jr <rabroersma@yahoo.com>
2006-12-02 00:43 ` Aaron Bono <postgresql@aranya.com>
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