[SQL] constraint question (I think)
Hi folks. I'm trying to model a vehicle compound structure and have the following: Four seperate compounds, North, East, South and West, Each compound has a number of rows with Labels fastened to the fences As the compounds are not perfect rectangles, the rows are of differing lengths. I have tables for the compounds and rows, but can't work out how to set the constraints on the bays table - a seperate table while development takes place, merged with our existing stock table eventually; I've managed to add the constraint to prevent compound_bay records being created for compound rows that do not exist, My question is, how do I prevent a record being created for a bay higher than than the length of the row, i.e. > 20 in North row A or > 40 in East row A? -- list of available compounds create table compounds ( co_id int4 primary key, co_name varchar(80) ); -- description of rows per compound create table compound_rows ( co_id int4 references compounds(co_id), cr_id int4 not null, cr_label varchar(5) not null, cr_sequence int4 not null, cr_length int4, primary key (co_id, cr_id) ); create unique index "compound_rows_sequence_index" on compound_rows using btree (co_id, cr_sequence); create unique index "compound_rows_label_index" on compound_rows using btree (co_id, cr_label); COPY compounds (co_id,co_name) from stdin; 1 North 2 East 3 South 4 West \. COPY compound_rows (co_id,cr_id,cr_label,cr_sequence,cr_length) from stdin; 1 1 A 10 20 1 2 B 20 20 1 3 C 30 25 1 4 D 40 25 2 1 A 10 40 2 2 B 20 40 2 3 C 30 45 2 4 D 40 45 \. -- link from stock table one bay per vehicle create table compound_bays ( st_VINvarchar(17) primary key references stock (st_VIN), co_id int4 not null, cr_id int4 not null, cb_id int4 not null ); alter table compound_bays add constraint compound_bays_row_check foreign key (co_id, cr_id) references compound_rows(co_id,cr_id); -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] array index access outside range
Hello list,
Let's suppose that we have an inteher array "parents"
select parents from machdefs where defid=888;
{1,2,3}
Currently accessing parents[0] returns a null, which is reasonable,
and i am thinking of relying on this assumption.
The question is, will it be safe to do that?
Is there any thoughts/needs/discussions of changing this behaviour in a future
version
thus breaking my program?
Thanx a lot
--
Achilleas Mantzios
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Trigger/Function - one solution - was constraint question (I think)
I have managed to develop one solution using functions and triggers. Has anyone got a better solution? Gary create unique index "compound_bays_unique_index" on compound_bays using btree (co_id,cr_id,cb_id); create or replace function compound_rows_range_check() returns trigger as $proc$ DECLARE BAYNO int4; BEGIN -- if changing compound or row fail IF NEW.co_id <> OLD.co_id THEN RAISE EXCEPTION 'cannot change compound id'; END IF; IF NEW.cr_id <> OLD.cr_id THEN RAISE EXCEPTION 'cannot change row id'; END IF; SELECT cb_id into BAYNO from compound_bays where co_id = NEW.co_id and cr_id = NEW.cr_id and cb_id > NEW.cr_length order by cb_id desc limit 1; IF found THEN RAISE EXCEPTION 'Cannot remove occupied bays: % > %', BAYNO, NEW.cr_length; END IF; RETURN NEW; END; $proc$ LANGUAGE plpgsql; CREATE TRIGGER compound_rows_range_check BEFORE UPDATE on compound_rows FOR EACH ROW EXECUTE PROCEDURE compound_rows_range_check(); create or replace function compound_bays_range_check() returns trigger as $proc$ DECLARE ROWLENGTH int4; BEGIN SELECT cr_length into ROWLENGTH from compound_rows where co_id = NEW.co_id and cr_id = NEW.cr_id; IF not found THEN RAISE EXCEPTION 'Compound / Row not found'; END IF; IF NEW.cb_id > ROWLENGTH THEN RAISE EXCEPTION 'row length exceeded: % > %', NEW.cb_id,ROWLENGTH; END IF; RETURN NEW; END; $proc$ LANGUAGE plpgsql; CREATE TRIGGER compound_bays_range_check BEFORE INSERT OR UPDATE on compound_bays FOR EACH ROW EXECUTE PROCEDURE compound_bays_range_check(); -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Aggregates with NaN values
I am happy to see NaN and infinity handled in input. I would now like to compute aggregates (avg, min, max, etc) on columns with NaN values in them. The standard behavior (it appears) is to have the aggregate return NaN if the data contain one-or-more NaN values. I am used to using coalesce with NULL values, but that doesn't work with NaN. I can deal with these using CASE statuement to assign a value, but is there a standard way of dealing with the NaN (or Infinity, for that matter) cases to get a behvavior where they are "ignored" by an aggregate? Thanks, Sean -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Trigger/Function - one solution - was constraint question (I think)
I would develop like this (No so many changes, it is basically a small trigger) create or replace function compound_rows_range_check() returns trigger as $body$ DECLARE BAYNO int4; BEGIN -- First Verification = if changing compound or row fail IF (old.co_id <> new.co_id or old.cr_id <> new.cr_id) THEN RAISE EXCEPTION 'Cannot change co_id () | cr_id ()', old.co_id, old.cr_id; END IF; -- Last Verification SELECT cb_id into BAYNO from compound_bays where co_id = NEW.co_id and cr_id = NEW.cr_id and cb_id > NEW.cr_length order by cb_id desc limit 1; IF (FOUND) THEN RAISE EXCEPTION 'Cannot remove occupied bays: % > %', BAYNO, NEW.cr_length; END IF; RETURN NEW; END; $body$ LANGUAGE 'plpgsql'; 2008/12/4 Gary Stainburn <[EMAIL PROTECTED]> > I have managed to develop one solution using functions and triggers. Has > anyone got a better solution? > > Gary > > create unique index "compound_bays_unique_index" on compound_bays using > btree > (co_id,cr_id,cb_id); > > create or replace function compound_rows_range_check() returns trigger as > $proc$ > DECLARE > BAYNO int4; > BEGIN > -- if changing compound or row fail > IF NEW.co_id <> OLD.co_id THEN >RAISE EXCEPTION 'cannot change compound id'; > END IF; > IF NEW.cr_id <> OLD.cr_id THEN >RAISE EXCEPTION 'cannot change row id'; > END IF; > SELECT cb_id into BAYNO from compound_bays where > co_id = NEW.co_id and > cr_id = NEW.cr_id and > cb_id > NEW.cr_length > order by cb_id desc > limit 1; > IF found THEN >RAISE EXCEPTION 'Cannot remove occupied bays: % > %', > BAYNO, NEW.cr_length; > END IF; > RETURN NEW; > END; > $proc$ LANGUAGE plpgsql; > > CREATE TRIGGER compound_rows_range_check BEFORE UPDATE on compound_rows > FOR EACH ROW EXECUTE PROCEDURE compound_rows_range_check(); > > create or replace function compound_bays_range_check() returns trigger as > $proc$ > DECLARE > ROWLENGTH int4; > BEGIN > SELECT cr_length into ROWLENGTH from compound_rows where > co_id = NEW.co_id and > cr_id = NEW.cr_id; > IF not found THEN >RAISE EXCEPTION 'Compound / Row not found'; > END IF; > IF NEW.cb_id > ROWLENGTH THEN >RAISE EXCEPTION 'row length exceeded: % > %', > NEW.cb_id,ROWLENGTH; > END IF; > RETURN NEW; > END; > $proc$ LANGUAGE plpgsql; > > CREATE TRIGGER compound_bays_range_check BEFORE INSERT OR UPDATE on > compound_bays > FOR EACH ROW EXECUTE PROCEDURE compound_bays_range_check(); > > > > -- > Gary Stainburn > > This email does not contain private or confidential material as it > may be snooped on by interested government parties for unknown > and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 > > -- > Sent via pgsql-sql mailing list ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql >
