Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 2

up vote 1 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]);
}
share|improve this answer
    
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

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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