通过在postgresql中传递数组值，从另一个存储过程调用一个存储程序

Question

我无法将数组值作为输入传递给另一个存储过程。但是，当我尝试插入(表kvstore填充没有问题(数组值时，会加载该表。请告诉我如何将数组值从一个存储过程动态传递到另一个存储程序。

加薪通知打印如下值注意：值1:注意：数值2:

CREATE OR REPLACE FUNCTION test_schema.test_insert_choice(
p_member_crn  character varying,
p_preferencearray text[][],
p_created timestamp without time zone,
p_modified timestamp without time zone,
p_created_system smallint,
p_created_by character varying,
p_modified_by character varying,
p_modified_system smallint
)
RETURNS text AS
$BODY$
DECLARE     

lv_n_rtn                INTEGER ; 
lv_t_err_msg            TEXT ;
lv_t_err_dtl            TEXT ;

BEGIN   

/*FOR i IN 1 .. array_upper(p_preferencearray, 1) LOOP
INSERT INTO lcdm_main.kvstore (key, value)
VALUES (p_preferencearray[i][1], p_preferencearray[i][2]);
END LOOP;*/
FOR i IN 1 .. array_upper(p_preferencearray, 1) LOOP
raise notice 'Value 1: %', p_preferencearray[i][1];
raise notice 'Value 2: %', p_preferencearray[i][2];
perform  test_schema.test_insert_master(p_member_crn,
p_preferencearray[i][1],
p_preferencearray[i][2],
p_created,
p_modified,
p_created_system,
p_created_by,
p_modified_by,
p_modified_system);
END LOOP;
RETURN 0;
END;
$BODY$
LANGUAGE plpgsql VOLATILE SECURITY DEFINER
COST 100;```

Answer 1

找到了解决方案，用FOREACH替换FOR有助于解决问题

CREATE OR REPLACE FUNCTION test_schema.test_insert_choice(
p_member_crn  character varying,
p_preferencearray text[][],
p_created timestamp without time zone,
p_modified timestamp without time zone,
p_created_system smallint,
p_created_by character varying,
p_modified_by character varying,
p_modified_system smallint
)
RETURNS text AS
$BODY$
DECLARE     
lv_n_rtn                INTEGER ; 
lv_t_err_msg            TEXT ;
lv_t_err_dtl            TEXT ;
m   text[];
arr text[] := p_preferencearray;
BEGIN   

FOREACH m SLICE 1 IN ARRAY arr
LOOP
perform  test_schema.test_insert_master(p_member_crn,
m[1],  --Preference Id
m[2],  --Preference Value
p_created,
p_modified,
p_created_system,
p_created_by,
p_modified_by,
p_modified_system);
END LOOP;
RETURN 0;

END;
$BODY$
LANGUAGE plpgsql VOLATILE SECURITY DEFINER
COST 100;

感谢大家