[SQL] backup and document views and user functions
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: harel...@ergolight-sw.com
Re: [SQL] backup and document views and user functions
Hello 2010/8/30 David Harel : > 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: harel...@ergolight-sw.com > > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] backup and document views and user functions
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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] backup and document views and user functions
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 Leeuwenreinou...@n.leeuwen.net http://reinoud.van.leeuwen.net kvk 27320762 __ -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] 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 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: harel...@ergolight-sw.com > > -- Peter Steinheuser psteinheu...@myyearbook.com
Re: [SQL] backup and document views and user functions
hello 2010/8/30 Peter Steinheuser : > 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 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: harel...@ergolight-sw.com >> > > > > -- > Peter Steinheuser > psteinheu...@myyearbook.com > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] PostGres Tables in ArcSDE and ArcCatalog.
Hi All, I am using PostGRES 8.3 in conjunction with ESRI's ArcSDE. ArcSDE allows the storage of spatial data inside of an PostGRES database. One of the programs bundled with ESRI's GIS suite is ArcCatalog. ArcCatalog is a spatial data manager. It is possible to browse and analyze the contents of PostGRES databases using ArcCatalog. In my PostGRES database, I have a table called SF30001. I can fully interact with the table using PSQL and pgAdmin III. However, I cannot interact with the table at all using ArcCatalog. Full interactivity needs to happen with ArcCatalog because the majority of the users of the PostGRES database will be using ArcCatalog to access data in the PostGRES database. If I execute a make table query along the lines of "select * into SF30001_test from SF30001" I can interact with the table in ArcCatalog just fine. I suspect that what I am running into is an ArcSDE configuration setting but I am not sure which one. I am not ready to rule out the issue as a PostGRES issue either. I'm currently pouring over configuration settings and how-to documents, but I wonder if anybody has any recommendations on how to gain full interactivity with the original table in ArcCatalog. Thanks, Mike
Re: [SQL] PostGres Tables in ArcSDE and ArcCatalog.
On Mon, Aug 30, 2010 at 1:31 PM, Michael Andrew Babb wrote: > Hi All, > If I execute a make table query along the lines of “select * into > SF30001_test from SF30001” I can interact with the table in ArcCatalog just > fine. what do \z SF30001 \z SF30001_test say about the permissions on the two tables? -- To understand recursion, one must first understand recursion. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
