From: | "Ian Harding" <ianh(at)tpchd(dot)org> |
---|---|
To: | <pgsql-general(at)postgresql(dot)org>, <rsanford(at)trefs(dot)com> |
Subject: | Re: SELECT @@IDENTITY |
Date: | 2003-06-23 18:49:58 |
Message-ID: | sef6e985.070@mail.tpchd.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
It would be nice. keep table names short, name id columns the tablename plus "id", and let PG assign sequence names, so I can write:
set sql "select currval(${tablename}_${tablename}id_seq) as lastid"
in pltcl.
In other words,
create table foobar (
foobarid serial primary key,
otherfoo varchar);
creates a sequence named foobar_foobarid_seq every time.
I guess you could even define a function called @@IDENTITY(varchar) and give it the table name... That's about as close as you are going to get.
Ian Harding
Programmer/Analyst II
Tacoma-Pierce County Health Department
iharding(at)tpchd(dot)org
Phone: (253) 798-3549
Pager: (253) 754-0002
>>> "Robert J. Sanford, Jr." <rsanford(at)trefs(dot)com> 06/23/03 10:58AM >>>
Okay, I did a quick search through both the general and SQL lists(1,2)
trying to determine if there is a PostgreSQL construct equivalent to
Microsoft SQL Server's "SELECT @@IDENTITY". After performing an insert the
database caches the last sequence number for each connection and the select
retrieves that value. Thus if connection A and connection B each inserted
into the same table they would each know what the value of the sequence was
immediately after their insert.
Is there any direct support for this? I know that I can manually code to
select the nextval of a sequence but the syntactic sugar of SELECT
@@IDENTITY is really nice.
Many thanks!
rjsjr
1 - The only valid query I could find was
http://archives.postgresql.org/pgsql-general/2001-09/msg00523.php which is
unanswered.
2 - The search engine for the archives seems to ignore the "@@" in the
search string so I ended up with a LOT of information on corporate identity
and identity columns.
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
From | Date | Subject | |
---|---|---|---|
Next Message | Franco Bruno Borghesi | 2003-06-23 18:54:05 | Re: SELECT @@IDENTITY |
Previous Message | Bruce Momjian | 2003-06-23 18:32:38 | Re: MySQL/PG search times |