PostgreSQL中的组合数组类型作为OUTPUT参数

Question

我试图在我的函数中将数据插入到复合数组类型中。它应接受来自复合数组类型的INPUT参数的数据，并将数据存储到相同类型的OUPUT参数中。

CREATE TYPE public.type_x_type AS (x integer); 

CREATE TYPE public.type_y_type AS(x integer,y integer); 

我的功能是

CREATE OR REPLACE FUNCTION GET_PRICE_PC_X 
    (
    IP_PRICE_INFO IN TYPE_X_TYPE[], 
    PC_COST OUT TYPE_Y_TYPE[], 
    OP_RESP_CODE OUT VARCHAR, 
    OP_RESP_MSG OUT VARCHAR 
) 
RETURNS RECORD AS $$ 
DECLARE 
    SELECTED_PRICE CURSOR(IP_PFCNTR INT) 
    FOR 
    SELECT ID, PHONE FROM CUSTOMER WHERE ID=IP_PFCNTR; 
    J NUMERIC(10); 
BEGIN 
J := 0; 
    FOR I IN ARRAY_LOWER(IP_PRICE_INFO,1) .. ARRAY_UPPER(IP_PRICE_INFO,1) 
    LOOP 
    FOR K IN SELECTED_PRICE(IP_PRICE_INFO[I].X) 
    LOOP 
    PC_COST := ROW(K.ID,K.PHONE); 
END LOOP; 
    END LOOP; 
    OP_RESP_CODE :='000'; 
    OP_RESP_MSG :='Success'; 
EXCEPTION 
WHEN OTHERS THEN 
    OP_RESP_CODE :='200'; 
    OP_RESP_MSG :=SQLERRM; 
END; 
$$ language 'plpgsql'; 

select * from GET_PRICE_PC_X(ARRAY[ROW(1)] :: TYPE_X_TYPE[]); 

而且我得到下面的错误。

PC_COST | OP_RESPONSE_CODE | OP_RESP_MSG 
--------------------------------------------------------- 
      | 200    | malformed array literal: "(1,30003)" 

我将在某处调用OUT类型，所以我需要将数据插入到数组中。

Answer 1

当你开发一个功能，然后不使用WHEN OTHERS。那么调试很糟糕。你的功能的问题是一个复合型到阵列的转让

PC_COST := ROW(K.ID,K.PHONE); 

这是错误的。可能你会做追加。

关键部分应该看起来像

J := 0; PC_COST := '{}'; 
FOR I IN ARRAY_LOWER(IP_PRICE_INFO,1) .. ARRAY_UPPER(IP_PRICE_INFO,1) 
LOOP 
    FOR K IN SELECTED_PRICE(IP_PRICE_INFO[I].X) 
    LOOP 
    PC_COST := PC_COST || ROW(K.ID,K.PHONE)::type_y_type; 
    END LOOP; 
END LOOP; 

你的功能可以通过一个查询所取代 - 也许少可读，但显著速度更快 - 嵌套查询循环可能会很慢（被更快地运行一个简单的SELECT比更琐碎SELECT S）：

CREATE OR REPLACE FUNCTION public.get_price_pc_x(ip_price_info type_x_type[], 
               OUT pc_cost type_y_type[], 
               OUT op_resp_code character varying, 
               OUT op_resp_msg character varying) 
RETURNS record 
LANGUAGE plpgsql STABLE 
AS $function$ 
BEGIN 
    pc_cost := ARRAY(SELECT ROW(id, phone)::type_y_type 
        FROM customer 
        WHERE id IN (SELECT (unnest(ip_price_info)).x)); 
    OP_RESP_CODE :='000'; 
    OP_RESP_MSG :='Success'; 
EXCEPTION 
    WHEN OTHERS THEN 
    OP_RESP_CODE :='200'; 
    OP_RESP_MSG :=SQLERRM; 
END; 
$function$; 

注：使用NUMERIC类型循环变量是一个错误的观念 - 这种类型是昂贵的，只有金钱或贵重计算应该被使用。 int类型在这个地方是绝对正确的。

通常你可以减少你的功能更多 - 错误处理不应该存在 - 没有一个原因，这个功能应该失败 - （不是可以处理的原因）

CREATE OR REPLACE FUNCTION public.get_price_pc_x(ip_price_info type_x_type[]) 
RETURNS type_y_type[] 
LANGUAGE sql STABLE 
AS $function$ 
    SELECT ARRAY(SELECT ROW(id, phone)::type_y_type 
        FROM customer 
        WHERE id IN (SELECT (unnest(ip_price_info)).x)); 
$function$;