Thread

1. Unqualified relations in views

   Pete O'Such <posuch@gmail.com> — 2023-09-13T05:58:28Z

   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
   

2. Re: Unqualified relations in views

   Laurenz Albe <laurenz.albe@cybertec.at> — 2023-09-13T06:11:03Z

   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
   
   
   
   

3. Re: Unqualified relations in views

   Pete O'Such <posuch@gmail.com> — 2023-09-13T17:41:34Z

   > 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