Select statement with array is throwing error

Question

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?

Firstly one recommendation Please do not declare any variable name starting with special Character some times I faced the compiler get confused with this kind of naming convention. — smn_onrocks
– smn_onrocks, Commented Oct 13, 2016 at 6:19
I put '_' in front of all declared variables for distinction - never given me trouble. @a_horse_with_no_name that works, thanks. — Kaarel Purde
– Kaarel Purde, Commented Oct 13, 2016 at 6:28
Doing stuff like that in loops might indicate some inefficient handling of sets in your function. You might want to revisit that logic - but there is not enough information to really say that. But seeing a select inside a loop usually rings an alarm bell for me. — user330315
– user330315, Commented Oct 13, 2016 at 6:40

user330315 · Accepted Answer · 2016-10-13 06:38:47Z

1

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;

answered Oct 13, 2016 at 6:38

user330315

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Select statement with array is throwing error

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related