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

From: stuart(at)zapata(dot)org (Stuart robinson)
To: pgsql-general(at)postgresql(dot)org
Subject: how to get the primary key of a freshly inserted row in a stored procedure
Date: 2002-08-05 06:11:14
Message-ID: e6ca726c.0208042211.5fd92353@posting.google.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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]

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Jeff Davis 2002-08-05 06:33:51 Re: index and sequence name length limit?
Previous Message Rob Brown-Bayliss 2002-08-05 05:13:50 Re: O'Reilly Open Source Convention Report