35

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

vmb
  • 2,878
  • 15
  • 60
  • 90

2 Answers2

76

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.

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
  • Also i want to restore these functions to another db.How can i do that??.Functions only need to restore – vmb Dec 07 '12 at 09: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 Dec 07 '12 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 Jul 21 '16 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 Feb 01 '19 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 Oct 21 '21 at 15:40
  • SELECT pg_get_functiondef(oid) FROM pg_catalog.pg_proc where pronamespace in (select oid from pg_catalog.pg_namespace where nspname = 'public'); – Sérgio Jan 14 '22 at 18:24
  • I had to skip aggregate functions. So, I had to add "and f.proname not like '%agg%';" at the end of the "where" clause – viggy28 Jun 16 '22 at 15:02
  • @cerd In PostgreSQL 11 `proisagg` was replaced with `prokind`: `AND f.prokind = 'f'` – egofer Jun 29 '23 at 10:48
  • @egofer To clarify, `WHERE proisagg` would be repaced with `prokind = 'a'` ; since you want _non_ aggregates you use `WHERE prokind <> 'f'`. If you use `WHERE prokind = 'f'` instead, you'll omit window functions which you probably want to capture. I don't know why `pg_get_functiondef()` doesn't just output definitions for aggregates too; presumably you have to do that yourself by joining on `pg_aggregate` instead. – Craig Ringer Jul 09 '23 at 21:48
37

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
dezso
  • 2,894
  • 23
  • 29