Re: Proposal: RETURNING primary_key()

"Igal @ Lucee.org" <igal(at)lucee(dot)org> writes:
> On 3/7/2016 12:45 PM, Joshua D. Drake wrote:
>> I agree that the problem is that you don't always know what the
>> primary key is.
>> I would argue the solution is to check before you write the query.

Yeah. I'm rather suspicious of this proposal; I do not think it's
actually very useful to return a primary-key value without any indication
of what the primary key is. There are also corner cases where it seems
pretty ill-defined. For example, suppose you do this on an inheritance
parent table that has a pkey defined, but not all its child tables do
(or maybe they do but their pkeys aren't identical to the parent's).
What should happen then?

> Sure, that would be great, but perhaps I should have give some more context:
> We have an application server which allows our developers to query
> databases with simplified syntax. Our code is written in a generic way
> to allow the developers that use our application server to pass whatever
> query they want into the database server, whether it's SQL Server,
> MySQL, Oracle, etc.

That's an exceptionally weak use-case to argue for this with. Unless
you can get *all* those DBMS suppliers to invent equivalent features,
you're going to have to have pkey-querying logic anyway. The argument
for bespoke syntax for it in just one DBMS seems pretty weak.

I am fairly sure, also, that all of those systems have support for the
SQL-standard information_schema views. So if you write a pkey-identifying
query against those views, you'd have some chance of a solution that
actually did work everywhere.

regards, tom lane