In an interesting discussion with one of our Customers’ Development team, a question came up:<\/p>\n\n\n\n
“Do you have a query to get the list of input\/output parameters in the defined sequence for a given procedure\/function?”<\/strong><\/p><\/blockquote>\n\n\n\n
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.<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
Read on to know how to go about it – get the procedure\/function parameters list.<\/p>\n\n\n\n
<\/p>\n\n\n\n
Below is an example to get metadata of a procedure, you can try it out for a function as well.<\/p>\n\n\n\n
–Creating a few TYPES for demo purposes (input parameters to procedure)<\/h2>\n\n\n\n
CREATE TYPE zz_enum_test_type AS ENUM ('new', 'open', 'closed');\n\nCREATE TYPE zz_test_type AS \n (description text\n ,item_length int4\n ,item_breadth int4\n ,item_height int4);\n\nCREATE TYPE zz_test_lines_type AS\n (line_id int4\n ,line_type text\n ,description text);\n\nCREATE TYPE zz_test_hdr_type AS \n (hdr_id int4\n ,hdr_type text\n ,v_test_lines_type zz_test_lines_type);<\/code><\/pre>\n\n\n\n–Creating a procedure with the above types as input parameters<\/h2>\n\n\n\n
CREATE OR REPLACE PROCEDURE pro4\n (p1 in int\n ,p2 in zz_test_type\n ,p3 in zz_enum_test_type\n ,p_test_hdr_type in zz_test_hdr_type\n ,x1 in out int)\nLANGUAGE plpgsql\n as $$\n BEGIN\n SELECT 10*n into x1 where 1=1;\n RAISE NOTICE 'The result is %',x1;\n END;\n$$;<\/code><\/pre>\n\n\n\n–Create a recursive sub-parameters view<\/h2>\n\n\n\n
CREATE OR REPLACE VIEW zz_get_sub_parameters_v AS\nWITH RECURSIVE get_sub_parameters\nAS\n( \n SELECT t.typname::text as obj_name\n ,a.attnum::text as sub_parameter_sequence\n ,a.attname::text as parameter_name_disp\n ,a.attname::text as parameter_name\n ,pg_catalog.format_type ( a.atttypid, a.atttypmod ) as parameter_data_type\n FROM pg_catalog.pg_attribute a\n JOIN pg_catalog.pg_type t\n ON a.attrelid = t.typrelid\n JOIN pg_catalog.pg_namespace n\n ON ( n.oid = t.typnamespace )\n --where t.typname = 'zz_test_hdr_type' --uncomment this if you want to test \n union all \n SELECT g.obj_name::text\n ,(g.sub_parameter_sequence ||'.'||a1.attnum)::text as sub_parameter_sequence\n ,(g.parameter_name_disp ||'.'||a1.attname)::text as parameter_name_disp\n ,a1.attname::text as parameter_name\n ,pg_catalog.format_type( a1.atttypid, a1.atttypmod ) as parameter_data_type\n FROM pg_catalog.pg_attribute a1\n JOIN pg_catalog.pg_type t1\n ON a1.attrelid = t1.typrelid\n JOIN pg_catalog.pg_namespace n1\n ON ( n1.oid = t1.typnamespace )\n join get_sub_parameters g on t1.typname = g.parameter_data_type\n)\nSELECT * FROM get_sub_parameters \n ORDER BY sub_parameter_sequence;<\/code><\/pre>\n\n\n\n–Run the query to see the recursive data<\/h2>\n\n\n\n
SELECT * FROM zz_get_sub_parameters_v \n WHERE obj_name= 'zz_test_hdr_type';<\/code><\/pre>\n\n\n\nthe output would look like this:<\/p>\n\n\n\n
obj_name | sub_parameter_sequence | parameter_name_disp | parameter_name | parameter_data_type \n------------------+------------------------+-------------------------------+-------------------+---------------------\n zz_test_hdr_type | 1 | hdr_id | hdr_id | integer\n zz_test_hdr_type | 2 | hdr_type | hdr_type | text\n zz_test_hdr_type | 3 | v_test_lines_type | v_test_lines_type | zz_test_lines_type\n zz_test_hdr_type | 3.1 | v_test_lines_type.line_id | line_id | integer\n zz_test_hdr_type | 3.2 | v_test_lines_type.line_type | line_type | text\n zz_test_hdr_type | 3.3 | v_test_lines_type.description | description | text\n(6 rows)\n<\/code><\/pre>\n\n\n\n–Create a function that will give metadata of a given procedure<\/h2>\n\n\n\n
CREATE OR REPLACE FUNCTION zz_get_metadata_f(p_routine_schema name\n ,p_routine_name name)\nRETURNS TABLE (out_routine_schema text\n ,out_routine_type text\n ,out_routine_internal_name text\n ,out_routine_name text\n ,out_parameter_sequence text\n ,out_parameter_name text\n ,out_parameter_mode text\n ,out_parameter_data_type text\n ,out_enum_values text)\nLANGUAGE plpgsql\nAS $$\nBEGIN\n RETURN QUERY\n WITH RECURSIVE get_metadata\n AS\n (\n --non-recursive query start\n SELECT row_number() OVER (ORDER BY proc.specific_schema,proc.specific_name,args.ordinal_position)row_num\n ,proc.specific_schema::text as routine_schema\n ,proc.routine_type::text as routine_type\n ,proc.specific_name::text as routine_internal_name\n ,proc.routine_name::text as routine_name\n ,proc.external_language::text as ext_language\n ,(args.ordinal_position || '.' || 0)::text as parameter_sequence\n ,args.parameter_name::text\n ,args.parameter_mode::text\n ,(case when args.data_type='USER-DEFINED' then args.udt_name else args.data_type end) ::text as parameter_data_type\n ,args.udt_catalog::text \n ,args.udt_schema::text \n ,args.udt_name::text\n FROM information_schema.routines proc\n LEFT JOIN information_schema.parameters args\n ON proc.specific_schema = args.specific_schema\n AND proc.specific_name = args.specific_name\n 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\n --and proc.routine_type = 'PROCEDURE'\n --non-recursive query end\n UNION ALL \n --RECURSIVE QUERY START\n SELECT g.row_num AS row_num\n ,g.routine_schema::text \n ,g.routine_type::text\n ,g.routine_internal_name::text\n ,g.routine_name::text\n ,g.ext_language::text\n ,(substring(g.parameter_sequence,1,(length(g.parameter_sequence)-2))||'.'||z.sub_parameter_sequence)::text\n ,g.parameter_name||'.'||z.parameter_name_disp::text\n ,g.parameter_mode::text\n ,z.parameter_data_type::text\n ,null::text \n ,null::text \n ,null::text\n FROM zz_get_sub_parameters_v z\n INNER JOIN get_metadata g ON g.udt_name = z.obj_name --this is recursive join\n ) --recursive query end\n --with recursive get_metadata ends here. actual query is below\n SELECT routine_schema\n ,routine_type\n ,routine_internal_name\n ,routine_name\n ,parameter_sequence\n ,parameter_name\n ,parameter_mode\n ,parameter_data_type\n ,(SELECT string_agg(pg_enum.enumlabel,',')\n \t\t FROM pg_type \n \t\t JOIN pg_enum ON pg_enum.enumtypid = pg_type.oid\n where typname = zz.parameter_data_type)::text enum_values\n FROM get_metadata zz \n WHERE routine_name = p_routine_name\n ORDER BY row_num,parameter_sequence;\nEND; \n$$<\/code><\/pre>\n\n\n\nNow, the final call to get the parameters list in sequence<\/h2>\n\n\n\n
SELECT * FROM zz_get_metadata_f('apps','pro4');\n\n|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|\n|------------------|----------------|-------------------------|----------------|----------------------|---------------------------------------------|------------------|-----------------------|---------------|\n|apps |PROCEDURE |pro4_41623 |pro4 |1.0 |p1 |IN |integer | |\n|apps |PROCEDURE |pro4_41623 |pro4 |2.0 |p2 |IN |zz_test_type | |\n|apps |PROCEDURE |pro4_41623 |pro4 |2.1 |p2.description |IN |text | |\n|apps |PROCEDURE |pro4_41623 |pro4 |2.2 |p2.item_length |IN |integer | |\n|apps |PROCEDURE |pro4_41623 |pro4 |2.3 |p2.item_breadth |IN |integer | |\n|apps |PROCEDURE |pro4_41623 |pro4 |2.4 |p2.item_height |IN |integer | |\n|apps |PROCEDURE |pro4_41623 |pro4 |3.0 |p3 |IN |zz_enum_test_type |new,open,closed|\n|apps |PROCEDURE |pro4_41623 |pro4 |4.0 |p_test_hdr_type |IN |zz_test_hdr_type | |\n|apps |PROCEDURE |pro4_41623 |pro4 |4.1 |p_test_hdr_type.hdr_id |IN |integer | |\n|apps |PROCEDURE |pro4_41623 |pro4 |4.2 |p_test_hdr_type.hdr_type |IN |text | |\n|apps |PROCEDURE |pro4_41623 |pro4 |4.3 |p_test_hdr_type.v_test_lines_type |IN |zz_test_lines_type | |\n|apps |PROCEDURE |pro4_41623 |pro4 |4.3.1 |p_test_hdr_type.v_test_lines_type.line_id |IN |integer | |\n|apps |PROCEDURE |pro4_41623 |pro4 |4.3.2 |p_test_hdr_type.v_test_lines_type.line_type |IN |text | |\n|apps |PROCEDURE |pro4_41623 |pro4 |4.3.3 |p_test_hdr_type.v_test_lines_type.description|IN |text | |\n|apps |PROCEDURE |pro4_41623 |pro4 |5.0 |x1 |INOUT |integer | |\n<\/code><\/pre>\n\n\n\n