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