Re: "RETURNING PRIMARY KEY" syntax extension

On 27/06/14 09:09, Tom Dunstan wrote:
> On 27 June 2014 06:14, Gavin Flower <GavinFlower(at)archidevsys(dot)co(dot)nz <mailto: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.

Normal columns are injected into the query's returning list at parse time, whereas
this version of the patch handles expansion of PRIMARY KEY at the rewrite stage, which
would make handling a mix of PRIMARY KEY and normal output expressions somewhat tricky
to handle. (In order to maintain the columns in their expected position you'd
have to add some sort of placeholder/dummy TargetEntry to the returning list at parse
time, then rewrite it later with the expanded primary key columns, or something
equally messy).

On the other hand, it should be fairly straightforward to handle a list of keywords
for expansion (e.g. "RETURNING PRIMARY KEY, UNIQUE KEYS, SEQUENCE VALUES") should
the need arise.

Regards

Ian Barwick

--
Ian Barwick http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services