Can I specify an index (using some counter) to an element when adding elements to an array in PostgreSQL?
For example, I have this code from Oracle PL/SQL and I want to write it in PostgreSQL:
invoice_list.EXTEND;
invoice_list(counter):= cfp_cur.invoice_no; --adding some invoices from a loop to a text array
amount_list.EXTEND;
amount_list(counter) := inv_amount; --adding some amounts to a number array
counter := counter + 1; --increasing counter
PostgreSQL does not have an EXTEND method like Oracle does. PostgreSQL, however, can extend 1-dimensional arrays automatically by assigning array elements beyond the end of the current array length.
In your example, this becomes very simply:
CREATE FUNCTION some_function () RETURNS something AS $$
DECLARE
invoice_list text[];
amount_list float8[];
BEGIN
-- Do something
...
FOR counter IN 1 ... 10 LOOP
-- get current values for cfp_cur.invoice_no and inv_amount
invoice_list[counter] := cfp_cur.invoice_no;
amount_list[counter] := inv_amount;
END LOOP;
-- Do something with the arrays
...
RETURN;
END;
|| to concatenate an element to the end of an array invoice_list := invoice_list || cfp_cur.invoice_no postgresql.org/docs/current/static/functions-array.html
SELECT my_array::text. Not so q-n-d: array_to_string(my_array, ',')
||to append an element to an array: postgresql.org/docs/current/static/functions-array.html