Re: RETURNING clause: how to specifiy column indexes?

Kris Jurka wrote:
>
> I think the expectation is that:
>
> CREATE TABLE t(a int, b int);
> INSERT INTO t(b,a) VALUES (1,2) RETURNING *;
>
> will return 1,2 instead of 2,1 as it does now. In this case the op is
> not expecting that the (potentially reorganized) table order is driving
> the results, but the order that they've actually specified the columns
> in creates the result.
>

Kris, do you have pointers to a spec that says the named-columns should
specify the index, or that it should instead be the order in the table?
My interpretation from the JDBC spec was that the latter is true, I may
be wrong...

In the case where it is table-order, then I presume in PG that the
"natural" order of the columns (even if reordering is allowed at a alter
date) is specified by data in one of the pg_* tables (pg_class,
pg_index, etc). Does anyone know if this is true/false?

If true, my next idea would be to derive the column name using a
subquery in the returning clause. But it sounds like this may have
potential security contraints (will any INSERT query always have read
access to the PG tables?). And no guarantee of the order matching in the
long term.

Is there a more elegant approach, like?:

INSERT... RETURNING (PG_LIST_KEYS(tblname))

I looked but did not find such a utility. It seems that such function
would be best implemented in the server instead of in a driver (eg.
having hardcoded subquery to the schema).

Ken