List user defined functions (UDFs) in PostgreSQL database

Bart Gawrych - Dataedo Team Bart Gawrych 2019-06-03

Table of Contents:


    Query below return all User Defined Functions and information about it in PostgreSQL database.

    Note

    PostgreSQL 11 introduce procedures, so we need to distinguish procedures and functions. In this case use: PostgreSQL 11 Query

    Universal

    Query

    select n.nspname as function_schema,
           p.proname as function_name,
           l.lanname as function_language,
           case when l.lanname = 'internal' then p.prosrc
                else pg_get_functiondef(p.oid)
                end as definition,
           pg_get_function_arguments(p.oid) as function_arguments,
           t.typname as return_type
    from pg_proc p
    left join pg_namespace n on p.pronamespace = n.oid
    left join pg_language l on p.prolang = l.oid
    left join pg_type t on t.oid = p.prorettype 
    where n.nspname not in ('pg_catalog', 'information_schema')
    order by function_schema,
             function_name;
    

    Columns

    • function_schema - name of the schema containing function
    • function_name - name of the function
    • function_language - language in which function is written
    • definition - actual source code of the function for interpreted languages, a link symbol or file name (depending on language)
    • function_arguments - function arguments type
    • return_type - function return type

    Rows

    • One row - represents one function
    • Scope of rows: - all functions in database
    • Ordered by - function schema, function name

    Sample results

    PostgreSQL 11+

    Query

    select n.nspname as schema_name,
           p.proname as specific_name,
           case p.prokind 
                when 'f' then 'FUNCTION'
                when 'p' then 'PROCEDURE'
                when 'a' then 'AGGREGATE'
                when 'w' then 'WINDOW'
                end as kind,
           l.lanname as language,
           case when l.lanname = 'internal' then p.prosrc
                else pg_get_functiondef(p.oid)
                end as definition,
           pg_get_function_arguments(p.oid) as arguments,
           t.typname as return_type
    from pg_proc p
    left join pg_namespace n on p.pronamespace = n.oid
    left join pg_language l on p.prolang = l.oid
    left join pg_type t on t.oid = p.prorettype 
    where n.nspname not in ('pg_catalog', 'information_schema')
    order by schema_name,
             specific_name;
    

    Columns

    • schema_name - name of the schema containing function/procedure
    • specific_name - name of the function/procedure
    • kind:
      • FUNCTION - normal function
      • PROCEDURE - procedure
      • AGGREGATE - aggregate function
      • WINDOW - window function
    • language - language in which function/procedure is written
    • definition - actual source code of the function/procedure for interpreted languages, a link symbol or file name (depending on language)
    • arguments - function/procedure arguments
    • return_type - return type

    Rows

    • One row - represents one function or procedure
    • Scope of rows: - all functions and procedures in database
    • Ordered by - schema name, specific name

    Sample results

    0
    There are no comments. Click here to write the first comment.

    By continuing to use our website, you consent to the use of cookies in accordance with our Privacy Policy (updated 28-04-2020).
    Accept