Fetch parameters & metadata datatype of Procedure/Function

In an interesting discussion with one of our Customers’ Development team, a question came up:

“Do you have a query to get the list of input/output parameters in the defined sequence for a given procedure/function?”

Obviously, we did two things a) Do a quick search to see if anyone had this requirement before; b) See if there was something already available as part of PostgreSQL catalog tables.

We’re not pretty happy with the search results. Of course, there were a few queries that did give basic input parameters, but, if the input parameters are having USER-DEFINED TYPES, then we’re at loss.

Read on to know how to go about it – get the procedure/function parameters list.

Below is an example to get metadata of a procedure, you can try it out for a function as well.

–Creating a few TYPES for demo purposes (input parameters to procedure)

CREATE TYPE zz_enum_test_type AS ENUM ('new', 'open', 'closed');

CREATE TYPE zz_test_type AS      
  (description        text
  ,item_length        int4
  ,item_breadth       int4
  ,item_height        int4);

CREATE TYPE zz_test_lines_type AS
  (line_id            int4
  ,line_type          text
  ,description        text);

CREATE TYPE zz_test_hdr_type AS  
  (hdr_id             int4
  ,hdr_type           text
  ,v_test_lines_type  zz_test_lines_type);

–Creating a procedure with the above types as input parameters

  (p1              in      int
  ,p2              in      zz_test_type
  ,p3              in      zz_enum_test_type
  ,p_test_hdr_type in      zz_test_hdr_type
  ,x1              in out  int)
LANGUAGE plpgsql
    as $$
      SELECT 10*n into x1 where 1=1;
      RAISE NOTICE 'The result is %',x1;

–Create a recursive sub-parameters view

CREATE OR REPLACE VIEW zz_get_sub_parameters_v AS
WITH RECURSIVE get_sub_parameters
  SELECT t.typname::text                                     as  obj_name
          ,a.attnum::text                                      as  sub_parameter_sequence
          ,a.attname::text                                     as  parameter_name_disp
          ,a.attname::text                                     as  parameter_name
          ,pg_catalog.format_type ( a.atttypid, a.atttypmod )  as  parameter_data_type
     FROM pg_catalog.pg_attribute a
     JOIN pg_catalog.pg_type      t
       ON a.attrelid = t.typrelid
     JOIN pg_catalog.pg_namespace n
       ON ( n.oid = t.typnamespace )
    --where t.typname = 'zz_test_hdr_type'  --uncomment this if you want to test 
    union all 
    SELECT g.obj_name::text
          ,(g.sub_parameter_sequence ||'.'||a1.attnum)::text   as sub_parameter_sequence
          ,(g.parameter_name_disp    ||'.'||a1.attname)::text  as parameter_name_disp
          ,a1.attname::text                                    as parameter_name
          ,pg_catalog.format_type( a1.atttypid, a1.atttypmod ) as parameter_data_type
     FROM pg_catalog.pg_attribute a1
     JOIN pg_catalog.pg_type      t1
       ON a1.attrelid = t1.typrelid
     JOIN pg_catalog.pg_namespace n1
       ON ( n1.oid = t1.typnamespace )
     join get_sub_parameters g on t1.typname = g.parameter_data_type
SELECT * FROM get_sub_parameters 
  ORDER BY sub_parameter_sequence;

–Run the query to see the recursive data

SELECT * FROM zz_get_sub_parameters_v  
  WHERE obj_name= 'zz_test_hdr_type';

the output would look like this:

     obj_name     | sub_parameter_sequence |      parameter_name_disp      |  parameter_name   | parameter_data_type 
 zz_test_hdr_type | 1                      | hdr_id                        | hdr_id            | integer
 zz_test_hdr_type | 2                      | hdr_type                      | hdr_type          | text
 zz_test_hdr_type | 3                      | v_test_lines_type             | v_test_lines_type | zz_test_lines_type
 zz_test_hdr_type | 3.1                    | v_test_lines_type.line_id     | line_id           | integer
 zz_test_hdr_type | 3.2                    | v_test_lines_type.line_type   | line_type         | text
 zz_test_hdr_type | 3.3                    | v_test_lines_type.description | description       | text
(6 rows)

–Create a function that will give metadata of a given procedure

CREATE OR REPLACE FUNCTION zz_get_metadata_f(p_routine_schema  name
                                            ,p_routine_name    name)
RETURNS TABLE (out_routine_schema            text
              ,out_routine_type              text
              ,out_routine_internal_name     text
              ,out_routine_name              text
              ,out_parameter_sequence        text
              ,out_parameter_name            text
              ,out_parameter_mode            text
              ,out_parameter_data_type       text
              ,out_enum_values               text)
LANGUAGE plpgsql
AS $$
            WITH RECURSIVE get_metadata
                --non-recursive query start
              SELECT row_number() OVER (ORDER BY proc.specific_schema,proc.specific_name,args.ordinal_position)row_num
                    ,proc.specific_schema::text   as routine_schema
                    ,proc.routine_type::text      as routine_type
                    ,proc.specific_name::text     as routine_internal_name
                    ,proc.routine_name::text      as routine_name
                    ,proc.external_language::text as ext_language
                    ,(args.ordinal_position || '.' || 0)::text  as parameter_sequence
                    ,(case when args.data_type='USER-DEFINED' then args.udt_name else args.data_type end)  ::text         as parameter_data_type
              FROM information_schema.routines     proc
              LEFT JOIN information_schema.parameters   args
                   ON proc.specific_schema = args.specific_schema
                  AND proc.specific_name   = args.specific_name
                WHERE proc.routine_schema  = coalesce(p_routine_schema,proc.routine_schema) --give your custom schema here or comment it. if you comment it, it might take time to execute
                --and proc.routine_type    = 'PROCEDURE'
                --non-recursive query end
            UNION ALL 
                --RECURSIVE QUERY START
                SELECT g.row_num AS row_num
                  FROM zz_get_sub_parameters_v z
            INNER JOIN get_metadata g ON g.udt_name = z.obj_name  --this is recursive join
            ) --recursive query end
              --with recursive get_metadata ends here. actual query is below
            SELECT routine_schema
                  ,(SELECT string_agg(pg_enum.enumlabel,',')
            		      FROM pg_type 
            		      JOIN pg_enum ON pg_enum.enumtypid = pg_type.oid
                     where typname = zz.parameter_data_type)::text enum_values
              FROM get_metadata      zz 
             WHERE routine_name    = p_routine_name
             ORDER BY row_num,parameter_sequence;

Now, the final call to get the parameters list in sequence

SELECT * FROM zz_get_metadata_f('apps','pro4');

|out_routine_schema|out_routine_type|out_routine_internal_name|out_routine_name|out_parameter_sequence|out_parameter_name                           |out_parameter_mode|out_parameter_data_type|out_enum_values|
|apps              |PROCEDURE       |pro4_41623               |pro4            |1.0                   |p1                                           |IN                |integer                |               |
|apps              |PROCEDURE       |pro4_41623               |pro4            |2.0                   |p2                                           |IN                |zz_test_type           |               |
|apps              |PROCEDURE       |pro4_41623               |pro4            |2.1                   |p2.description                               |IN                |text                   |               |
|apps              |PROCEDURE       |pro4_41623               |pro4            |2.2                   |p2.item_length                               |IN                |integer                |               |
|apps              |PROCEDURE       |pro4_41623               |pro4            |2.3                   |p2.item_breadth                              |IN                |integer                |               |
|apps              |PROCEDURE       |pro4_41623               |pro4            |2.4                   |p2.item_height                               |IN                |integer                |               |
|apps              |PROCEDURE       |pro4_41623               |pro4            |3.0                   |p3                                           |IN                |zz_enum_test_type      |new,open,closed|
|apps              |PROCEDURE       |pro4_41623               |pro4            |4.0                   |p_test_hdr_type                              |IN                |zz_test_hdr_type       |               |
|apps              |PROCEDURE       |pro4_41623               |pro4            |4.1                   |p_test_hdr_type.hdr_id                       |IN                |integer                |               |
|apps              |PROCEDURE       |pro4_41623               |pro4            |4.2                   |p_test_hdr_type.hdr_type                     |IN                |text                   |               |
|apps              |PROCEDURE       |pro4_41623               |pro4            |4.3                   |p_test_hdr_type.v_test_lines_type            |IN                |zz_test_lines_type     |               |
|apps              |PROCEDURE       |pro4_41623               |pro4            |4.3.1                 |p_test_hdr_type.v_test_lines_type.line_id    |IN                |integer                |               |
|apps              |PROCEDURE       |pro4_41623               |pro4            |4.3.2                 |p_test_hdr_type.v_test_lines_type.line_type  |IN                |text                   |               |
|apps              |PROCEDURE       |pro4_41623               |pro4            |4.3.3                 |p_test_hdr_type.v_test_lines_type.description|IN                |text                   |               |
|apps              |PROCEDURE       |pro4_41623               |pro4            |5.0                   |x1                                           |INOUT             |integer                |               |

As you can see from the above query output the Procedure pro4 parameters & metadata were fetched.

Let us know what you think about it.

Talk to us if you need assistance with your PostgreSQL databases.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>