Re: [HACKERS] pg_views definition format
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.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 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) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_views definition format
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. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_views definition format
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_views definition format
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_views definition format
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. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_views definition format
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?
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_views definition format
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 gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
