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;

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

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

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