#!/usr/bin/perl
use DBI;

$dest_dir = "./functions/"; # this directory will be created
### connect to the database
my $dbhost="127.0.0.1";
my $dbname="yourdb";
my $dbuser="postgres";
my $dbpass="secret";


if ( -e $dest_dir )
{
	die "Directory $dest_dir already exists! To get a clean dump you need to erase that directory\n";
}

print "This script will dump functions to $dest_dir/\$SCHEMA/\$FUNCTION.sql\n";

my $Con = "DBI:Pg:dbname=$dbname;host=$dbhost";
my $Dbh = DBI->connect($Con, $dbuser, $dbpass, {RaiseError =>1}) || die "Database access problem for $Con.\nError returned was: ". $DBI::errstr;

my $sql="SELECT n.nspname AS schema
        , p.proname AS function
        , (CASE WHEN p.proretset THEN 'setof ' ELSE '' END) || pg_catalog.format_type(p.prorettype, NULL) AS result_data_type
        , pg_catalog.oidvectortypes(p.proargtypes) AS input_data_types
        , p.prosrc AS source_md5
	, l.lanname AS language
	, CASE WHEN p.provolatile = 'i' THEN 'immutable'                                               
	       WHEN p.provolatile = 's' THEN 'stable'                                                  
	       WHEN p.provolatile = 'v' THEN 'volatile'                                                
	END as volatility
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
     LEFT JOIN pg_catalog.pg_user u ON u.usesysid = p.proowner
WHERE p.prorettype <> 'pg_catalog.cstring'::pg_catalog.regtype
      AND p.proargtypes[0] <> 'pg_catalog.cstring'::pg_catalog.regtype
      AND NOT p.proisagg
      AND pg_catalog.pg_function_is_visible(p.oid)
      AND nspname != 'pg_catalog'
;";

my $sth = $Dbh->prepare($sql);
$sth->execute();
while ( my ($schema, $function, $result_data_types, $input_data_types, $code, $language, $volatility) = $sth->fetchrow() )
{
	system("mkdir -p \"$dest_dir/$schema\"");
	$file="$dest_dir/$schema/$function.sql";
	open DATA, ">> $file" or die "can't open $file!\n";
	print DATA "CREATE OR REPLACE FUNCTION $schema.$function($input_data_types) RETURNS $result_data_types \nAS \$BODY\$$code\$BODY\$ LANGUAGE $language $volatility;\n\n\n\n\n\n";
	close (FILE);
	$count++;
}
$sth->finish();
$Dbh->disconnect;


print "\nDumped $count functions!\n";