Re: 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