Matt,
This is how you can do it:
create function person_ins(_name text)
returns integer
language plpgsql
as $$
declare
insert_id integer;
begin
insert into person (name) values(_name);
select into insert_id currval('person_id_seq');
return insert_id;
end;
$$;
Or perhaps even better: you return the whole inserted row (in 8.1 with INOUT
parameters):
create function person_ins(_name text)
returns person
language plpgsql
as $$
declare
inserted_row person;
begin
insert into person (name) values(_name);
select into inserted_row * from person where id=currval('person_id_seq');
return inserted_row;
end;
$$;
Hope there is no syntax error.
Best Regards,
Otto
----- Original Message -----
From: "Matt A." <[EMAIL PROTECTED]>
To: <pgsql-sql@postgresql.org>
Sent: Sunday, August 21, 2005 9:56 PM
Subject: [SQL] returning inserted id
this may have been asked a thousand times but i
haven't found a standard answer...
MSSQL
set nocount on
insert into (column) values (value)
select identityid = @@identity
set nocount off
POSTGRESQL
*cricket cricket* :)
How is this done? By a trigger function? Or is it
natively supported? Could I get an example on how to
do it too?
Thanks,
Matt
____________________________________________________
Start your day with Yahoo! - make it your home page
http://www.yahoo.com/r/hs
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
