Re: how to get the primary key of a freshly inserted row in a stored procedure

In article <ain47i$1i2n$1(at)news(dot)hub(dot)org>, Lee Harr wrote:
>> 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';
>>

Hi,

Since your function returns an integer :

change RETURN v_id to RETURN currval(\'id_seq\') ;

--
HTH

William