Passing user-defined Type Array as input parameter to a function

Question

I have created a user-defined type in PostgreSQL(version 11.5).

CREATE TYPE key_type AS (key_no character varying(50), key_nm character varying(128));

I created below function with an input parameter of type key_type[] and output as TEXT.

create or replace function fn_net(inp IN key_type[]) returns TEXT as........

But I am unable to call the function, I have tried as below.

do  
$$  
declare  
v_key key_type[];  
v_res TEXT;  
begin  
v_key.key_no := '709R';  
v_key.key_nm := 'Risk';  
select * from fn_det(v_key) into v_res;  
raise notice '%', v_res;  
end;  
$$  
language plpgsql;

I get malformed array error or function does not exist error. Please help me as to how to pass the inputs correctly.

NOTE: I am able to run successfully if I specify input type as key_type instead of key_type[] but I need array type for the requirement.

user330315 · Accepted Answer · 2020-07-22 20:11:36Z

1

Your variable assignment is wrong, you need to provide the array index to which you want to assign an element.

When you are calling a function returning a single value, you don't need a SELECT in PL/pgSQL, just assign the result:

do  
$$  
declare  
  v_key key_type[];  
  v_res TEXT;  
begin  
  v_key[1] := ('709R', 'Risk'); -- first array element
  v_key[2] := ('711X', 'Risk2'); -- second array element
  v_res := fn_det(v_key);  
  raise notice '%', v_res;  
end;  
$$  
language plpgsql;

answered Jul 22, 2020 at 20:11

user330315

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Passing user-defined Type Array as input parameter to a function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related