pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedbackup and document views and user functions
6+ messages / 5 participants
[nested] [flat]
* backup and document views and user functions
@ 2010-08-30 18:21 David Harel <hareldvd@gmail.com>
0 siblings, 4 replies; 6+ messages in thread
From: David Harel @ 2010-08-30 18:21 UTC (permalink / raw)
To: pgsql-sql
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@ergolight-sw.com
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: backup and document views and user functions
@ 2010-08-30 18:30 Reinoud van Leeuwen <reinoud.v@n.leeuwen.net>
parent: David Harel <hareldvd@gmail.com>
3 siblings, 0 replies; 6+ messages in thread
From: Reinoud van Leeuwen @ 2010-08-30 18:30 UTC (permalink / raw)
To: pgsql-sql
On Mon, Aug 30, 2010 at 09:21:06PM +0300, David Harel 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.
pg_dump?
--
__________________________________________________
"Nothing is as subjective as reality"
Reinoud van Leeuwen reinoud.v@n.leeuwen.net
http://reinoud.van.leeuwen.net kvk 27320762
__________________________________________________
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: backup and document views and user functions
@ 2010-08-30 18:49 Hélder M. Vieira <hmv@mail.telepac.pt>
parent: David Harel <hareldvd@gmail.com>
3 siblings, 0 replies; 6+ messages in thread
From: Hélder M. Vieira @ 2010-08-30 18:49 UTC (permalink / raw)
To: pgsql-sql
Hi.
>I am looking for an easy way to backup views and functions. I want to store
>them in our version control system.
Well... As far as I can see, for functions you should go to the 'pg_catalog'
schema, table 'pg_proc'. In column 'prosrc' you will find the original text,
but other attributes are dispersed in a few columns, including arrays for
argument names and types. For views, you should look in the view 'pg_views'.
This is the result of a quick reading through the catalog, I hope I can only
hope I'm not missing something, please check carefully if this is waht
you're looking for.
Regards,
Helder M. Vieira
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: backup and document views and user functions
@ 2010-08-30 18:51 Pavel Stehule <pavel.stehule@gmail.com>
parent: David Harel <hareldvd@gmail.com>
3 siblings, 0 replies; 6+ messages in thread
From: Pavel Stehule @ 2010-08-30 18:51 UTC (permalink / raw)
To: David Harel <hareldvd@gmail.com>; +Cc: pgsql-sql
Hello
2010/8/30 David Harel <hareldvd@gmail.com>:
> Hi,
>
> I am looking for an easy way to backup views and functions. I want to store
> them in our version control system.
>
move your functions and view to separate schema - and do backup with
pg_dump -n schema
regards
Pavel Stehule
> 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@ergolight-sw.com
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: backup and document views and user functions
@ 2010-08-30 19:20 Peter Steinheuser <psteinheuser@myyearbook.com>
parent: David Harel <hareldvd@gmail.com>
3 siblings, 1 reply; 6+ messages in thread
From: Peter Steinheuser @ 2010-08-30 19:20 UTC (permalink / raw)
To: David Harel <hareldvd@gmail.com>; +Cc: pgsql-sql
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@gmail.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@ergolight-sw.com
>
>
--
Peter Steinheuser
psteinheuser@myyearbook.com
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: backup and document views and user functions
@ 2010-08-30 20:10 Pavel Stehule <pavel.stehule@gmail.com>
parent: Peter Steinheuser <psteinheuser@myyearbook.com>
0 siblings, 0 replies; 6+ messages in thread
From: Pavel Stehule @ 2010-08-30 20:10 UTC (permalink / raw)
To: Peter Steinheuser <psteinheuser@myyearbook.com>; +Cc: David Harel <hareldvd@gmail.com>; pgsql-sql
hello
2010/8/30 Peter Steinheuser <psteinheuser@myyearbook.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@gmail.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@ergolight-sw.com
>>
>
>
>
> --
> Peter Steinheuser
> psteinheuser@myyearbook.com
>
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2010-08-30 20:10 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-08-30 18:21 backup and document views and user functions David Harel <hareldvd@gmail.com>
2010-08-30 18:30 ` Reinoud van Leeuwen <reinoud.v@n.leeuwen.net>
2010-08-30 18:49 ` Hélder M. Vieira <hmv@mail.telepac.pt>
2010-08-30 18:51 ` Pavel Stehule <pavel.stehule@gmail.com>
2010-08-30 19:20 ` Peter Steinheuser <psteinheuser@myyearbook.com>
2010-08-30 20:10 ` Pavel Stehule <pavel.stehule@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox