Обсуждение: how to get the primary key of a freshly inserted row in a stored procedure
how to get the primary key of a freshly inserted row in a stored procedure
От
stuart@zapata.org (Stuart robinson)
Дата:
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