CODEWITHSUNDEEP
postgresqlplpgsqlpostgresql-9.4
user1720827
user1720827

Reputation: 147

Composite Array Type as OUTPUT parameter in PostgreSQL

I'm trying to insert data into composite array type in my function. It should accept data from composite array type of INPUT parameter and store data into OUPUT parameter of same type.

CREATE TYPE public.type_x_type AS (x integer);

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

My function is 

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[]);

And I'm getting the below error.

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

I'll be calling that OUT type somewhere, so I need the data to be inserted into array.

Upvotes: 1

Views: 1230

Answers (1)

Pavel Stehule
Pavel Stehule

Reputation: 45825

When you develop a function, then doesn't use WHEN OTHERS. The debugging is terrible then. The problem of your function is a assignment a composite type to a array 

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

This is wrong. Probably you would to do append.

The critical part should to look like

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;

Your function can be replaced by one query - maybe less readable, but significantly faster - loops with nested queries can be slow (is faster run one simple SELECT than more trivial SELECTs):

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$;

Note: using NUMERIC type for cycle variable is a wrong idea - this type is expensive and should be used only for money or precious calculation. The int type is absolutely correct in this place.

Usually you can reduce you function more - the error handling should not be there - there is not a reason why this function should fail - (not a reason that can be handled)

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$;

Upvotes: 2

Related Questions