From: | Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> |
---|---|
To: | Pete O'Such <posuch(at)gmail(dot)com>, pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Unqualified relations in views |
Date: | 2023-09-13 06:11:03 |
Message-ID: | ff870b3f1993e0ed4ebff0a50ddbc7c717ee8676.camel@cybertec.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Anthony Apollis | 2023-09-13 09:06:56 | Re: Making Sure Primary and Secondary Keys Alligns |
Previous Message | Pete O'Such | 2023-09-13 05:58:28 | Unqualified relations in views |