Re: pg_views definition format - Mailing list pgsql-hackers

On May 13, 12:52 pm, t...@sss.pgh.pa.us (Tom Lane) wrote:
> Kev <kevinjamesfi...@gmail.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

That's handy to know about pg_views.  I'm still not sure how I should
code my script to make it future-proof though (because things of the
form "((a))" seem beyond dead-certain...) unless...is there some
function I can call to parse and then recompile the SQL, so I can feed
in my generated code in any format I like and then have it translate?
Or would the only way to do this be to actually create a view and then
call pg_get_viewdef() and then delete the view?