Re: Proposal: RETURNING primary_key()

"Igal @ Lucee.org" <igal@lucee.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