Skip site navigation (1) Skip section navigation (2)

Re: Trigger/Function - one solution - was constraint question (I think)

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 (view raw or flat)
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     

In response to

Responses

pgsql-sql by date

Next:From: Sean DavisDate: 2008-12-04 18:01:49
Subject: Aggregates with NaN values
Previous:From: Achilleas MantziosDate: 2008-12-04 15:02:18
Subject: array index access outside range

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group