Thread: 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?

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@zapata.org]

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@zapata.org]
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>

--
Darren Ferguson