pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

returning inserted id
3+ messages / 3 participants
[nested] [flat]

* returning inserted id
@ 2005-08-21 19:56  Matt A. <survivedsushi@yahoo.com>
  0 siblings, 2 replies; 3+ messages in thread

From: Matt A. @ 2005-08-21 19:56 UTC (permalink / raw)
  To: pgsql-sql

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 
 



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: returning inserted id
@ 2005-08-27 06:14  Bruno Wolff III <bruno@wolff.to>
  parent: Matt A. <survivedsushi@yahoo.com>
  1 sibling, 0 replies; 3+ messages in thread

From: Bruno Wolff III @ 2005-08-27 06:14 UTC (permalink / raw)
  To: Matt A. <survivedsushi@yahoo.com>; +Cc: pgsql-sql

On Sun, Aug 21, 2005 at 12:56:27 -0700,
  "Matt A." <survivedsushi@yahoo.com> wrote:
> 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? 

Use a serial type for the column and use currval to get the last value
assign to the corresponding sequence in the current session.
You probably want to read the documentation section on sequences.
http://candle.pha.pa.us/main/writings/pgsql/sgml/functions-sequence.html
http://candle.pha.pa.us/main/writings/pgsql/sgml/datatype.html#DATATYPE-INT



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: returning inserted id
@ 2005-08-27 12:53  Havasvölgyi Ottó <h.otto@freemail.hu>
  parent: Matt A. <survivedsushi@yahoo.com>
  1 sibling, 0 replies; 3+ messages in thread

From: Havasvölgyi Ottó @ 2005-08-27 12:53 UTC (permalink / raw)
  To: Matt A. <survivedsushi@yahoo.com>; pgsql-sql

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
> *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
>
> 





^ permalink  raw  reply  [nested|flat] 3+ messages in thread

end of thread, other threads:[~2005-08-27 12:53 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-08-21 19:56 returning inserted id Matt A. <survivedsushi@yahoo.com>
2005-08-27 06:14 ` Bruno Wolff III <bruno@wolff.to>
2005-08-27 12:53 ` Havasvölgyi Ottó <h.otto@freemail.hu>

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