0

How to take entire database store procedures full scripts same as the MS SQL 2008 R2 Generate Script feature. Need help.

barshan
  • 37
  • 7

1 Answers1

0

The following query will return the complete DDL statements for each function that is owned by the current user in the schema public:

select pg_get_functiondef(p.oid)||';'
from pg_proc p
  join pg_namespace n on n.oid = p.pronamespace
  join pg_user u on u.usesysid = p.proowner
where n.nspname = 'public'
  and p.prokind <> 'a'
  and u.usename = current_user;

prokind <> 'a' is necessary because pg_get_functiondef() doesn't work with user defined aggregate functions.

If you want the functions from a different schema or owner, just change the where conditions accordingly.

You can spool the output to a file to get a SQL script containing all functions.