How to assign value to a multi dimensional array in postgresql procedure

Question

I would like to assign values dynamically into an array using PostgreSQL.

what I have done so far:- I have declared _tmp_user_data as _tmp_user_data character varying[][]; and I have written the array assignment inside a posgresql cursor

    _counter := 0;
    open _user_data_cursor for execute _sql_user_data;
    Loop
        FETCH _user_data_cursor INTO _tmp_user_name, _tmp_crop_name;
        If found Then
            RAISE NOTICE '_tmp_crop_name name:%', _tmp_crop_name;
            _tmp_user_data[_counter]['user_name'] := _tmp_user_name;
        else
            exit;
        end if;
        _counter := _counter + 1;
    END LOOP;
    CLOSE _user_data_cursor;

I have been getting the following error

ERROR: invalid input syntax for integer: "user_name"

Answer 1

The core problem is that Postgres arrays only accept integer subscripts, not strings.

_tmp_user_data[_counter]['user_name']

Something like this could work:

_tmp_user_data[_counter][1]  -- _counter being an integer variable

Start reading the manual here.

There is no data type varying[][] (though that spelling is tolerated). The data type is character varying[] (or varchar[]). Postgres arrays use the same type for any number of dimensions. See:

• Use string[][] with ngpsql

And Postgres arrays are 1-based by default. The manual:

By default PostgreSQL uses a one-based numbering convention for arrays, that is, an array of n elements starts with array[1] and ends with array[n].

Related:

• Normalize array subscripts for 1-dimensional array so they start with 1

Typically, you don't need an explicit cursor to loop in PL/pgSQL. See:

• Truncating all tables in a Postgres database
• Loop on tables with PL/pgSQL in Postgres 9.0+

And more often than not, there is a superior solution without any looping at all ...