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

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

From: "Rafael Domiciano" <rafael(dot)domiciano(at)gmail(dot)com>
To: "Gary Stainburn" <gary(dot)stainburn(at)ringways(dot)co(dot)uk>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: Trigger/Function - one solution - was constraint question (I think)
Date: 2008-12-04 19:59:55
Message-ID: 3a0028490812041159u39698b7dy51a3097d25c22c01@mail.gmail.com (view raw or flat)
Thread:
Lists: 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(dot)stainburn(at)ringways(dot)co(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>

In response to

pgsql-sql by date

Next:From: Mark RobertsDate: 2008-12-05 18:51:16
Subject: Re: Aggregates with NaN values
Previous:From: Sean DavisDate: 2008-12-04 18:01:49
Subject: Aggregates with NaN values

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