Re: backup and document views and user functions - Mailing list pgsql-sql

You'll probably have to write something (a function) that pulls the data out of pg_catalog.<br />You can get a leg up
onthat by connecting to psql using -E, which echoes hidden queries.<br />If you do a \df+   on a function, you'll see
thequery PG uses.<br /><br />ex. <br />production=# \df+ myschema.*<br /><br />********* QUERY **********<br />SELECT
n.nspnameas "Schema",<br />  p.proname as "Name",<br />  pg_catalog.pg_get_function_result(p.oid) as "Result data
type",<br/>   pg_catalog.pg_get_function_arguments(p.oid) as "Argument data types",<br /> CASE<br />  WHEN p.proisagg
THEN'agg'<br />  WHEN p.proiswindow THEN 'window'<br />  WHEN p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype
THEN'trigger'<br />   ELSE 'normal'<br />END as "Type",<br /> CASE<br />  WHEN p.provolatile = 'i' THEN 'immutable'<br
/> WHEN p.provolatile = 's' THEN 'stable'<br />  WHEN p.provolatile = 'v' THEN 'volatile'<br /> END as "Volatility",<br
/> pg_catalog.pg_get_userbyid(p.proowner) as "Owner",<br />  l.lanname as "Language",<br />  p.prosrc as "Source
code",<br/>  pg_catalog.obj_description(p.oid, 'pg_proc') as "Description"<br /> FROM pg_catalog.pg_proc p<br />    
LEFTJOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace<br />     LEFT JOIN pg_catalog.pg_language l ON l.oid =
p.prolang<br/>WHERE n.nspname ~ '^(myschema)$'<br />ORDER BY 1, 2, 4;<br /> **************************<br /><br /><br
/><br/><div class="gmail_quote">On Mon, Aug 30, 2010 at 2:21 PM, David Harel <span dir="ltr"><<a
href="mailto:hareldvd@gmail.com">hareldvd@gmail.com</a>></span>wrote:<br /><blockquote class="gmail_quote"
style="margin:0pt 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204); padding-left: 1ex;"><div bgcolor="#ffffff"
style="direction:ltr;" text="#000000"><font size="+1"><font face="Arial">Hi,<br /><br /> I am looking for an easy way
tobackup views and functions. I want to store them in our version control system.<br /><br /> Using pgAdmin I can
accessthem one at a time. I am looking for a better reporting mechanism. psql shell command for such report will be
justfine.<br /><br /> Sorry for the lame question. I didn't find any clues on the web .(typically, I fail to phrase the
rightkeywords)<br /></font></font> <pre cols="72">--  
Thanks.

David Harel,

==================================

Home office +972 77 7657645
Cellular:   +972 54 4534502
Snail Mail: Amuka           D.N Merom Hagalil           13802           Israel
Email:      <a href="mailto:hareldvd@ergolight-sw.com" target="_blank">hareldvd@ergolight-sw.com</a>

</pre></div></blockquote></div><br /><br clear="all" /><br />-- <br />Peter Steinheuser<br /><a
href="mailto:psteinheuser@myyearbook.com">psteinheuser@myyearbook.com</a><br/>