How do I save functions to individual files in PostgreSQL?

Question

I maintain a legacy application that uses a PostgreSQL database.

The application is heavily dependent on stored procedures (aka functions).

I want to save these functions to files named after the function name so I can then use a VCS (version control system).

I know that I can save the code with the ALTER FUNCTION using PgAdmin but this only allows me to save one function at a time.

I am looking for a way to save all the functions automatically. Is there any way to script this task?

Answer 1

Run the following

1. mkdir /tmp/foo
2. chown postgresql:postgresql /tmp/foo

Then in psql, run this command whose inner workings I shamelessly stole from Craig's answer on SO,

SELECT FORMAT(
  'COPY (SELECT pg_get_functiondef(%s)) TO ''/tmp/foo/%s''',
  pp.oid,
  pp.proname
)
from pg_proc pp
inner join pg_namespace pn on (pp.pronamespace = pn.oid)
inner join pg_language pl on (pp.prolang = pl.oid)
where pl.lanname NOT IN ('c','internal') 
  and pn.nspname NOT LIKE 'pg_%'
  and pn.nspname <> 'information_schema';

Then run \gexec

Answer 2

I wrote a simple PHP script using pg_get_functiondef to get the source code and then save it to a file.

$connection = pg_connect("connection string details");
$result = pg_query($connection, "select proname, pg_get_functiondef(oid) from pg_proc where pronamespace=<namespace_oid>");

while ($row = pg_fetch_row($result)) {
    $fp = fopen("{$row[0]}.sql", 'w');
    fwrite($fp, $row[1]);
}

Answer 3

I can suggest 2 methods:

1. Use pg_dump -s to save only the schema of your application. This will create one large file in which you will have to look for the stored procedures and save them to individual files. This may be OK for you.

2. Query the pg_proc catalog table to create "CREATE FUNCTION" statements. See http://www.postgresql.org/docs/9.1/static/catalog-pg-proc.html for more information about which fields you will need.