Populate multidimensional array

Question

I'm trying populate a multidimensional array on PostgreSQL, but it not work. Below my code:

CREATE OR REPLACE FUNCTION teste()
  RETURNS void AS
$BODY$
DECLARE
    tarifas NUMERIC[7][24];
    a INTEGER;
    b INTEGER;

    BEGIN
        FOR a IN 0..6 LOOP
            RAISE NOTICE 'TESTE TESTE %', a;
            FOR b IN 0..23 LOOP
                RAISE NOTICE 'tarifas[%][%] = 0;', a, b;
                tarifas[a][b] = 0;
            END LOOP;
        END LOOP;
    END
$BODY$
  LANGUAGE plpgsql VOLATILE;

Answer 1

Postgres has a dedicated function for that purpose exactly: array_fill():

returns an array initialized with supplied value and dimensions, optionally with lower bounds other than 1

Use it:

CREATE OR REPLACE FUNCTION teste()
  RETURNS void AS
$func$
DECLARE
    tarifas numeric[7][24] := array_fill(0, ARRAY[7,24]);
    a int;
    b int;
BEGIN
   -- do something
END
$func$  LANGUAGE plpgsql;

Notes

• Array dimensions in numeric[7][24] are just documentation. The manual:

The current implementation does not enforce the declared number of dimensions either. Arrays of a particular element type are all considered to be of the same type, regardless of size or number of dimensions. So, declaring the array size or number of dimensions in CREATE TABLE is simply documentation; it does not affect run-time behavior.

• About the assignment operator in plpgsql: := or =:

  • The forgotten assignment operator "=" and the commonplace ":="

• It's generally not possible to write to an array element directly. You can concatenate or append / prepend elements. Or assign an array as a whole. Details in the manual. But a statement like this is not possible:

  tarifas[%][%] = 0

• Default lower bound of an array is 1, not 0. But you can define arbitrary array dimension. Example:

  SELECT '[2:3][2:4]={{7,7,7},{7,7,7}}'::int[]