Обсуждение: Re: how to get the primary key of a freshly inserted row in a stored procedure

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

The way you are doing it will work fine

The other way is your could return CURRVAL('id_seq');

HTH

On Tue, 6 Aug 2002, 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';
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>

--
Darren Ferguson

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