38

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

Improve this question
0

2 Answers 2

Reset to default
81

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.

Improve this answer
9
  • Also i want to restore these functions to another db.How can i do that??.Functions only need to restore
    – vmb
    Commented Dec 7, 2012 at 9:46
  • @vmb The output is SQL function definitions, so pipe them into psql. See updated answer. Even better, create an extension and package them in it.
    – Craig Ringer
    Commented Dec 7, 2012 at 12:41
  • 7
    It should be noted, that pg_get_functiondef('proc_name'::regproc) DOES NOT dump the function's privileges (GRANT and REVOKE statements) which may be sometimes considered as part of the function definition.
    – NumberFour
    Commented Jul 21, 2016 at 21:18
  • 1
    This answer is great: by far the best option I looked at. It helped us in dumping our nearly 400 functions into a reliable backup file as well as to use for revision history. To do that, at the end the of the query I added an order by: ORDER BY pg_get_functiondef(f.oid) Thanks!
    – nbburn
    Commented Feb 1, 2019 at 23:06
  • 2
    Note this will not dump aggregate functions. It will produce an error ("ERROR: X is an aggregate function"). You need to add and f.proisagg is false to exclude those.
    – cerd
    Commented Oct 21, 2021 at 15:40
40

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
Improve this answer
2

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