Pass array variable as parameter to another function

Question

I initialized two arrays in a function, one for numbers (list of amounts) and the other for text (list of invoices). I want to call another function from this one and pass these two arrays as parameters.
I can call the function this way and everything is OK:

select function_2('{110011-0115-7}'::text[], '{100}'::numeric[])

But when I want send a variable that holds a text array the array seems to be empty:

select function_2(ARRAY[invoice_list], ARRAY[amount_list]);

Function definition:

CREATE OR REPLACE FUNCTION function_1(OUT out_code1 integer
                                    , OUT out_message1 text)
  RETURNS RECORD AS
$BODY$
DECLARE
   counter      numeric;
   inv_cur      record;
   invoice_list text[];
   amount_list  numeric[]; 
BEGIN
   FOR inv_cur IN
      SELECT "ID", "AMOUNT"
      FROM "INVOICE"
      WHERE "ACCOUNT_ID" = in_account
   LOOP
       --Adding invoices to invoice array
        invoice_list[counter] := inv_cur."ID";

        --Adding amounts to amount array
        amount_list[counter] := inv_cur."AMOUNT";

        --Increasing counter for array indexes
        counter := counter + 1;
   END LOOP;

   --Caling other function
   SELECT * FROM function_2(ARRAY[invoice_list], ARRAY[amount_list]);
END
$BODY$
  LANGUAGE plpgsql VOLATILE

Other function definition:

 CREATE OR REPLACE FUNCTION function_2(in_invoices text[]
                                     , in_amounts numeric[]) ...

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

You could fix your function like this:

CREATE OR REPLACE FUNCTION function_1(in_account int
                                , OUT out_code1 int
                                , OUT out_message1 text)
  RETURNS RECORD AS
$func$
DECLARE
   counter    int := 1;  -- use int and initialize
   inv_cur      record;
   invoice_list text[];
   amount_list  numeric[]; 
BEGIN

   FOR inv_cur IN
      SELECT "ID", "AMOUNT"
      FROM   "INVOICE"
      WHERE  "ACCOUNT_ID" = in_account  -- !!!
      ORDER  BY "ID" -- don't you care about sort order?
   LOOP
       --Adding invoices to invoice array
        invoice_list[counter] := inv_cur."ID";

        --Adding amounts to amount array
        amount_list[counter] := inv_cur."AMOUNT";

        --Increasing counter for array indexes
        counter := counter + 1;
   END LOOP;

   --  Calling other function
   SELECT f.outfield_1, f.outfield_2  -- replace with actual names!
   INTO  out_code1, out_message1
   FROM  function_2(invoice_list, amount_list) f;

END
$func$  LANGUAGE plpgsql VOLATILE

Notes

The function variable counter must be initialized or it's set to NULL.
in_account is interpreted as column name of table "INVOICE" (which it probably isn't). Seems a function parameter is missing.
Replace my added ORDER BY "ID" with your actual desired sort order.
You need to assign the result of your final SELECT.
invoice_list and amount_list are arrays already. Only wrap them into another ARRAY layer if you want to add another array dimension (I doubt that.)

Now the function should work. It's still expensive nonsense ...

Array handling the way you do it is very expensive. Looping is expensive, too. Replace function_1() with this query:

SELECT f.*
FROM  (
   SELECT function_2(array_agg("ID"), array_agg("AMOUNT")) AS f
   FROM  (
      SELECT "ID", "AMOUNT"
      FROM   "INVOICE"
      WHERE  "ACCOUNT_ID" = in_account  -- your input here!
      ORDER  BY "ID"
      ) t1
   ) t2;

You could make do with a single query level:

SELECT (function_2(array_agg("ID" ORDER BY "ID")
                 , array_agg("AMOUNT" ORDER BY "ID"))).*
FROM   "INVOICE"
WHERE  "ACCOUNT_ID" = in_account;

But performance would be much worse. The version with subqueries has to sort only once and also calls the function only once:

How to avoid multiple function evals with the (func()).* syntax in an SQL query?

You can wrap that into an SQL function if need be.

Pass array variable as parameter to another function

1 Answers1

Notes