From hareldvd@gmail.com Mon Aug  30 18:21:20 2010
Received: from maia.hub.org (maia-3.hub.org [200.46.204.243])
	by mail.postgresql.org (Postfix) with ESMTP id BDB4863226D
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Mon, 30 Aug 2010 15:21:20 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia,
	port 10024) with ESMTP id 54274-05 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Mon, 30 Aug 2010 18:21:13 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from mail-ew0-f46.google.com (mail-ew0-f46.google.com
	[209.85.215.46])
	by mail.postgresql.org (Postfix) with ESMTP id 2ADA6633031
	for <pgsql-sql@postgresql.org>; Mon, 30 Aug 2010 15:21:12 -0300 (ADT)
Received: by ewy23 with SMTP id 23so3381944ewy.19
	for <pgsql-sql@postgresql.org>; Mon, 30 Aug 2010 11:21:11 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:received:received:message-id:date:from
	:user-agent:mime-version:to:subject:content-type;
	bh=f97TFxK39PRkzrL1MN970ssN2DFHbA80v0HYS8Er9oY=;
	b=jDR42gQ1rxEwFZxFq+G8elQaqXdDrW3HfxxmSvReW8xyCfakv+VxViyfL1Q3N969vD
	aoYWEWcV28FI1YoWGeadWTknrkEKb1Nb4Nv5bp2gmjSswNHiV3lgGAxQVapMvsrthxIO
	kGTanrLiQNFgRFuQ8ZmU28uVAGvAmFhCIzNi0=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:user-agent:mime-version:to:subject
	:content-type;
	b=h6Llfb7xmUUa7a1Td3vkxkaCEyE+vXI3kpzV19UT0GfDNWLaOK519BJloiKF/Y9mQq
	cS6vTzcD65LWiJNP0tIDK+ZQAZISedi1k6kHy6wQFAhInLHgrxHW1yhwpNhPpL4wh/Fw
	nlLD8FQznDddlB1ei8cHhuiP/lWWw80dGXgH0=
Received: by 10.213.31.79 with SMTP id x15mr8732545ebc.60.1283192471547;
	Mon, 30 Aug 2010 11:21:11 -0700 (PDT)
Received: from [10.0.0.19] ([109.66.34.85])
	by mx.google.com with ESMTPS id a48sm12490224eei.1.2010.08.30.11.21.08
	(version=SSLv3 cipher=RC4-MD5); Mon, 30 Aug 2010 11:21:10 -0700 (PDT)
Message-ID: <4C7BF692.3030903@gmail.com>
Date: Mon, 30 Aug 2010 21:21:06 +0300
From: David Harel <hareldvd@gmail.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.9.1.11) Gecko/20100713 Thunderbird/3.0.6
MIME-Version: 1.0
To: postgres sql <pgsql-sql@postgresql.org>
Subject: backup and document views and user functions
Content-Type: multipart/alternative;
	boundary="------------090802050708000406040801"
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.889 tagged_above=-5 required=5 tests=BAYES_00=-1.9,
	HTML_MESSAGE=0.001, RCVD_IN_DNSWL_NONE=-0.0001,
	T_FILL_THIS_FORM_SHORT=0.01
X-Spam-Level: 
X-Archive-Number: 201008/84
X-Sequence-Number: 34524

This is a multi-part message in MIME format.
--------------090802050708000406040801
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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



--------------090802050708000406040801
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html style="direction: ltr;">
<head>

<meta http-equiv="content-type" content="text/html; charset=ISO-8859-1">
</head>
<body style="direction: ltr;" text="#000000" bgcolor="#ffffff">
<font size="+1"><font face="Arial">Hi,<br>
<br>
I am looking for an easy way to backup views and functions. I want to
store them in our version control system.<br>
<br>
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.<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>
</body>
</html>

--------------090802050708000406040801--



From reinoud.v@n.leeuwen.net Mon Aug  30 19:02:15 2010
Received: from maia.hub.org (maia-3.hub.org [200.46.204.243])
	by mail.postgresql.org (Postfix) with ESMTP id 5D72C632331
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Mon, 30 Aug 2010 16:02:15 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia,
	port 10024) with ESMTP id 70908-06 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Mon, 30 Aug 2010 19:02:04 +0000 (UTC)
X-Greylist: delayed 00:08:20.022099 by SQLgrey-1.7.6
Received: from smtp-vbr1.xs4all.nl (smtp-vbr1.xs4all.nl [194.109.24.21])
	by mail.postgresql.org (Postfix) with ESMTP id 95B2663226D
	for <pgsql-sql@postgresql.org>; Mon, 30 Aug 2010 16:02:04 -0300 (ADT)
Received: from spoetnik.xs4all.nl (spoetnik.xs4all.nl [82.95.228.65])
	by smtp-vbr1.xs4all.nl (8.13.8/8.13.8) with ESMTP id o7UIrgxk039562
	for <pgsql-sql@postgresql.org>; Mon, 30 Aug 2010 20:53:42 +0200 (CEST)
	(envelope-from reinoud.v@n.leeuwen.net)
Received: by spoetnik.xs4all.nl (Postfix, from userid 1000)
	id 1717F1CC5A; Mon, 30 Aug 2010 20:30:00 +0200 (CEST)
Date: Mon, 30 Aug 2010 20:30:00 +0200
From: Reinoud van Leeuwen <reinoud.v@n.leeuwen.net>
To: pgsql-sql@postgresql.org
Subject: Re: backup and document views and user functions
Message-ID: <20100830182959.GK781@spoetnik.xs4all.nl>
Mail-Followup-To: pgsql-sql@postgresql.org
References: <4C7BF692.3030903@gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <4C7BF692.3030903@gmail.com>
User-Agent: Mutt/1.5.13 (2006-08-11)
X-Virus-Scanned: by XS4ALL Virus Scanner
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.9 tagged_above=-5 required=5 tests=BAYES_00=-1.9,
	RCVD_IN_DNSWL_NONE=-0.0001
X-Spam-Level: 
X-Archive-Number: 201008/87
X-Sequence-Number: 34527

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
__________________________________________________



From hmv@mail.telepac.pt Mon Aug  30 18:56:51 2010
Received: from maia.hub.org (maia-5.hub.org [200.46.204.29])
	by mail.postgresql.org (Postfix) with ESMTP id 77E7263226D
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Mon, 30 Aug 2010 15:56:51 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024)
	with ESMTP id 43624-06 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Mon, 30 Aug 2010 18:56:44 +0000 (UTC)
X-Greylist: delayed 00:06:38.40254 by SQLgrey-1.7.6
Received: from sapo.pt (relay6.ptmail.sapo.pt [212.55.154.26])
	by mail.postgresql.org (Postfix) with SMTP id CE0F76321B4
	for <pgsql-sql@postgresql.org>; Mon, 30 Aug 2010 15:56:43 -0300 (ADT)
Received: (qmail 27588 invoked from network); 30 Aug 2010 18:50:01 -0000
Received: from unknown (HELO sapo.pt) (10.134.37.162)
	by relay6 with SMTP; 30 Aug 2010 18:50:01 -0000
Received: (qmail 19893 invoked from network); 30 Aug 2010 18:50:03 -0000
X-AntiVirus: PTMail-AV 0.3-0.95.1
X-Scan-Status: AV clean (0.01761 seconds);
 AS clean (0.10950 seconds);
Received: from unknown (HELO Hmv03) (hmv@[188.82.214.228])
	(envelope-sender <hmv@mail.telepac.pt>)
	by mta12 (qmail-ldap-1.03) with SMTP
	for <pgsql-sql@postgresql.org>; 30 Aug 2010 18:50:02 -0000
Message-ID: <4E7D507109C04F01A205FAB5D7E0607D@Hmv03>
From: =?iso-8859-1?Q?H=E9lder_M._Vieira?= <hmv@mail.telepac.pt>
To: "postgres sql" <pgsql-sql@postgresql.org>
References: <4C7BF692.3030903@gmail.com>
In-Reply-To: <4C7BF692.3030903@gmail.com>
Subject: Re: backup and document views and user functions
Date: Mon, 30 Aug 2010 19:49:48 +0100
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1";
	reply-type=original
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Windows Mail 6.0.6002.18197
X-MimeOLE: Produced By Microsoft MimeOLE V6.0.6002.18197
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=1.239 tagged_above=-5 required=5 tests=BAYES_50=0.8,
	RCVD_IN_DNSWL_NONE=-0.0001, STOX_REPLY_TYPE=0.439
X-Spam-Level: *
X-Archive-Number: 201008/86
X-Sequence-Number: 34526

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




From pavel.stehule@gmail.com Mon Aug  30 18:52:34 2010
Received: from maia.hub.org (maia-2.hub.org [200.46.204.251])
	by mail.postgresql.org (Postfix) with ESMTP id 84B01632CA5
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Mon, 30 Aug 2010 15:52:34 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia,
	port 10024) with ESMTP id 02944-06 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Mon, 30 Aug 2010 18:52:26 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from mail-pz0-f46.google.com (mail-pz0-f46.google.com
	[209.85.210.46])
	by mail.postgresql.org (Postfix) with ESMTP id 0A78C632CA2
	for <pgsql-sql@postgresql.org>; Mon, 30 Aug 2010 15:52:26 -0300 (ADT)
Received: by pzk9 with SMTP id 9so2185299pzk.19
	for <pgsql-sql@postgresql.org>; Mon, 30 Aug 2010 11:52:25 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:received:mime-version:received:in-reply-to
	:references:from:date:message-id:subject:to:cc:content-type;
	bh=T00Vg4oAQm5oDdvOim2CDL4gbALhkzO+IR9goDXJ0Go=;
	b=S0mFSd6xhxdF8V+9ko0hBJ89aX/97qD8eRa/Zv54GFuRZpNvPuaUHRWOAQ/ZOmU6/H
	aMy+QOp8heUud8djrRB9l3qnUCTGTC1AynOCijNd2WTXC0zVu1JXe56Vc5KeCGVybBqx
	i3/9mCBIeW+YCT4ZVVYjizrJLCyieBWiG0Q+Q=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:from:date:message-id:subject:to
	:cc:content-type;
	b=Ce5AFR04CsxoU4CKrD0N9oa3r/ztqBeLzMiE1dx71776YoPk2V1GbezwpEIQzqrhqI
	h9A7dr6uQUvOO0eZrXUteDLdykIIaQXIS+mOBGzPaFrZX58PRe5RWKMXF9wbpBR16xVv
	aVNc73WehdzX/UyZHs+TKDYP/KFteN1HK6+7g=
Received: by 10.142.48.18 with SMTP id v18mr5051679wfv.101.1283194345356; Mon,
	30 Aug 2010 11:52:25 -0700 (PDT)
MIME-Version: 1.0
Received: by 10.220.202.133 with HTTP; Mon, 30 Aug 2010 11:51:44 -0700 (PDT)
In-Reply-To: <4C7BF692.3030903@gmail.com>
References: <4C7BF692.3030903@gmail.com>
From: Pavel Stehule <pavel.stehule@gmail.com>
Date: Mon, 30 Aug 2010 20:51:44 +0200
Message-ID: <AANLkTimjzmeSW-Mj97TBcqL=xpdt3GWtOzRQ00ET+BCc@mail.gmail.com>
Subject: Re: backup and document views and user functions
To: David Harel <hareldvd@gmail.com>
Cc: postgres sql <pgsql-sql@postgresql.org>
Content-Type: text/plain; charset=UTF-8
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.9 tagged_above=-5 required=5 tests=BAYES_00=-1.9,
	RCVD_IN_DNSWL_NONE=-0.0001
X-Spam-Level: 
X-Archive-Number: 201008/85
X-Sequence-Number: 34525

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
>
>



From psteinheuser@myyearbook.com Mon Aug  30 19:47:26 2010
Received: from maia.hub.org (maia-5.hub.org [200.46.204.29])
	by mail.postgresql.org (Postfix) with ESMTP id D4688632CA2
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Mon, 30 Aug 2010 16:47:26 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024)
	with ESMTP id 57281-02 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Mon, 30 Aug 2010 19:47:18 +0000 (UTC)
X-Greylist: delayed 00:26:57.707894 by SQLgrey-1.7.6
Received: from mail-ey0-f174.google.com (mail-ey0-f174.google.com
	[209.85.215.174])
	by mail.postgresql.org (Postfix) with ESMTP id 71688632963
	for <pgsql-sql@postgresql.org>; Mon, 30 Aug 2010 16:47:18 -0300 (ADT)
Received: by eyg5 with SMTP id 5so3439227eyg.19
	for <pgsql-sql@postgresql.org>; Mon, 30 Aug 2010 12:47:16 -0700 (PDT)
MIME-Version: 1.0
Received: by 10.216.236.226 with SMTP id w76mr5431002weq.7.1283196018635; Mon,
	30 Aug 2010 12:20:18 -0700 (PDT)
Received: by 10.216.172.203 with HTTP; Mon, 30 Aug 2010 12:20:18 -0700 (PDT)
In-Reply-To: <4C7BF692.3030903@gmail.com>
References: <4C7BF692.3030903@gmail.com>
Date: Mon, 30 Aug 2010 15:20:18 -0400
Message-ID: <AANLkTikL3oHQabvtM7g0=AvQ76ni4qA3FseV1Z-yoVXz@mail.gmail.com>
Subject: Re: backup and document views and user functions
From: Peter Steinheuser <psteinheuser@myyearbook.com>
To: David Harel <hareldvd@gmail.com>
Cc: postgres sql <pgsql-sql@postgresql.org>
Content-Type: multipart/alternative; boundary=000e0cd402c4de3403048f0f5a13
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.899 tagged_above=-5 required=5 tests=BAYES_00=-1.9,
	HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 201008/88
X-Sequence-Number: 34528

--000e0cd402c4de3403048f0f5a13
Content-Type: text/plain; charset=ISO-8859-1

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

--000e0cd402c4de3403048f0f5a13
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

You&#39;ll probably have to write something (a function) that pulls the dat=
a out of pg_catalog.<br>You can get a leg up on that by connecting to psql =
using -E, which echoes hidden queries.<br>If you do a \df+=A0=A0 on a funct=
ion, you&#39;ll see the query PG uses.<br>
<br>ex. <br>production=3D# \df+ myschema.*<br><br>********* QUERY *********=
*<br>SELECT n.nspname as &quot;Schema&quot;,<br>=A0 p.proname as &quot;Name=
&quot;,<br>=A0 pg_catalog.pg_get_function_result(p.oid) as &quot;Result dat=
a type&quot;,<br>
=A0 pg_catalog.pg_get_function_arguments(p.oid) as &quot;Argument data type=
s&quot;,<br>=A0CASE<br>=A0 WHEN p.proisagg THEN &#39;agg&#39;<br>=A0 WHEN p=
.proiswindow THEN &#39;window&#39;<br>=A0 WHEN p.prorettype =3D &#39;pg_cat=
alog.trigger&#39;::pg_catalog.regtype THEN &#39;trigger&#39;<br>
=A0 ELSE &#39;normal&#39;<br>END as &quot;Type&quot;,<br>=A0CASE<br>=A0 WHE=
N p.provolatile =3D &#39;i&#39; THEN &#39;immutable&#39;<br>=A0 WHEN p.prov=
olatile =3D &#39;s&#39; THEN &#39;stable&#39;<br>=A0 WHEN p.provolatile =3D=
 &#39;v&#39; THEN &#39;volatile&#39;<br>
END as &quot;Volatility&quot;,<br>=A0 pg_catalog.pg_get_userbyid(p.proowner=
) as &quot;Owner&quot;,<br>=A0 l.lanname as &quot;Language&quot;,<br>=A0 p.=
prosrc as &quot;Source code&quot;,<br>=A0 pg_catalog.obj_description(p.oid,=
 &#39;pg_proc&#39;) as &quot;Description&quot;<br>
FROM pg_catalog.pg_proc p<br>=A0=A0=A0=A0 LEFT JOIN pg_catalog.pg_namespace=
 n ON n.oid =3D p.pronamespace<br>=A0=A0=A0=A0 LEFT JOIN pg_catalog.pg_lang=
uage l ON l.oid =3D p.prolang<br>WHERE n.nspname ~ &#39;^(myschema)$&#39;<b=
r>ORDER BY 1, 2, 4;<br>
**************************<br><br><br><br><div class=3D"gmail_quote">On Mon=
, Aug 30, 2010 at 2:21 PM, David Harel <span dir=3D"ltr">&lt;<a href=3D"mai=
lto:hareldvd@gmail.com">hareldvd@gmail.com</a>&gt;</span> wrote:<br><blockq=
uote class=3D"gmail_quote" style=3D"margin: 0pt 0pt 0pt 0.8ex; border-left:=
 1px solid rgb(204, 204, 204); padding-left: 1ex;">






<div style=3D"direction: ltr;" text=3D"#000000" bgcolor=3D"#ffffff">
<font size=3D"+1"><font face=3D"Arial">Hi,<br>
<br>
I am looking for an easy way to backup views and functions. I want to
store them in our version control system.<br>
<br>
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.<br>
<br>
Sorry for the lame question. I didn&#39;t find any clues on the web
.(typically, I fail to phrase the right keywords)<br>
</font></font>
<pre cols=3D"72">--=20
Thanks.

David Harel,

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D

Home office +972 77 7657645
Cellular:   +972 54 4534502
Snail Mail: Amuka
            D.N Merom Hagalil
            13802
            Israel
Email:      <a href=3D"mailto:hareldvd@ergolight-sw.com" target=3D"_blank">=
hareldvd@ergolight-sw.com</a>

</pre>
</div>

</blockquote></div><br><br clear=3D"all"><br>-- <br>Peter Steinheuser<br><a=
 href=3D"mailto:psteinheuser@myyearbook.com">psteinheuser@myyearbook.com</a=
><br>

--000e0cd402c4de3403048f0f5a13--



From pavel.stehule@gmail.com Mon Aug  30 20:17:25 2010
Received: from maia.hub.org (maia-3.hub.org [200.46.204.243])
	by mail.postgresql.org (Postfix) with ESMTP id 0CD49633574
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Mon, 30 Aug 2010 17:17:25 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia,
	port 10024) with ESMTP id 56318-09-2 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Mon, 30 Aug 2010 20:17:16 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from mail-yw0-f46.google.com (mail-yw0-f46.google.com
	[209.85.213.46])
	by mail.postgresql.org (Postfix) with ESMTP id E625C633737
	for <pgsql-sql@postgresql.org>; Mon, 30 Aug 2010 17:17:08 -0300 (ADT)
Received: by ywh1 with SMTP id 1so1801433ywh.19
	for <pgsql-sql@postgresql.org>; Mon, 30 Aug 2010 13:17:07 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:received:mime-version:received:in-reply-to
	:references:from:date:message-id:subject:to:cc:content-type
	:content-transfer-encoding;
	bh=J2EQ5sWnRqcYJF0wGvg30+GagGjU8RZsmkX34x8Cx7s=;
	b=dtJjMuhyaEk0VywFplqIqgcaChU8HAoEJM7KBuPWX7wU1DyroYQWKCt2DXOKqVWWah
	gViaHL7d4vNNsMRaZOLxCtOgYpI5fWyS2RLnlco6JxJqlvFg+2aYlfk8hzOLLBGPAqTO
	NERCKt5mgsZ2u1Ck7eLlE5LpVx/rJPKJ7PlNQ=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:from:date:message-id:subject:to
	:cc:content-type:content-transfer-encoding;
	b=G2M5r84hvOFJ0/R7/y7pkd5sfb0Iqx1DK3p8mKWxo+4DU+PVe1Q7xXjnI33+wR0Xuo
	yIV92jVMmsdexlLxgWYKdfIizJYM/CWGHIQURn1dlX+9IySZnuqtE0pMIInIOewYFPUU
	j/QF6UD5XFj+OtuUAYZOQZvpwE1c92TF4YCuk=
Received: by 10.220.88.167 with SMTP id a39mr2796339vcm.213.1283199099789;
	Mon, 30 Aug 2010 13:11:39 -0700 (PDT)
MIME-Version: 1.0
Received: by 10.220.202.133 with HTTP; Mon, 30 Aug 2010 13:10:56 -0700 (PDT)
In-Reply-To: <AANLkTikL3oHQabvtM7g0=AvQ76ni4qA3FseV1Z-yoVXz@mail.gmail.com>
References: <4C7BF692.3030903@gmail.com>
	<AANLkTikL3oHQabvtM7g0=AvQ76ni4qA3FseV1Z-yoVXz@mail.gmail.com>
From: Pavel Stehule <pavel.stehule@gmail.com>
Date: Mon, 30 Aug 2010 22:10:56 +0200
Message-ID: <AANLkTinFxiL-iP47z9A3_MVGPTqgnYEtbGFRvMM7dGZ3@mail.gmail.com>
Subject: Re: backup and document views and user functions
To: Peter Steinheuser <psteinheuser@myyearbook.com>
Cc: David Harel <hareldvd@gmail.com>, postgres sql <pgsql-sql@postgresql.org>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.9 tagged_above=-5 required=5 tests=BAYES_00=-1.9,
	RCVD_IN_DNSWL_NONE=-0.0001
X-Spam-Level: 
X-Archive-Number: 201008/89
X-Sequence-Number: 34529

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+=C2=A0=C2=A0 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=3D# \df+ myschema.*
>
> ********* QUERY **********
> SELECT n.nspname as "Schema",
> =C2=A0 p.proname as "Name",
> =C2=A0 pg_catalog.pg_get_function_result(p.oid) as "Result data type",
> =C2=A0 pg_catalog.pg_get_function_arguments(p.oid) as "Argument data type=
s",
> =C2=A0CASE
> =C2=A0 WHEN p.proisagg THEN 'agg'
> =C2=A0 WHEN p.proiswindow THEN 'window'
> =C2=A0 WHEN p.prorettype =3D 'pg_catalog.trigger'::pg_catalog.regtype THE=
N
> 'trigger'
> =C2=A0 ELSE 'normal'
> END as "Type",
> =C2=A0CASE
> =C2=A0 WHEN p.provolatile =3D 'i' THEN 'immutable'
> =C2=A0 WHEN p.provolatile =3D 's' THEN 'stable'
> =C2=A0 WHEN p.provolatile =3D 'v' THEN 'volatile'
> END as "Volatility",
> =C2=A0 pg_catalog.pg_get_userbyid(p.proowner) as "Owner",
> =C2=A0 l.lanname as "Language",
> =C2=A0 p.prosrc as "Source code",
> =C2=A0 pg_catalog.obj_description(p.oid, 'pg_proc') as "Description"
> FROM pg_catalog.pg_proc p
> =C2=A0=C2=A0=C2=A0=C2=A0 LEFT JOIN pg_catalog.pg_namespace n ON n.oid =3D=
 p.pronamespace
> =C2=A0=C2=A0=C2=A0=C2=A0 LEFT JOIN pg_catalog.pg_language l ON l.oid =3D =
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 fin=
e.
>>
>> 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,
>>
>> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
>>
>> 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
>