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?

up vote 3 down vote accepted

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]);
}
  • This is not bad either, but personally I'm in favour of lower level stuff like bash/sed (or any shell, minus standard Windows cmd.exe :) for things like these. – dezso Sep 19 '12 at 6:52
  • @dezso how can you accomplish the same task with bash? why do you prefer lower level stuff? – Fernando Sep 21 '12 at 0:28
  • With just psql, dba.stackexchange.com/a/175841/2639 – Evan Carroll Jun 9 '17 at 2:00

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.

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

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.