0

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

2

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.

1

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.

  • Thanks I am trying using this option : pg_dump -U postgres -n public -v -Fc -f /tmp/public.dump -d dbname and I restore using pg_restore. After I check, the functions is there. But is there a way to check that these functions will run on my new server?? – buzzing_bee Jan 31 at 8:32
  • @buzzing_bee: well that is an entirely different question. The only way to find out is to create them there and run them. – a_horse_with_no_name Jan 31 at 8:35
  • I see then. So that the functions is copied (or restored) but that doesn't mean it will run according to schedule? – buzzing_bee Jan 31 at 8:38
  • what "schedule" are you talking about? – a_horse_with_no_name Jan 31 at 8:39
  • Sorry if it's confusing. I'm new to postgres. I thought that a functions in a database can be scheduled coz my fellow colleague mentioned that he needs me to make sure this specific function runs every night. Thus, I need to copied the function to new server – buzzing_bee Jan 31 at 8:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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