Thread: Unqualified relations in views
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.
Thanks,
Pete O'Such
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
> "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