Re: lastval()

Abhijit Menon-Sen said:
> At 2005-05-11 10:55:37 +1000, neilc(at)samurai(dot)com wrote:
>>
>> > Here is a small patch that implements a function lastval() [...]
>>
>> What do people think of this idea? (Tom seems opposed, I'm just
>> wondering if there are other opinions out there.)
>
> For what it's worth, I think it's a bad idea.
>
> In the MySQL wire protocol (hi Dennis!), the "last insert id" is sent
> along with every "OK" message, and the client can just keep the value
> in memory. Users call a function to retrieve that value, rather than
> issuing a "SELECT nextval()".

You can do both - they have an SQL level function as well as supporting it
at the protocol layer. See
http://dev.mysql.com/doc/mysql/en/information-functions.html

>
> So the server-side lastval() function is not enough for any meaningful
> compatibility. The client would also need to be changed to provide the
> pgsql_last_insert_id() or a similar function (which could do a "SELECT
> lastval()" internally).
>
> In this situation -- where both client changes AND a server round-trip
> are required -- what's the point of adding cruft to the server? Might
> as well confine changes to the client, and use nextval to implement the
> feature.
>

I don't believ it can be sensibly done by the client alone. Either it needs
something like this or it shouldn't be done at all.

> By the way, what would lastval() do if an insert trigger inserts a row
> into a table with another serial column?
>

or more than one? Yes, it's not good in certain circumstances. That doesn't
make it useless in all circumstances.

I'm not jumping out of my seat to have this. But as Joshua points out, it is
frequently requested.

cheers

andrew