sql - How to take backup of functions only in Postgres

Question

I want to take backup of all functions in my postgres database.How to take backup of functions only in Postgres?

Answer 1

use pg_getfunctiondef; see system information functions. pg_getfunctiondef was added in PostgreSQL 8.4.

SELECT pg_get_functiondef('proc_name'::regproc);

To dump all functions in a schema you can query the system tables in pg_catalog; say if you wanted everything from public:

SELECT pg_get_functiondef(f.oid)
FROM pg_catalog.pg_proc f
INNER JOIN pg_catalog.pg_namespace n ON (f.pronamespace = n.oid)
WHERE n.nspname = 'public';

it's trivial to change the above to say "from all schemas except those beginning with pg_" instead if that's what you want.

In psql you can dump this to a file with:

psql -At dbname > /path/to/output/file.sql <<"__END__"
... the above SQL ...
__END__

To run the output in another DB, use something like:

psql -1 -v ON_ERROR_STOP -f /path/to/output/file.sql target_db_name

If you're replicating functions between DBs like this, though, consider storing the authorative copy of the function definitions as a SQL script in a revision control system like svn or git, preferably packaged as a PostgreSQL extension. See packaging extensions.