Re: pg_views definition format

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

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?