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
CREATE OR REPLACE PROCEDURE pro4
(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 $$
BEGIN
SELECT 10*n into x1 where 1=1;
RAISE NOTICE 'The result is %',x1;
END;
$$;–Create a recursive sub-parameters view
CREATE OR REPLACE VIEW zz_get_sub_parameters_v AS
WITH RECURSIVE get_sub_parameters
AS
(
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 $$
BEGIN
RETURN QUERY
WITH RECURSIVE get_metadata
AS
(
--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
,args.parameter_name::text
,args.parameter_mode::text
,(case when args.data_type='USER-DEFINED' then args.udt_name else args.data_type end) ::text as parameter_data_type
,args.udt_catalog::text
,args.udt_schema::text
,args.udt_name::text
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
,g.routine_schema::text
,g.routine_type::text
,g.routine_internal_name::text
,g.routine_name::text
,g.ext_language::text
,(substring(g.parameter_sequence,1,(length(g.parameter_sequence)-2))||'.'||z.sub_parameter_sequence)::text
,g.parameter_name||'.'||z.parameter_name_disp::text
,g.parameter_mode::text
,z.parameter_data_type::text
,null::text
,null::text
,null::text
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
,routine_type
,routine_internal_name
,routine_name
,parameter_sequence
,parameter_name
,parameter_mode
,parameter_data_type
,(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;
END;
$$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