snippetsqlMinor

How do I save functions to individual files in PostgreSQL?

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

postgresqlindividualsavefileshowfunctions

Problem

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?

Solution

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=");

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

Code Snippets

$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]);
}

Context

StackExchange Database Administrators Q#24452, answer score: 4

Revisions (0)

No revisions yet.