Re: Trigger/Function - one solution - was constraint question (I think) - Mailing list pgsql-sql

From Rafael Domiciano
Subject Re: Trigger/Function - one solution - was constraint question (I think)
Date
Msg-id 3a0028490812041159u39698b7dy51a3097d25c22c01@mail.gmail.com
Whole thread Raw
In response to Re: Trigger/Function - one solution - was constraint question (I think)  (Gary Stainburn <gary.stainburn@ringways.co.uk>)
List pgsql-sql
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 <gary.stainburn@ringways.co.uk>
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 (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

pgsql-sql by date:

Previous
From: "Sean Davis"
Date:
Subject: Aggregates with NaN values
Next
From: Mark Roberts
Date:
Subject: Re: Aggregates with NaN values