By using AWS re:Post, you agree to the AWS re:Post Terms of Use
AWS re:Postre:Post

Parameter name Information missing in system view PG_PROC_INFO.proargnames for not named parameters

0

In PostgreSQL, when creating a procedure using the following syntax:

CREATE OR REPLACE PROCEDURE schema3.position_test( OUT arg1 INTEGER, INOUT BOOLEAN, OUT arg3 BIGINT ) LANGUAGE 'plpgsql' SECURITY INVOKER AS $$ BEGIN -- Procedure logic here END; $$;

The system catalog pg_proc correctly displays the proargnames column as {arg1,"",arg3}. However, when executing the same query in Amazon Redshift and checking the system catalog pg_proc_info, the proargnames column displays as {arg1,arg3}.

My question is: How can I accurately determine the exact position of parameter names in Redshift?

Any insights or clarification on this difference would be greatly appreciated. Thank you!

Topics
Analytics
Tags
Amazon Redshift
Language
English
dashline
asked a month ago130 views
1 Answer
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 proargnames column.

Key Source:

profile picture
EXPERT
Osvaldo Marte
answered a month ago
  • dashline
    a month ago

    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?

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions

Relevant content