Re: RETURNING clause: how to specifiy column indexes?

On Wed, 12 Dec 2007, Ken Johanson wrote:

> 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...

No, I was actually misremembering what the JDBC spec said, although I
think it's an interesting case to consider regardless of any specs.

> 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?

pg_attribute.attnum stores column order at the moment. If/when
reordering is allowed, there will be another column indicating the
logical order of the colums.

> 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.

There is no requirement that insert permission on a user table implies
read access to pg_catalog. Still many clients will break if they can't
read pg_catalog. For example, all of the JDBC driver's MetaData results
need to query pg tables, updatable ResultSets need to query pg tables to
know what the primary key is and so on. So if this functionality required
access to pg_catalog that would neither be unprecedented nor unreasonable.

> Is there a more elegant approach, like?:
>
> INSERT... RETURNING (PG_LIST_KEYS(tblname))
>

You can't dynamically derive the returning clause for the same reason you
can't say "INSERT INTO (SELECT myfunc()) VALUES (...)", using myfunc to
determine the table name at runtime. The planner needs to know all the
tables/columns/other database parts up front before executing anything.

Kris Jurka