1개 답변
- 최신
- 최다 투표
- 가장 많은 댓글
1
In Amazon Redshift, the pg_proc_info system view does not provide the parameter names for unnamed parameters, as you have observed. This is a known behavior in Redshift, and it is different from how PostgreSQL handles it.
A workaround you can try is combining the information from proargnames, proargmodes, and proallargtypes. This should allow you to accurately determine the position, name (if named), mode, and data type of each parameter in the function or procedure definition.
This approach should help you work around the limitation of Redshift not displaying unnamed parameter names directly in the
proargnamescolumn.
Key Source:
CREATE OR REPLACE PROCEDURE schema7.position_test ( INTEGER, INOUT BOOLEAN, OUT arg BIGINT) LANGUAGE 'plpgsql' SECURITY INVOKER AS $$ BEGIN END; $$;
This is how I am creating a procedure
& as per your workaround I am fetching 3 columns
SELECT p.proname AS Procedure_Name, p.proargnames AS Parameter_Name, p.proargmodes AS Parameter_Mode, p.proallargtypes AS Parameter_Type FROM pg_catalog.pg_proc_info p JOIN pg_catalog.pg_namespace n ON p.pronamespace = n.oid JOIN pg_catalog.pg_language l ON p.prolang = l.oid WHERE n.nspname = 'schema7'
procedure_name parameter_name parameter_mode parameter_type
position_test {arg} {i,b,o} {23,16,20}
now how can i say whether arg is the first or second or third parameter?