RE: [HACKERS] getting new serial value of serial insert

Why can't this simply be done with a stored proc? Or am I missing the boat?
Stored proc accepts parameters to insert, and returns whatever value you
want it to.

MikeA

>> -----Original Message-----
>> From: Aaron J. Seigo [mailto:aaron(at)gtv(dot)ca]
>> Sent: Thursday, November 04, 1999 8:26 AM
>> To: Tom Lane
>> Cc: Ed Loehr; pgsql-hackers(at)postgreSQL(dot)org
>> Subject: Re: [HACKERS] getting new serial value of serial insert
>>
>>
>> hi...
>>
>> > I don't like *any* of the proposals that have appeared in
>> this thread.
>> > Inventing nonstandard SQL syntax is a bad idea, and furthermore all
>>
>> agreed... at the same time though, just about every other
>> database out there has
>> non-standard SQL statements to work around various
>> limitations, perceived and
>> otherwise... also, a quick look through the user
>> documentation for postgres
>> will show that there already are a lot of non-standard SQL
>> statements..
>> *shrug*
>>
>> > of these solutions are extremely limited in capability:
>> they only work
>> > for "serial" columns, they only work for a single serial
>> column, etc
>> > etc. If we're going to address this issue at all, we should invent
>> > a general-purpose mechanism for passing back to the
>> frontend application
>> > the results of server-side operations that are performed
>> as a side effect
>> > of SQL commands.
>>
>> the RETURN cluase concept isn't limited to serial columns or
>> single columns...
>> it would allow the return of any columns that were affected by the
>> INSERT/UPDATE/DELETE...
>>
>> > The idea that comes to my mind is to invent a new command,
>> available in
>> > "trigger" procedures, that causes a message to be sent to
>> the frontend
>> > application. This particular problem of returning a
>> serial column's
>> > value could be handled in an "after insert" trigger
>> procedure, with a
>> > command along the lines of
>> > SENDFE "mytable.col1=" + new.col1
>> > We'd have to think about what restrictions to put on the message
>> > contents, if any. It might be sufficient just to counsel users
>> > to stick identification strings on the front of the message text
>> > as illustrated above.
>>
>> i don't think this is leaps and bounds above what can
>> already be done with
>> functions, triggers and external code now. while this would
>> probably create a
>> speed adantage (by skipping a select statement step) it
>> would still leave the
>> problem of having to implement a trigger for every type of
>> data you want back.
>>
>> and there are limitations inherent to this method: if
>> you wanted field1 returned when updating feild2, but field3
>> when updating
>> fielld4... except that one time when you want both field1
>> and field3 returned...
>> *takes a deep breath* it just isn't flexible enough...
>>
>> for every possible return situation, you'd have to define it
>> in a trigger...
>> and there still would be limitations to what rules you could
>> set up.. e.g. how
>> would you define in a trigger different returned values
>> depending on the user
>> that is currently accessing the database? a real world
>> example would be a user
>> coming in over the web and an admin coming in through the
>> same method. unless
>> pgsql handles the user authentication (which in most
>> webplications, it doesn't)
>> there would be no way to tell the difference without going
>> through more work
>> than it takes to do it with current methods (e.g. select).
>>
>> > transaction, there wouldn't be any artificial restriction to just
>> > returning one or a fixed number of values. Finally, we'd not be
>> > creating data-type-specific behavior for SERIAL; the facility could
>> > be used for many things.
>>
>> this is _exactly_ what i have said in several previous
>> posts: that it should not
>> be limited just to serial fields...
>>
>> > We'd need to think about just how to make the messages available to
>> > client applications. For libpq, something similar to the existing
>> > NOTIFY handling might work. Not sure how that would map
>> into ODBC or
>> > other frontend libraries.
>>
>> if it was integrated into the INSERT/UPDATE/DELETE queries,
>> it wouldn't need to
>> be implemented in each frontend library. it would just be
>> output, much like the
>> OID and # of records inserted that currently appears after an
>> INSERT/UDPATE/DELETE.
>>
>> however, if it is so completely horrid to add functionality
>> to the SQL
>> statements, i really can't think of another method that
>> would provide the
>> functionality that would actually make it useful outside of
>> a limited number of
>> situations.... so unless someone can think of a way, maybe
>> its just better to
>> leave it be.
>>
>> --
>> Aaron J. Seigo
>> Sys Admin
>>
>> Rule #1 of Software Design: Engineers are _not_ users
>>
>> ************
>>