I want to take backup of all functions in my postgres database.How to take backup of functions only in Postgres?
pg_getfunctiondef; see system information functions.
pg_getfunctiondef was added in PostgreSQL 8.4.
To dump all functions in a schema you can query the system tables in
pg_catalog; say if you wanted everything from
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.
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.