PL/pgSQL control structures for lists / arrays

Question

Is it possible to use something like this in Postgres? This is the 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;

Can I declare a list of characters and list of numbers as function input parameters?
I have found some examples with FOREACH element but I don't know how to grab a certain element from number list like in Oracle with p_amount_list(r).

Hernia

Explaining more about what this does, without reference to Oracle specifics, might get you a better answer. – Planogamete

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

Major points

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

• Postgres does not have "table types" like Oracle. Use array types, an array of numeric in this case: numeric[].

• The expression IF p_amount_list <> '{}' ... would rule out NULL and "empty array" alike. No need for a second check like in your original. But the IF is not needed at all. For NULL or empty array, the loop isn't entered anyway.

• r holds the element itself, not an index to it. (Therefore it must be a matching data type.)

This goes to demonstrate basic syntax of a FOREACH loop in a plpgsql function. Otherwise it would be expensive nonsense, better replaced with a much simpler and faster:

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

Creighton

And what about if I want to pass list of characters? Can I use anyarray or? – Hernia

Should the procedure like like this in that case? – Hernia

CREATE OR REPLACE FUNCTION CREATE_PAYMENT3(p_inv_list anyarray) RETURNS anyarray AS $func$ DECLARE s_inv text; -- init variable! r text; BEGIN FOREACH r IN ARRAY p_inv_list LOOP s_inv := s_inv || r; END LOOP; RETURN s_inv; END $func$ LANGUAGE plpgsql – Hernia

@Maki: You might be trying to reinvent array_to_string(). Else, start a new question. Comments are not the place for that. You can always link to this one for context. – Creighton