Обсуждение: backup and document views and user functions
<font size="+1"><font face="Arial">Hi,<br /><br /> I am looking for an easy way to backup views and functions. I want tostore them in our version control system.<br /><br /> Using pgAdmin I can access them one at a time. I am looking for abetter reporting mechanism. psql shell command for such report will be just fine.<br /><br /> Sorry for the lame question.I didn't find any clues on the web .(typically, I fail to phrase the right keywords)<br /></font></font> <pre class="moz-signature"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 class="moz-txt-link-abbreviated" href="mailto:hareldvd@ergolight-sw.com">hareldvd@ergolight-sw.com</a> </pre>
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 > >
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
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 __________________________________________________
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;
**************************
--
Peter Steinheuser
psteinheuser@myyearbook.com
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
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 >