0

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?

mg1075
  • 17,985
  • 8
  • 59
  • 100
  • Why the sub-select? The functions code is stored in `pg_proc.prosrc` - you just need to include _that_ column instead of the sub-select (e.g. `p.prosrc as code_content`) –  Nov 17 '17 at 07:10
  • @a_horse_with_no_name - Ahh, thanks, completely missed `pg_proc.prosrc`. I was focused on `information_schema.routines`. – mg1075 Nov 17 '17 at 15:09

0 Answers0