This question is a followup to some of the answers at this question:
How to display the function, procedure, triggers source code in postgresql?
(also see here: https://stackoverflow.com/a/20549944/538962)
While the answers to this question "work", I am running into a problem.
If I query for a list of functions, and in that query, include a sub-query to return the code for each function, the query breaks when a schema includes two or more functions with the same name (and different function signatures). For example, some_function(arg1 integer)
vs some_function(arg1 char)
: functions have the same name yet different arguments.
Here's the query:
SELECT
n.nspname AS schema
, p.proname AS function_or_trigger_name
-- THIS SUB-QUERY RETURNS MULTIPLE RECORDS IF
-- SEPARATE FUNCTIONS HAVE THE SAME NAME AND DIFFERENT SIGNATURES
, (
SELECT
r.routine_definition
FROM
information_schema.routines AS r
WHERE
r.specific_schema LIKE n.nspname
AND r.routine_name LIKE p.proname
AND r.routine_schema <> 'information_schema'
AND r.specific_schema <> 'pg_catalog'
) AS code_content
, pg_catalog.pg_get_function_result(p.oid) AS result_data_type
, pg_catalog.pg_get_function_arguments(p.oid) AS argument_data_types
, CASE
WHEN p.proisagg THEN 'agg'
WHEN p.proiswindow THEN 'window'
WHEN p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype THEN 'trigger'
ELSE 'normal'
END AS func_type
FROM
pg_catalog.pg_proc p
LEFT JOIN pg_catalog.pg_namespace n
ON n.oid = p.pronamespace
WHERE
-- pg_catalog.pg_function_is_visible(p.oid)
n.nspname <> 'pg_catalog'
AND n.nspname <> 'information_schema'
ORDER BY
schema DESC
, func_type
, function_or_trigger_name;
So the question is: is there a way to make a JOIN
in the sub-query to prevent duplicates when functions have the same name?