On Wed, 16 Jan 2008, Ken Johanson 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? Kris, please jump in where I'm missing anything:
Regclass is actually exactly what you need. This let's us skip all kinds
of parsing, deducing, ...
SELECT 'database.schema.table'::regclass::oid;
SELECT 'schema.table'::regclass::oid;
SELECT 'table'::regclass::oid;
SELECT '"database".schema."table"'::regclass::oid;
will all return the same thing, the oid for the qualified table, or if
unqualified, the first matching table on the search path. The oid will be
pg_class.oid which can then easily be used to lookup the columns in
pg_attribute as people have explained on -general.
> #Questions:
>
> -would the regclass-cast technique (I have no experience with it) work
> directly in the RETURNING or need to be in the [information_schema] query?
> Can you point me to examples?
Needs to be in a separate query.
> -would it be feasible to modify RETURNING in new server versions to accept
> indexes as args? That would obviate this whole discussion.
>
Not really, RETURNING is an arbitrary SELECT list, so you can say things
like RETURNING 1, 2+columnA, f(columnB). You could potentially add some
kind of keyword like RETURNING INDEXES 1,2,7, but I doubt the server
people have a great desire to support something this braindead for just
one client API to use.
Kris Jurka