Unqualified relations in views

On Wed, 2023-09-13 at 01:58 -0400, Pete O'Such wrote:

For a view, how does one show what schema was used to qualify a relation, when
the query used to create the view originally left the relation unqualified?

The qualification of the view query seems static in all uses of the view.
Using pg_get_viewdef() returns the unqualified relation, but Postgres always
executes a qualified version of the view query, seemingly determined upon
creation of the view.

That implies the final qualifier is stored by Postgres, but I don't know how
to show it.

PostgreSQL resolves tables and other objects according to the setting of
"search_path" at CREATE VIEW time. The query itself is stored in parsed form.

pg_get_viewdef() deparses the query and only prepends a schema name if the
schema is not on the "search_path". So the solution is to set "search_path" empty:

SET search_path = '';

SELECT pg_get_viewdef('myschema.myview');

Yours,
Laurenz Albe

PostgreSQL resolves tables and other objects according to the setting of
"search_path" at CREATE VIEW time. The query itself is stored in parsed

form.

pg_get_viewdef() deparses the query and only prepends a schema name if the
schema is not on the "search_path". So the solution is to set

"search_path"

empty:

SET search_path = '';

SELECT pg_get_viewdef('myschema.myview');

Thank you! That is the perfect answer to my question!

Is Postgres hiding the ball a bit here? Is there a reason that obscuring
the
known and static schema is better than showing it? In my case (tracking
down
execution differences between local and FDW view use) this has occupied a
lot of
time.

Thanks again,
Pete O'Such