Tom Lane wrote:
> Kris Jurka <books@ejurka.com> writes:
>> You need to look through the schemas in the search_path in order and see
>> which one a table with the given name appears in first.
>
> 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:
#Overview:
We need to implement:
Statement.executeUpdate(String sql, int columnIndexes[])
Current strategy is to find the natural column order (ordinal positions
for columnIndexes[]) and extract those names, passing them through to:
Statement.executeUpdate(String sql, String columnIndexes[])
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. We are already parsing the table
name (fully or partially qualified) from the DML; now we need to search
[information_schema], finding the matching catalog, schema, and table,
and searching schema in the order of the schema search-path.
#History
Most interesting probably is that Kris mentioned it would work to just
do a INSERT.. RETURNING * to get the keys, however I'm electing to try
the extra-mile / "hard way" to save returning LOBs or entire multi-row
inserts. Ideally I's like to do everything in one extra query to
[information_schema] or better yet in RETURNING.
#Now
I'm a bit perplexed as to how I could get the current-ref'd schema using
one query. I think it might involve passing a subselect of "SHOW
search_path" as the arg to the [information_schema] query, but using
that var as a list and filling the $user var is not familiar ground...
#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?
-would it be feasible to modify RETURNING in new server versions to
accept indexes as args? That would obviate this whole discussion.
Thanks,
Ken