From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | Peter Steinheuser <psteinheuser(at)myyearbook(dot)com> |
Cc: | David Harel <hareldvd(at)gmail(dot)com>, postgres sql <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: backup and document views and user functions |
Date: | 2010-08-30 20:10:56 |
Message-ID: | AANLkTinFxiL-iP47z9A3_MVGPTqgnYEtbGFRvMM7dGZ3@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
hello
2010/8/30 Peter Steinheuser <psteinheuser(at)myyearbook(dot)com>:
> 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.
>
there is much more easy way to get a function source code
SELECT pg_catalog.pg_get_functiondef(oid)
Regards
Pavel Stehule
> 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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Scott Marlowe | 2010-08-31 06:39:37 | Re: PostGres Tables in ArcSDE and ArcCatalog. |
Previous Message | Michael Andrew Babb | 2010-08-30 19:31:41 | PostGres Tables in ArcSDE and ArcCatalog. |