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

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