Re: SQL-question: returning the id of an insert querry

"scott.marlowe" <scott(dot)marlowe(at)ihs(dot)com> writes:

> select tablename.fieldname.currval;

That syntax would be problematic, it would mean to select all rows from
tablename and evaluate fieldname.currval for each one. Actually it's worse, it
would be confused with schemas I think.

The postgres-ish way to do this would be to create a function like currval
that took a table and column and told you the currval of the sequence
associated with it.

Well you can already do something like that:

db=> create or replace function currval(text,text) returns bigint as 'select currval($1 || ''_'' || $2 || ''_seq'')' language sql strict;
CREATE FUNCTION

db=> create table test (a serial);
NOTICE: CREATE TABLE will create implicit sequence "test_a_seq" for "serial" column "test.a"
CREATE TABLE

db=> insert into test(a) values (default);
INSERT 14080230 1

db=> select currval('test','a');
currval
---------
1
(1 row)

The only problem arises if you use table names or column names that cause
postgres to truncate the resulting sequence name. This could be worked-around
by using the dependency information instead of depending on the naming scheme.

But as long as you do that the above works fine. And means you could always
change your naming scheme or method for looking up the associated sequence
later without changing all your sql.

--
greg