From: Havasvölgyi Ottó <h.otto@freemail.hu>
To: Matt A. <survivedsushi@yahoo.com>
To: pgsql-sql@postgresql.org
Subject: Re: returning inserted id
Date: Sat, 27 Aug 2005 14:53:32 +0200
Message-ID: <005401c5ab06$54878c10$6b00a8c0@OTTO> (raw)
References: <20050821195627.8524.qmail@web35209.mail.mud.yahoo.com>
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." <survivedsushi@yahoo.com>
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
> *cricketcricket* :)
>
>
> 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
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: h.otto@freemail.hu, survivedsushi@yahoo.com
Subject: Re: returning inserted id
In-Reply-To: <005401c5ab06$54878c10$6b00a8c0@OTTO>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox