Re: "RETURNING PRIMARY KEY" syntax extension

On 27 June 2014 06:14, Gavin Flower <GavinFlower(at)archidevsys(dot)co(dot)nz> wrote:

> On 27/06/14 00:12, Rushabh Lathia wrote:
>
>> INSERT INTO dept VALUES (10,'ACCOUNTING','NEW YORK') returning primary
>> key, dname;
>>
>> I think allowing other columns with PRIMARY KEY would be more useful
>> syntax.
>> Even in later versions if we want to extend this syntax to return UNIQUE
>> KEY,
>> SEQUENCE VALUES, etc.. comma separation syntax will be more handy.
>>
>>
> I agree 100%.
>

If the query is being hand-crafted, what's to stop the query writer from
just listing the id columns in the returning clause? And someone specifying
RETURNING * is getting all the columns anyway.

The target use-case for this feature is a database driver that has just
done an insert and doesn't know what the primary key columns are - in that
case mixing them with any other columns is actually counter-productive as
the driver won't know which columns are which. What use cases are there
where the writer of the query knows enough to write specific columns in the
RETURNING clause but not enough to know which column is the id column?

Consistency is nice, and I can understand wanting to treat the PRIMARY KEY
bit as just another set of columns in the list to return, but I'd hate to
see this feature put on the back-burner to support use-cases that are
already handled by the current RETURNING feature. Maybe it's easy to do,
though.. I haven't looked into the implementation at all.

Cheers

Tom