Thread: Insert rule and seqences

I would be very happy if someone could please give me a hint on this subject, as PosgreSQL confuses me on this matter.

I have been trying to make a simple rule upon a insert statement to a table that refers to a sequence, but I keep getting this strange error every time i use this rule, saying :

    ERROR:  entity_seq.currval is not yet defined in this session

The idea is that I have two tables I like to bind on a insert to one of them, like this :

    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 ?

Im looking forward to be enlightened :-)

/BL

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

Jochem van Dieten wrote:

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

Thanks, this works perfect for me now.

Is it correct that this is not possible to do when using pure "language 'sql'"
? As this language type can't refere to the "new" attribute.

> 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';

You only needed the "RETURN new;" line and this was perfect, thanks.

/BL