PL / pgSQL control structures for lists / arrays

Question

Can I use something like this in Postgres? This is an example from PL / SQL, what I want to do:

PROCEDURE CREATE_PAYMENT(P_AMOUNT IN NUMBER, P_INVOICE_LIST IN SIMPLEARRAYTYPE, P_AMOUNT_LIST IN NUMBER_TABLE -- pass list of amounts . . .) s_chk_amnt NUMBER; invoice_list SIMPLEARRAYTYPE; amount_list NUMBER_TABLE; BEGIN -- check if amount list is null or contains zeros IF p_amount_list IS NOT NULL AND p_amount_list.COUNT <> 0 THEN FOR r IN p_amount_list.FIRST..p_amount_list.LAST LOOP s_chk_amnt := s_chk_amnt + p_amount_list(r); END LOOP; END IF; 

Is it possible to declare a list of characters and a list of numbers as input parameters to a function?
I found some examples with the FOREACH element , but I do not know how to grab a specific element from a list of numbers, for example, in Oracle using p_amount_list(r) .

Answer 1

 CREATE OR REPLACE FUNCTION CREATE_PAYMENT(p_amount_list numeric[]) RETURNS numeric AS $func$ DECLARE s_chk_amnt numeric := 0; -- init variable! r numeric; BEGIN -- IF p_amount_list <> '{}' THEN -- just noise FOREACH r IN ARRAY p_amount_list LOOP s_chk_amnt := s_chk_amnt + r; END LOOP; -- END IF; RETURN s_chk_amnt; END $func$ LANGUAGE plpgsql 

Highlights

• Oracle number numeric in Postgres . But if you don't have fractional digits, you prefer to use int or bigint in Postgres. About type mapping between Oracle and Postgres.

• Postgres does not have "table types," for example, Oracle . Use array types , the numeric array in this case: numeric[] .

• IF p_amount_list <> '{}' ... expression IF p_amount_list <> '{}' ... will exclude NULL and an "empty array". No need to re-check, as in the original. But IF is not needed at all. For NULL or an empty array, the loop is still not entered.

• r contains the element itself, not the index for it. (Therefore, it must be an appropriate data type.)

This demonstrates the basic syntax of the FOREACH loop in the plpgsql function. Otherwise, it would be expensive nonsense, better replaced much easier and faster:

 SELECT sum(elem) AS sum_amount FROM unnest(p_amount_list) elem;