Skip site navigation (1) Skip section navigation (2)

Re: pg_views definition format

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Kev <kevinjamesfield(at)gmail(dot)com>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: pg_views definition format
Date: 2009-05-13 16:52:20
Message-ID: 1736.1242233540@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackers
Kev <kevinjamesfield(at)gmail(dot)com> writes:
> ... I was surprised
> to find that some of my views of the form:

> select.........from b left join a on a.id=b.id

> ...were being translated to this:

> SELECT..........FROM (B LEFT JOIN a ON ((a.id = b.id)))

> ...before being stored in the table pg_views is derived from.  My
> surprise is at the double parentheses around "a.id = b.id".  Is that
> supposed to be that way?  Is it likely to change?

There isn't any such "table".  What pg_views is showing you is a reverse
compilation of the internal parsetree for the rule.  Whether there are
parentheses in a given place is dependent on whether the code thinks it
might be safe to omit them ... and I think in the non-prettyprinted
format the answer is always "no".  For instance with pg_views itself:

regression=# select pg_get_viewdef('pg_views'::regclass);
                                                                                                                 pg_get_viewdef
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 SELECT n.nspname AS schemaname, c.relname AS viewname, pg_get_userbyid(c.relowner) AS viewowner, pg_get_viewdef(c.oid) AS definition FROM (pg_class c LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = 'v'::"char");
(1 row)

regression=# select pg_get_viewdef('pg_views'::regclass, true);
                                                            pg_get_viewdef
---------------------------------------------------------------------------------------------------------------------------------------
  SELECT n.nspname AS schemaname, c.relname AS viewname, pg_get_userbyid(c.relowner) AS viewowner, pg_get_viewdef(c.oid) AS definition
    FROM pg_class c
    LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
   WHERE c.relkind = 'v'::"char";
(1 row)

Same parsetree, but the latter case is working a bit harder to make
it look nice.  The default case is overparenthesizing intentionally
to make dead certain the rule will be parsed the same way if it's
dumped and reloaded.

			regards, tom lane

In response to

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2009-05-13 17:01:45
Subject: Re: New trigger option of pg_standby
Previous:From: Andrew DunstanDate: 2009-05-13 16:46:56
Subject: Re: pg_views definition format

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group