Get functions DDL command

问题:

I need to get DDL of every function in my db for versioning purpose. Here is the query, but it works only for functions in public schema. When I'm trying to use it to get create of functions which exists in public and match schema I get error that subquery returns too many rows.

By checking all values from pg_proc using this query:

select * from pg_proc where proname = 'match_group_1_3_2';

Only values that changes is pronamespace:

How to distinguish both (or more) function? How to distinguish them if they are in the same schema but with different arguments? (by overloading)

I need to use native postgres catalog, not ANSI type - due to performance issues.

回答1:

There are more system catalog information functions to give you the additional information.
And you can get the schema name from the system table pg_namespace:

SELECT n.nspname AS schema_name
      ,p.proname AS function_name
      ,pg_get_functiondef(p.oid) AS func_def
      ,pg_get_function_arguments(p.oid) AS args
      ,pg_get_function_result(p.oid) AS result
FROM   pg_proc p
JOIN   pg_namespace n ON n.oid = p.pronamespace
WHERE  p.proname ILIKE '%match_group_1_3_2%';

This should find functions in any schema in the database.