pg_views definition format

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.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

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 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 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.