Search code examples
databasepostgresqlstored-procedurestransfer

Postgresql stored procedures transferring

Is there are way to get all stored procedures from 1st database and transfer it to the 2nd in creating database script? (without using pgAdmin or stuff like)

Solution

  • If you really interested only in the functions you can query pg_proc catalog and grab the CREATE FUNCTION statements for a given schema (let's say public):

    $ psql -AXtqc "SELECT pg_get_functiondef(oid)||';' FROM pg_proc p WHERE p.pronamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'public')" your_first_database

    If you really want only one step:

    $ psql -AXtqc "SELECT pg_get_functiondef(oid)||';' FROM pg_proc p WHERE p.pronamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'public')" your_first_database | psql your_second_database

    This is good for one-time-tasks, but not very good for definitive scripts, as the query may change from one version to other of PostgreSQL. Also, it consider function created by extensions as if they were not.