SQL-question: returning the id of an insert querry

[Scott Chapman]

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

On Wednesday 12 November 2003 03:51, Andrew Sullivan wrote:[color=blue]
> On Mon, Nov 10, 2003 at 08:56:03AM -0800, Scott Chapman wrote:[color=green]
> > I talked with the author or SQLObject about this recently and I
> > thnk he's implementing this correctly, by querying the cursor for
> > the last OID?:[/color]
>
> That won't scale unless you index oid. And your tables will all need
> oids, which is not standard any more.
>
> If you do your work in one transaction and get the currval that way,
> it is impossible to go wrong. Also, if you don't return the
> connection to the pool before getting the currval, you will not go
> wrong.[/color]

Then there's another issue. If I insert a record and I don't have
OID's, I have to know which sequence to query currval out of, right?

If that's true, then I have to have much more knowlege about the
database structures in my front-end application, which is a Bad Thing.

It would be nice if PostgreSQL could return the primary key it inserted
with but that may not be a fool-proof solution either. Is there a nice
way to handle this situation?

Scott

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Doug McNaught]

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

Scott Chapman <scott_list@mis chko.com> writes:
[color=blue]
> It would be nice if PostgreSQL could return the primary key it inserted
> with but that may not be a fool-proof solution either. Is there a nice
> way to handle this situation?[/color]

Write a database function that inserts the record and returns the
primary key value? That's probably the best way to insulate your app
from the database structure...

-Doug

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to majordomo@postg resql.org)

[Scott Chapman]

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

On Wednesday 12 November 2003 11:29, Doug McNaught wrote:[color=blue]
> Scott Chapman <scott_list@mis chko.com> writes:[color=green]
> > It would be nice if PostgreSQL could return the primary key it
> > inserted with but that may not be a fool-proof solution either. Is
> > there a nice way to handle this situation?[/color]
>
> Write a database function that inserts the record and returns the
> primary key value? That's probably the best way to insulate your app
> from the database structure...[/color]

The function still has to know which sequence to pull from doesn't it?

I don't know much about triggers/functions in PG. Is it possible to
have a function that intercepts the information AFTER the sequence
value is added as the new primary key and then return it? This would
enable the use of a more generic function.

Scott


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Alvaro Herrera]

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

On Wed, Nov 12, 2003 at 11:46:44AM -0800, Scott Chapman wrote:[color=blue]
> On Wednesday 12 November 2003 11:29, Doug McNaught wrote:[color=green]
> >
> > Write a database function that inserts the record and returns the
> > primary key value? That's probably the best way to insulate your app
> > from the database structure...[/color]
>
> The function still has to know which sequence to pull from doesn't it?[/color]

Yes, but it could pull it from the system catalogs ... (not too
portable)

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"No necesitamos banderas
No reconocemos fronteras" (Jorge González)

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Doug McNaught]

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

Scott Chapman <scott_list@mis chko.com> writes:
[color=blue]
> On Wednesday 12 November 2003 11:29, Doug McNaught wrote:[color=green]
> > Scott Chapman <scott_list@mis chko.com> writes:[color=darkred]
> > > It would be nice if PostgreSQL could return the primary key it
> > > inserted with but that may not be a fool-proof solution either. Is
> > > there a nice way to handle this situation?[/color]
> >
> > Write a database function that inserts the record and returns the
> > primary key value? That's probably the best way to insulate your app
> > from the database structure...[/color]
>
> The function still has to know which sequence to pull from doesn't it?[/color]

Yes. It's theoretically possible to derive that information if you
have enough system-tables-fu, but since the function knows which
table it's inserting into, it's not hard to put the proper sequence
name in as well.
[color=blue]
> I don't know much about triggers/functions in PG. Is it possible to
> have a function that intercepts the information AFTER the sequence
> value is added as the new primary key and then return it? This would
> enable the use of a more generic function.[/color]

Sure, in the function you would basically do (I forget the exact
pl/pgsql syntax):

INSERT INTO foo VALUES (...);
SELECT currval('the_pk _sequence') INTO pk;
RETURN pk;

Doesn't remove the need to know or derive the proper sequence name.
There is no "what primary key did I just insert" built into PG. And
you will need a separate function for each table.

But this way the DB knowledge resides in the DB and you just have a
nice clean API for inserting data from the clients. The schema can
change and the API will (homefully) remain the same...

-Doug

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Scott Chapman]

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

On Wednesday 12 November 2003 12:31, Doug McNaught wrote:[color=blue]
> Scott Chapman <scott_list@mis chko.com> writes:[color=green]
> > On Wednesday 12 November 2003 11:29, Doug McNaught wrote:[color=darkred]
> > > Scott Chapman <scott_list@mis chko.com> writes:
> > > > It would be nice if PostgreSQL could return the primary key it
> > > > inserted with but that may not be a fool-proof solution either.
> > > > Is there a nice way to handle this situation?
> > >
> > > Write a database function that inserts the record and returns the
> > > primary key value? That's probably the best way to insulate your
> > > app from the database structure...[/color]
> >
> > The function still has to know which sequence to pull from doesn't
> > it?[/color]
>
> Yes. It's theoretically possible to derive that information if you
> have enough system-tables-fu, but since the function knows which
> table it's inserting into, it's not hard to put the proper sequence
> name in as well.
>[color=green]
> > I don't know much about triggers/functions in PG. Is it possible
> > to have a function that intercepts the information AFTER the
> > sequence value is added as the new primary key and then return it?
> > This would enable the use of a more generic function.[/color]
>
> Sure, in the function you would basically do (I forget the exact
> pl/pgsql syntax):
>
> INSERT INTO foo VALUES (...);
> SELECT currval('the_pk _sequence') INTO pk;
> RETURN pk;
>
> Doesn't remove the need to know or derive the proper sequence name.
> There is no "what primary key did I just insert" built into PG. And
> you will need a separate function for each table.
>
> But this way the DB knowledge resides in the DB and you just have a
> nice clean API for inserting data from the clients. The schema can
> change and the API will (homefully) remain the same...[/color]

What's the process to suggest changes to PG along these lines? Say, a
proposal to make it configurable for a user to have a INSERT return the
primary key that it just inserted rather than what it returns now?

Scott

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postg resql.org so that your
message can get through to the mailing list cleanly

[Doug McNaught]

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

Scott Chapman <scott_list@mis chko.com> writes:
[color=blue]
> What's the process to suggest changes to PG along these lines? Say, a
> proposal to make it configurable for a user to have a INSERT return the
> primary key that it just inserted rather than what it returns now?[/color]

What if you have a multicolumn PK?

I'm not actually sure PG keeps track of "primary keyness" -- I think
it translates a PRIMARY KEY constraint into NOT NULL UNIQUE when the
table is created. So it might be really hard to implement your
feature, even if you can figure out what should happen for multicolumn
PKs.

-Doug

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Andrew Sullivan]

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

On Wed, Nov 12, 2003 at 12:35:27PM -0800, Scott Chapman wrote:[color=blue]
> What's the process to suggest changes to PG along these lines? Say, a
> proposal to make it configurable for a user to have a INSERT return the
> primary key that it just inserted rather than what it returns now?[/color]

Take a well-worked-out proposal to the folks on -hackers, and either
wait for someone else to do the work (unlikely), or do the work
yourself, and submit a patch.

A

--
----
Andrew Sullivan 204-4141 Yonge Street
Afilias Canada Toronto, Ontario Canada
<andrew@liberty rms.info> M2P 2A8
+1 416 646 3304 x110


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[scott.marlowe]

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

On Wed, 12 Nov 2003, Scott Chapman wrote:
[color=blue]
> On Wednesday 12 November 2003 12:31, Doug McNaught wrote:[color=green]
> > Scott Chapman <scott_list@mis chko.com> writes:[color=darkred]
> > > On Wednesday 12 November 2003 11:29, Doug McNaught wrote:
> > > > Scott Chapman <scott_list@mis chko.com> writes:
> > > > > It would be nice if PostgreSQL could return the primary key it
> > > > > inserted with but that may not be a fool-proof solution either.
> > > > > Is there a nice way to handle this situation?
> > > >
> > > > Write a database function that inserts the record and returns the
> > > > primary key value? That's probably the best way to insulate your
> > > > app from the database structure...
> > >
> > > The function still has to know which sequence to pull from doesn't
> > > it?[/color]
> >
> > Yes. It's theoretically possible to derive that information if you
> > have enough system-tables-fu, but since the function knows which
> > table it's inserting into, it's not hard to put the proper sequence
> > name in as well.
> >[color=darkred]
> > > I don't know much about triggers/functions in PG. Is it possible
> > > to have a function that intercepts the information AFTER the
> > > sequence value is added as the new primary key and then return it?
> > > This would enable the use of a more generic function.[/color]
> >
> > Sure, in the function you would basically do (I forget the exact
> > pl/pgsql syntax):
> >
> > INSERT INTO foo VALUES (...);
> > SELECT currval('the_pk _sequence') INTO pk;
> > RETURN pk;
> >
> > Doesn't remove the need to know or derive the proper sequence name.
> > There is no "what primary key did I just insert" built into PG. And
> > you will need a separate function for each table.
> >
> > But this way the DB knowledge resides in the DB and you just have a
> > nice clean API for inserting data from the clients. The schema can
> > change and the API will (homefully) remain the same...[/color]
>
> What's the process to suggest changes to PG along these lines? Say, a
> proposal to make it configurable for a user to have a INSERT return the
> primary key that it just inserted rather than what it returns now?[/color]

I wouldn't suggest changing current bevaiour (i.e. the number of rows
inserted is probably a SQL SPEC thing) but to have each serial column in a
table be addressable so you'd just do:

select tablename.field name.currval;

and you'd get the currval back for the serial. note that if the serial
value was implemented by hand like:

create table test (id int primary key default
'myseq'::text|| nextval('seqnam e')::text);

that currval or the equivalent would actually give back the key inserted,
'myseqx' where x was the sequence number.

The folks are -hackers are always willing to listen to a good idea, but
they've got plenty on their plates, so this kind of thing needs to be at
the least thought out well enough so they won't have lots of
implementation problems with the plan.

It might also be possible to have the serial type create a plsql function
that has the name tablename_field name_currval() and returns the last
currval('seqnam e') with a simple wrapper. That solution would be fairly
easy to implement, and would be quite useful.


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Tom Lane]

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

Doug McNaught <doug@mcnaught. org> writes:[color=blue]
> Scott Chapman <scott_list@mis chko.com> writes:[color=green]
>> What's the process to suggest changes to PG along these lines? Say, a
>> proposal to make it configurable for a user to have a INSERT return the
>> primary key that it just inserted rather than what it returns now?[/color][/color]
[color=blue]
> What if you have a multicolumn PK?[/color]

Or a PK that's not an integer? Or no PK at all?

There's not likely to be any interest in hotwiring INSERT to return
a different command tag than it does now; that would break too much
existing code. There has been some talk of inventing an "INSERT
.... RETURNING ..." syntax extension that would return whatever
expressions you cared to compute from inserted rows --- but in the form
of a SELECT result, not by trying to squeeze it into a command tag.
I think the last discussion of this was a year or so back in pg-hackers.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postg resql.org

[Greg Stark]

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


"scott.marl owe" <scott.marlowe@ ihs.com> writes:
[color=blue]
> select tablename.field name.currval;[/color]

That syntax would be problematic, it would mean to select all rows from
tablename and evaluate fieldname.currv al 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,te xt) 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


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postg resql.org so that your
message can get through to the mailing list cleanly