CODEWITHSUNDEEP
postgresqlstored-procedurespostgresql-9.2
Borys
Borys

Reputation: 3034

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:

enter image description here

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.

Upvotes: 1

Views: 8978

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656471

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 func_args
     , pg_get_function_result(p.oid)    AS func_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.

Upvotes: 10

Related Questions