Thread: get id of insert in plpgsql function

If I have a table mytable (id serial, y integer)  and wish to insert a
row into the table in a plpgsql function, how can I catch the id
(serial number) of the last inserted row?

am  Thu, dem 22.05.2008, um  9:56:49 +0200 mailte A B folgendes:
> If I have a table mytable (id serial, y integer)  and wish to insert a
> row into the table in a plpgsql function, how can I catch the id
> (serial number) of the last inserted row?

Either with currval() oder with RETURNING.


Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

I think I managed to get it working, but the error reporting is not so good,

Here is the function (I removed some stuff to make it shorter)

CREATE OR REPLACE FUNCTION addB(userid_ integer) RETURNS INTEGER AS $$
DECLARE
    bibid INTEGER;
BEGIN
    BEGIN
    INSERT INTO mytable (userid,y) VALUES (userid_,'f') RETURNING
currval('mytable_pkey') into bibid;
        RETURN bibid;
    EXCEPTION WHEN OTHERS THEN /*Catch all*/
        RETURN 0;
    END;
END;
$$ LANGUAGE plpgsql;

Now, I had forgot to add the column 'y' to mytable, and I got the error message

ERROR:  syntax error at or near "Resource" at character 1
STATEMENT:  Resource id #23

in the  pg_log/logfile
Is there clever setting to get a better response?

Sorry, I found it.
mytable_pkey is not a sequence, it should be mytable_userid_seq.
But still, is there a way to get more clever error reports?


2008/5/22 A B <gentosaker@gmail.com>:
> I think I managed to get it working, but the error reporting is not so good,
>
> Here is the function (I removed some stuff to make it shorter)
>
> CREATE OR REPLACE FUNCTION addB(userid_ integer) RETURNS INTEGER AS $$
> DECLARE
>        bibid INTEGER;
> BEGIN
>        BEGIN
>        INSERT INTO mytable (userid,y) VALUES (userid_,'f') RETURNING
> currval('mytable_pkey') into bibid;
>                RETURN bibid;
>        EXCEPTION WHEN OTHERS THEN /*Catch all*/
>                RETURN 0;
>        END;
> END;
> $$ LANGUAGE plpgsql;
>
> Now, I had forgot to add the column 'y' to mytable, and I got the error message
>
> ERROR:  syntax error at or near "Resource" at character 1
> STATEMENT:  Resource id #23
>
> in the  pg_log/logfile
> Is there clever setting to get a better response?
>