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
>> 
>> ************
>> 

Thu, 4 Nov 1999 10:53:41 +0200 
"Ansley, Michael" <Michael.Ansley@intec.co.za>
RE: [HACKERS] getting new serial value of serial insert
I assume it is possible in pgsql to return the just-inserted serial value
with a stored procedure.

Stored procedures, though, would seem to be significantly more hassle vs.
the INSERT-returns-serial approach.  I share the concern about non-std
SQL, though it seems the pgsql system (like most other RDBMS) is
already loaded with non-std SQL precisely because the std has
repeatedly been judged lacking for itches that needed scratching.

As for concern about modifying INSERT semantics just for serial types,
that too, I would normally share.  A generalized solution is better.
However, the pg serial type is already a special case, constructed by
Postgres from other existing components unlike other types.  For that
reason, I think the case of facilitating an atomic return of the
serial value from a SQL insert statement would provide pragmatic
support for the key access mode to a special-case (non-std?) extension
already present.  For the same reason, it strikes me that the
generalized ability to return any value from an INSERT should be
treated as largely orthogonal to the special case serial type.

Cheers.
Ed

"Ansley, Michael" wrote:

> 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.


Thu, 04 Nov 1999 10:36:50 -0600
Ed Loehr <ELOEHR@austin.rr.com>
Re: [HACKERS] getting new serial value of serial insert
hi..

> 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.
> 

in an earlier post i mentioned that this doesn't do anything FUNCTIONALY new,
it merely allows doing it with EASE and greater SPEED.. 

ease, because you don't have to write a function (not really stored procedure
=) to handle each specific insert and return pair you want.. with RETURN this
would be defined on a per query basis... 

speed, because you would skip the SELECT to get the information.. it would tap
the tuple whilst still in memory during the read, like a tigger... you skip the
SELECt...

last, it allows certain security possibilities: giving people access to the
information they just inserted without giving them general SELECT permissions
on the table(s) involved...

so, no.. you aren't missing the boat by thinking this sort of thing CAN be done
via other methods. the point is merely that the current methods are clumsy and
slow and it seems a number of people are going through the current necessary
hoops... 

-- 
Aaron J. Seigo
Sys Admin

Thu, 4 Nov 1999 14:29:15 -0700
"Aaron J. Seigo" <aaron@gtv.ca>
RE: [HACKERS] getting new serial value of serial insert