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

In article <ain47i$1i2n$1@news.hub.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