Re: backup and document views and user functions

You'll probably have to write something (a function) that pulls the data out
of pg_catalog.
You can get a leg up on that by connecting to psql using -E, which echoes
hidden queries.
If you do a \df+ on a function, you'll see the query PG uses.

ex.
production=# \df+ myschema.*

********* QUERY **********
SELECT n.nspname as "Schema",
p.proname as "Name",
pg_catalog.pg_get_function_result(p.oid) as "Result data type",
pg_catalog.pg_get_function_arguments(p.oid) as "Argument data types",
CASE
WHEN p.proisagg THEN 'agg'
WHEN p.proiswindow THEN 'window'
WHEN p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype THEN
'trigger'
ELSE 'normal'
END as "Type",
CASE
WHEN p.provolatile = 'i' THEN 'immutable'
WHEN p.provolatile = 's' THEN 'stable'
WHEN p.provolatile = 'v' THEN 'volatile'
END as "Volatility",
pg_catalog.pg_get_userbyid(p.proowner) as "Owner",
l.lanname as "Language",
p.prosrc as "Source code",
pg_catalog.obj_description(p.oid, 'pg_proc') as "Description"
FROM pg_catalog.pg_proc p
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
LEFT JOIN pg_catalog.pg_language l ON l.oid = p.prolang
WHERE n.nspname ~ '^(myschema)$'
ORDER BY 1, 2, 4;
**************************

On Mon, Aug 30, 2010 at 2:21 PM, David Harel <hareldvd(at)gmail(dot)com> wrote:

> Hi,
>
> I am looking for an easy way to backup views and functions. I want to store
> them in our version control system.
>
> Using pgAdmin I can access them one at a time. I am looking for a better
> reporting mechanism. psql shell command for such report will be just fine.
>
> Sorry for the lame question. I didn't find any clues on the web
> .(typically, I fail to phrase the right keywords)
>
> --
> Thanks.
>
> David Harel,
>
> ==================================
>
> Home office +972 77 7657645
> Cellular: +972 54 4534502
> Snail Mail: Amuka
> D.N Merom Hagalil
> 13802
> Israel
> Email: hareldvd(at)ergolight-sw(dot)com
>
>

--
Peter Steinheuser
psteinheuser(at)myyearbook(dot)com