From: | Gary Stainburn <gary(dot)stainburn(at)ringways(dot)co(dot)uk> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Trigger/Function - one solution - was constraint question (I think) |
Date: | 2008-12-04 17:59:48 |
Message-ID: | 200812041759.48987.gary.stainburn@ringways.co.uk |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Sean Davis | 2008-12-04 18:01:49 | Aggregates with NaN values |
Previous Message | Achilleas Mantzios | 2008-12-04 15:02:18 | array index access outside range |