potato300 potato300 - 1 month ago 7
SQL Question

Select statement with array is throwing error

This statement is throwing error:

FOR _i2 IN 1 .. array_upper(p_extra_info, 1) LOOP
....
SELECT currval('ad_extra_info_id_seq') INTO _new_extra_info_ids[_i2];
....
END LOOP;

ERROR: syntax error at or near "["
LINE 179: ...rrval('ad_extra_info_id_seq') INTO _new_extra_info_ids[_i2];
^
********** Error **********

ERROR: syntax error at or near "["
SQL state: 42601
Character: 7907


Variable
_new_extra_info_ids
is declared like this:
_new_extra_info_ids integer[];


Do you know what is wrong?

Answer

Use a direct assignment instead of a select:

FOR _i2 IN 1 .. array_upper(p_extra_info, 1) LOOP
    ....
    _new_extra_info_ids[_i2] := currval('ad_extra_info_id_seq');
    ....
END LOOP;