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?

2
  • 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) Commented Nov 17, 2017 at 7:10
  • @a_horse_with_no_name - Ahh, thanks, completely missed pg_proc.prosrc. I was focused on information_schema.routines. Commented Nov 17, 2017 at 15:09

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.