From: Richard Huxton <dev@archonet.com>
To: "Kumar" <sgnerd@yahoo.com.sg>, "psql" <pgsql-sql@postgresql.org>
Subject: Re: Scripting only the functions
Date: Mon, 20 Oct 2003 18:23:14 +0100
User-Agent: KMail/1.5
References: <00ff01c3970a$c1c3d610$7502a8c0@hdsc.com>
In-Reply-To: <00ff01c3970a$c1c3d610$7502a8c0@hdsc.com>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200310201823.14910.dev@archonet.com>

On Monday 20 October 2003 14:04, Kumar wrote:
> Dear Friends,
>
> I am working with Postgres 7.3.4 on RH Linux server 7.2.
>
> Using pg_dump I could manage to take a script for all the DB objects. But
> wanted to take the script (DDL) for all the scripts in my database. While I
> searched I dont find any options in the pg_dump except for script tables
> only.

That'll be because there isn't one (as far as I know).

You could pipe the output of your pg_dump schema through a small perl script 
something like:

#!/usr/bin/perl -w
use strict;

my ($line, $in_func);
while ($line = <>) {
    if ($line =~ /^CREATE.+FUNCTION/) { $in_func = 1; }
    if ($in_func) { print $line; }
    if ($line =~ /^\s+LANGUAGE\s+\w+;$/) {
        $in_func = 0;
        print "\n";
    }
}

That should do it, although it will fail if you have a line in your function 
starting with "LANGUAGE" (doesn't seem likely to me).

The other options you could use are:

1. To write your own function-dumping script (use psql -E then \df+ to see how 
to get the function source).
2. Use pgadmin if there aren't too many functions to process, and do it by 
hand.

-- 
  Richard Huxton
  Archonet Ltd