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

Re: Insert rule and seqences

From: Jochem van Dieten <jochemd(at)oli(dot)tudelft(dot)nl>
To: Bo Lorentsen <bl(at)netgroup(dot)dk>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Insert rule and seqences
Date: 2001-09-03 19:18:33
Message-ID: 3B93D789.4060305@oli.tudelft.nl (view raw or flat)
Thread:
Lists: pgsql-novice
Bo Lorentsen wrote:
> 
>     CREATE SEQUENCE entity_seq;
> 
>     CREATE TABLE A (
>         id        INTEGER DEFAULT nextval( 'entity_seq' ),
>         name      TEXT
>     );
>  
>     CREATE TABLE B (
>         name     TEXT,
>         a_ref    INTEGER NOT NULL     -- REFERENCES A( id )
>     );
> 
>     CREATE RULE insert_on_a
>     AS ON INSERT
>     TO A (
>         INSERT INTO B name, a_ref VALUES( name, currval( 'entity_seq' ));
>     );
> 
> Is this all wrong, or is the another way to get the new 'id' value from 
> the A table ?

I wouldn't use a RULE but a TRIGGER. Something like the one below 
(please check syntax ;) ).

CREATE TRIGGER tr_insert_on_a AFTER INSERT OR UPDATE ON A
FOR EACH ROW EXECUTE PROCEDURE fn_insert_on_a();

CREATE function fn_insert_on_a() RETURNS OPAQUE AS '
     BEGIN
         INSERT INTO B name, a_ref VALUES(NEW.name, NEW.id);
     END;
' LANGUAGE 'plpgsql';

Jochem


In response to

Responses

pgsql-novice by date

Next:From: Tom LaneDate: 2001-09-03 20:11:32
Subject: Re: renaming a db
Previous:From: Bo LorentsenDate: 2001-09-03 10:39:41
Subject: Insert rule and seqences

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