1

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
1

1 Answer 1

1

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;
3
  • You can also use || 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
    – user330315
    Commented Jul 6, 2015 at 13:51
  • Thx @Patrick , this helps. And what is the easiest way to display these array just to check them?
    – Maki
    Commented Jul 6, 2015 at 14:01
  • Quick-and-dirty: SELECT my_array::text. Not so q-n-d: array_to_string(my_array, ',')
    – Patrick
    Commented Jul 6, 2015 at 14:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.