PostgreSQL – Copy Table Functions to Another Database Schema

postgresql

as the title said. Can we move or copy existing table functions to another database schema?

I can get all the functions from a database schema using command below (Taken from here):

SELECT routine_name FROM information_schema.routines WHERE routine_type='FUNCTION' AND specific_schema='public';

And I also can get the function code details using command below (Taken from here):

\df+ public.<function_name>

As I need to move the database to the new server, so I need to copy all table functions inside a specific schema. Say I have a functions called update() inside a schema analytics.

Can we copy this functions to another database ?
and is there any command to see when this functions will be run?

Note that I am using PostgreSQL 11 and Ubuntu 18.04

Best Answer

You can't tell pg_dump to dump functions only. You can exclude tables, but you still have to deal with other objects (like a_horse_with_no_name said).

However, you can make a dump without data (-s) and filter it upon restoring. Note the -Fc part: this will produce a file suitable for pg_restore, which is able to consume a custom-made table of contents.

First take the dump:

pg_dump -U username -Fc -s -n public -f dump_test your_database

Then create a list of the functions:

pg_restore -l dump_test | grep FUNCTION > function_list

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

pg_restore -U username -d your_other_database -L function_list dump_test

Note that this is a nearly verbatim copy of my earlier answer on stackoverflow.

Related Question