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

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

If you use a sequence which it appears you do then just replace id_seq
with the id of your sequence

HTH

On 4 Aug 2002, Stuart robinson 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?
>
> CREATE FUNCTION foo(VARCHAR, VARCHAR)
> RETURNS INTEGER
> AS '
> DECLARE
> p1 ALIAS FOR $1;
> p2 ALIAS FOR $2;
> v_id INTEGER;
> BEGIN
> INSERT INTO foo (a, b) VALUES (p1, p2);
> SELECT id
> INTO v_id
> FROM foo
> WHERE a = p1 AND
> b = p2;
> RETURN v_id;
> END;
> '
> LANGUAGE 'plpgsql';
>
> Thanks in advance.
>
> --
> Stuart Robinson [stuart(at)zapata(dot)org]
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>

--
Darren Ferguson