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.

Answer 2

You can't tell pg_dump to dump only functions. However, you can make a dump without data (-s or --schema-only) and filter it on restoring. Note the --format=c (also -Fc) part: this will produce a file suitable for pg_restore.

First take the dump:

pg_dump -U username --format=c --schema-only -f dump_test your_database 

Then create a list of the functions:

pg_restore --list dump_test | grep FUNCTION > function_list 

And finally restore them (-L or --use-list specifies the list file created above):

pg_restore -U username -d your_other_database -L function_list dump_test