The following bug has been logged online:
Bug reference: 3637
Logged by: Pedro Gimeno
Email address: pgsql-001@personal.formauri.es
PostgreSQL version: n/a
Operating system: n/a
Description: Path resolving function (feature request)
Details:
There are some applications in which resolving the search_path to find an
unqualified table's schema is needed but it's not feasible to create a
function for that purpose.
An example of an application which would need it is the Zeos components
library http://sf.net/projects/zeoslib/ which, given a SELECT statement,
constructs the corresponding INSERT, UPDATE and DELETE statements for
writing to the given table, which must match the schema used when executing
the SELECT.
The only solution I've found so far is the following construction:
SELECT
nspname
FROM pg_class
INNER JOIN pg_namespace n
ON n.oid = relnamespace
WHERE nspname = ANY(current_schemas(TRUE))
AND relkind IN ('r', 'v', 'S')
AND relname = 'Target_Table'
ORDER BY strpos(
'/'||array_to_string(current_schemas(TRUE),'/')||'/',
'/'||nspname||'/')
LIMIT 1
;
but it won't be granted to work with names having a slash in them. I could
replace '/' with e.g. the ASCII US (Unit Separator), E'\37', but again it's
possible that a schema name uses that. And, after all, it's quite tricky and
hardly readable.
A function which returns the position within an array in which a given
element is found would help eliminate the array_to_string trickery and the
matching-char-in-schema-name hazard, but a function that resolves paths
would be more desirable in my opinion.
Perhaps a parallel path resolving function for functions, operators and
maybe other objects is desirable as well.
-- Pedro Gimeno