RE: [HACKERS] getting new serial value of serial insert - Mailing list pgsql-hackers

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@gtv.ca]
>> Sent: Thursday, November 04, 1999 8:26 AM
>> To: Tom Lane
>> Cc: Ed Loehr; pgsql-hackers@postgreSQL.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
>> 
>> ************
>>