select view definition from pg_views feature request

Michael Shapiro <mshapiro51@gmail.com> writes:

It seems that the definition of a view from pg_catalog.pg_views does not
qualify the tables used in the view if the tables are in the current search
path.

Is it possible to either have the definition always qualify all tables
independent of the search_path (or else provide a new column that does
that)?

Why don't you just change the search path to empty before selecting?

regards, tom lane

Is that how PgAdmin does it? The views extracted by PgAdmin are fully
qualified....

On Sun, Nov 3, 2019 at 3:15 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

On Sun, 03 Nov 2019 16:15:23 -0500, Tom Lane <tgl@sss.pgh.pa.us>
wrote:

Michael Shapiro <mshapiro51@gmail.com> writes:

It seems that the definition of a view from pg_catalog.pg_views does not
qualify the tables used in the view if the tables are in the current search
path.

Is it possible to either have the definition always qualify all tables
independent of the search_path (or else provide a new column that does
that)?

Why don't you just change the search path to empty before selecting?

regards, tom lane

I'm guessing this is fine inside a transaction, but what if you're
using a client that commits by statement? In that case, wouldn't the
change to the path affect the other backends?

George

On Tue, 5 Nov 2019 at 11:05, George Neuner <gneuner2@comcast.net> wrote:

On Sun, 03 Nov 2019 16:15:23 -0500, Tom Lane <tgl@sss.pgh.pa.us>

Why don't you just change the search path to empty before selecting?

I'm guessing this is fine inside a transaction, but what if you're
using a client that commits by statement? In that case, wouldn't the
change to the path affect the other backends?

See https://www.postgresql.org/docs/current/sql-set.html

"SET only affects the value used by the current session."

Also:

"The effects of SET LOCAL last only till the end of the current transaction"

Neither affects other sessions.

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Can I set search_path='' (ie to a string that does not match any existing
schema)? Would that be the proper way to guarantee that the definition for
any view will always be fully-qualified?

On Sun, Nov 3, 2019 at 3:15 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

On 11/5/19 6:43 AM, Michael Shapiro wrote:

Can I set search_path='' (ie to a string that does not match any
existing schema)? Would that be the proper way to guarantee that the
definition for any view will always be fully-qualified?

test=# show search_path;
search_path
------------------------------------------------------------------
public,accounting,history,main,utility,timeclock,table_templates

test=# select * from pg_views where viewname = 'up_view';;
schemaname | viewname | viewowner | definition
------------+----------+-----------+-------------------------------
public | up_view | aklaver | SELECT up_test.id AS up_id, +
| | | up_test.col1 AS bool_col,+
| | | up_test.col_2 AS col2 +
| | | FROM up_test;
(1 row)

test=# set search_path = '';
SET
test=# show search_path;
search_path
-------------
""
(1 row)

test=# select * from pg_views where viewname = 'up_view';;
schemaname | viewname | viewowner | definition
------------+----------+-----------+-------------------------------
public | up_view | aklaver | SELECT up_test.id AS up_id, +
| | | up_test.col1 AS bool_col,+
| | | up_test.col_2 AS col2 +
| | | FROM public.up_test;
(1 row)

On Sun, Nov 3, 2019 at 3:15 PM Tom Lane <tgl@sss.pgh.pa.us
<mailto:tgl@sss.pgh.pa.us>> wrote:

Michael Shapiro <mshapiro51@gmail.com <mailto:mshapiro51@gmail.com>>
writes:

It seems that the definition of a view from pg_catalog.pg_views

does not

qualify the tables used in the view if the tables are in the

current search

path.

Is it possible to either have the definition always qualify all

tables

independent of the search_path (or else provide a new column that

does

that)?

Why don't you just change the search path to empty before selecting?

                        regards, tom lane

--
Adrian Klaver
adrian.klaver@aklaver.com

Adrian Klaver <adrian.klaver@aklaver.com> writes:

On 11/5/19 6:43 AM, Michael Shapiro wrote:

Can I set search_path='' (ie to a string that does not match any
existing schema)? Would that be the proper way to guarantee that the
definition for any view will always be fully-qualified?

[ example ]

If you read the documentation about search_path, you'll find out that
setting it to empty means that only the pg_catalog schema is present
in the effective search path (and maybe your pg_temp schema, if you
have created any temp tables). So system catalog references,
references to built-in functions and operators, and temp table names
will not be qualified. Everything else will be.

regards, tom lane

On Tue, 5 Nov 2019 14:29:00 +1300, David Rowley
<david.rowley@2ndquadrant.com> wrote:

See https://www.postgresql.org/docs/current/sql-set.html

"SET only affects the value used by the current session."

Also:

"The effects of SET LOCAL last only till the end of the current transaction"

Neither affects other sessions.

Ok, so you need to "ALTER DATABASE ..." for the change to affect
everyone. Thanks for the clarification.

George