Thread: pg_views definition format
Hi, I have a script that automatically generates the SQL to create some views. I'd like it to check whether its generated SQL matches the SQL returned by "select definition from pg_views where...". I've guessed most of the rules just by looking at the output, but 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? Thanks, Kev
On May 13, 11:31 am, Kev <kevinjamesfi...@gmail.com> wrote: > Hi, > > I have a script that automatically generates the SQL to create some > views. I'd like it to check whether its generated SQL matches the SQL > returned by "select definition from pg_views where...". I've guessed > most of the rules just by looking at the output, but 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? > > Thanks, > Kev One other thing I'm just curious about, "!=" gets replaced with "<>"...how come? (Feels more VB-ish than C-ish, so I was surprised that that would be the official/preferred reconstruct....)
Kevin Field <kevinjamesfield@gmail.com> wrote: > One other thing I'm just curious about, "!=" gets replaced with > "<>"...how come? (Feels more VB-ish than C-ish, so I was surprised > that that would be the official/preferred reconstruct....) "<>" is the SQL standard operator. "!=" is a PostgreSQL extension, for the convenience and comfort of those more used to it. -Kevin
Kevin Field wrote: > > > One other thing I'm just curious about, "!=" gets replaced with > "<>"...how come? (Feels more VB-ish than C-ish, so I was surprised > that that would be the official/preferred reconstruct....) > > <> is the official SQL standard notation for "not equals", AFAIK. != is not. cheers andrew
Kev <kevinjamesfield@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.nspnameAS schemaname, c.relname AS viewname, pg_get_userbyid(c.relowner) AS viewowner, pg_get_viewdef(c.oid) AS
definitionFROM (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
---------------------------------------------------------------------------------------------------------------------------------------
SELECTn.nspname AS schemaname, c.relname AS viewname, pg_get_userbyid(c.relowner) AS viewowner, pg_get_viewdef(c.oid)
ASdefinition 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
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?
On May 13, 12:41 pm, Kevin.Gritt...@wicourts.gov ("Kevin Grittner")
wrote:
> Kevin Field <kevinjamesfi...@gmail.com> wrote:
> > One other thing I'm just curious about, "!=" gets replaced with
> > "<>"...how come? (Feels more VB-ish than C-ish, so I was surprised
> > that that would be the official/preferred reconstruct....)
>
> "<>" is the SQL standard operator. "!=" is a PostgreSQL extension,
> for the convenience and comfort of those more used to it.
Ahh, that makes sense. Thanks, guys.
On Wed, 13 May 2009, Kevin Field wrote:
> 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?
Just make it a temporary view and then it drops when the session ends.
Here's a working shell example that transforms a view into the parsed form
and returns it:
$ v="select * from pg_views"
$ p=`psql -Atc "create temporary view x as ${v}; select pg_get_viewdef('x'::regclass);"`
$ echo $p
SELECT pg_views.schemaname, pg_views.viewname, pg_views.viewowner, pg_views.definition FROM pg_views;
--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
On May 13, 5:37 pm, gsm...@gregsmith.com (Greg Smith) wrote:
> On Wed, 13 May 2009, Kevin Field wrote:
> > 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?
>
> Just make it a temporary view and then it drops when the session ends.
> Here's a working shell example that transforms a view into the parsed form
> and returns it:
>
> $ v="select * from pg_views"
> $ p=`psql -Atc "create temporary view x as ${v}; select pg_get_viewdef('x'::regclass);"`
> $ echo $p
> SELECT pg_views.schemaname, pg_views.viewname, pg_views.viewowner, pg_views.definition FROM pg_views;
Thanks. This works more quickly than I thought it might, which is
good.
Something I ran into though when trying to extend this logic to rules:
for some reason rule definitions are compiled with "create rule x as "
in front of them, unlike views, which just have everything after the
"as". I can keep the two parts separate and test accordingly, but it
seems a bit inconsistent.
On May 14, 2:22 pm, Kevin Field <kevinjamesfi...@gmail.com> wrote: > > Something I ran into though when trying to extend this logic to rules: > for some reason rule definitions are compiled with "create rule x as " > in front of them, unlike views, which just have everything after the > "as". I can keep the two parts separate and test accordingly, but it > seems a bit inconsistent. The fix isn't actually this clean in the end, since the 'fake' rule to be returned will have a different 'definition' (because its name is different) than the one we'd actually use to insert. So either we do some regexing or we have to back up the old rule's definition, drop the rule, insert it, get the new definiton, compare, and then if they're different, drop it again and put the old one back.
On May 14, 2:22 pm, Kevin Field <kevinjamesfi...@gmail.com> wrote: > > Something I ran into though when trying to extend this logic to rules: > for some reason rule definitions are compiled with "create rule x as " > in front of them, unlike views, which just have everything after the > "as". I can keep the two parts separate and test accordingly, but it > seems a bit inconsistent. The fix isn't actually this clean in the end, since the 'fake' rule to be returned will have a different 'definition' (because its name is different) than the one we'd actually use to insert. So either we do some regexing or we have to back up the old rule's definition, drop the rule, insert it, get the new definiton, compare, and then if they're different, drop it again and put the old one back.