PostgreSQL: Copy Table Functions to another database schema

Question

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

[SOLVED]
Sorry for updating this question after 2 years. The questions has been answered and I'm using the command given by @dezso with slight modification which is:
pg_dump -Fc --username=dbuser --port=5432 --encoding=utf-8 --clean --no-owner -w -d dbname -f dbname.dump

as for calling the functions, it is as @a_horse_with_no_name suggest, I'm calling it using cron.

Thank you guys. cheers

Answer 1

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.

Answer 2

You can use pg_dump but exclude the data and the table definitions, e.g.:

pg_dump -U buzzing_bee -d your_db --schema=public --exclude-table=% --schema-only --file=functions.sql

This will however still include the DDL for views, triggers, types or other non-table objects. You will need to edit the generated file and remove those.