Re: how to get the primary key of a freshly inserted row in a stored procedure - Mailing list pgsql-general

> I'm writing a PL/pgSQL function that will insert a row and return its
> id. Right now I just do a select after the insert to get the id of the
> new row (see example code below). But I'm guessing that there's a
> better way. Any recommendations?

It would help to see your table definitions, but I am thinking
something like this might work... (this assumes that id uses
a sequence for its values, like a SERIAL type.)

> CREATE FUNCTION foo(VARCHAR, VARCHAR)
> RETURNS INTEGER
> AS '
>    DECLARE
>      p1 ALIAS FOR $1;
>      p2 ALIAS FOR $2;
>      v_id INTEGER;
>    BEGIN
       select nextval(''id_seq'') into v_id;
>      INSERT INTO foo (id, a, b) VALUES (v_id, p1, p2);
>    RETURN v_id;
>    END;
> '
> LANGUAGE 'plpgsql';
>