Re: Patch for Statement.getGeneratedKeys()

Ken Johanson <pg-user@kensystem.com> writes:
> Tom Lane wrote:
>> I've lost track of the context in which this needs to be done, but in
>> some cases a cast to or from regclass offers a painless way to
>> disambiguate table names.  Just a suggestion ...

> Tom, can you offer an example of this and how the overall goal might be
> achieved?

Well, most of the point is that regclass can substitute for an explicit
understanding of search_path disambiguation.  If you see "s.t" in the
query then it's clear that this is table t in schema s, but if you see
just "t" then you aren't so sure which schema it is in.  Resolving that
in a pure-SQL query is theoretically possible but it seems mighty ugly.

> To get the column names, I need to look in [the pg_* table equiv to
> information_schema] tables, and of course this means knowing which table
> is being referenced for modification.

Right.  I suggest using regclass to obtain the OID of the referenced
table, which then allows direct lookup in pg_attribute and other
relevant catalogs.  Something along the line of

    select attname from pg_attribute where attrelid = 't'::regclass

which also works for

    select attname from pg_attribute where attrelid = 's.t'::regclass

This is oversimplified because it doesn't consider any quoting issues
for funny characters in table names, and the query itself needs some
refinements like checking for attisdropped, but hopefully the point
is clear.

> -would it be feasible to modify RETURNING in new server versions to
> accept indexes as args? That would obviate this whole discussion.

I'm not clear what you're hoping for there.  It seems to me that any way
you slice it, you'll need to know which columns of the result are what.
"RETURNING *" is inadequate for that reason, but so would be "RETURNING
some-index-columns-but-I-aint-saying-which".  ISTM you've got to parse
things out enough to understand which columns you want and why; once
you know that, asking for them by name doesn't seem like much trouble.

            regards, tom lane