CODEWITHSUNDEEP
arrayspostgresql
Maki
Maki

Reputation: 511

Adding element with index to an array in PostgreSQL

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

Upvotes: 1

Views: 2341

Answers (1)

Patrick
Patrick

Reputation: 32234

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;

Upvotes: 1

Related Questions