Invalid for loop in a function

Question

I am trying to make the following function working:

CREATE OR REPLACE FUNCTION validate_count(devices TEXT[], campaign_id INTEGER) RETURNS void AS $$ DECLARE devices_array TEXT[] := devices; devices_count INTEGER := array_length(devices, 1); row_id INTEGER := campaign_id; BEGIN FOR device IN unnest(devices_array) LOOP IF my_count('my_table', device, row_id) != 1; RAISE EXCEPTION 'invalid_count %', row_id ENDIF END LOOP; END $$ LANGUAGE plpgsql;

my_count is a working function which returns INTEGER.

The definition fails with the error:

ERROR: syntax error at or near "unnest" LINE 7: FOR device IN unnest(devices_array) LOOP

Could you spot the issue? Thanks!

I am planning to call the function as follows:

select validate_count('{foo, bar}', 1)

score 1 · Accepted Answer · 2019-03-14 14:33:02Z

Use a FOREACH loop:

CREATE OR REPLACE FUNCTION validate_count(devices TEXT[], campaign_id INTEGER) RETURNS void AS $$ DECLARE device text; devices_count INTEGER := array_length(devices, 1); BEGIN FOREACH device IN ARRAY devices LOOP IF my_count('my_table', device, campaign_id) <> 1 then RAISE EXCEPTION 'invalid_count %', campaign_id; END IF; END LOOP; END $$ LANGUAGE plpgsql;

I fixed the ; to use THEN but it's still fails on syntax.. could you please check why? thx in advance!
@AndreyDeineko: sorry, forgot to declare the loop variable. Btw: there is no need to re-assign the parameters. You can just use them directly.

csd · Accepted Answer · 2019-03-14 14:33:20Z

A few things need to be fixed to make this valid.

The FOR loop needs SELECT before the unnest, i.e.:

FOR device IN SELECT unnest(devices_array) LOOP

You need to declare device up top, for example:

DECLARE device RECORD;

The IF statement needs a THEN instead of a ;, i.e.:

IF my_count('my_table', device, row_id) != 1 THEN

The RAISE EXCEPTION statement needs a semicolon at the end of the line, i.e.:

RAISE EXCEPTION 'invalid_count %', row_id;

The END for the IF statement should be END IF;.

The END for the LOOP statement should be END LOOP;.

Here's the net result:

CREATE OR REPLACE FUNCTION validate_count(devices TEXT[], campaign_id INTEGER) RETURNS void AS $$ DECLARE devices_array TEXT[] := devices; devices_count INTEGER := array_length(devices, 1); row_id INTEGER := campaign_id; device RECORD; BEGIN FOR device IN SELECT unnest(devices_array) LOOP IF my_count('my_table', device, row_id) != 1 THEN RAISE EXCEPTION 'invalid_count %', row_id; END IF; END LOOP; END $$ LANGUAGE plpgsql;

Thank you very much for detailed explanation! After fixes it says loop variable of loop over rows must be a record or row variable or list of scalar variables
Did you DECLARE device RECORD; up top (without typos)? This is the error message when device isn't known to the parser.
I have just done that actually, yes now it says ERROR: unrecognized RAISE statement option at or near "array_length" LINE 11: ...ays have either 0 or %s of device bid modifiers', array_leng...... (it's a hint I use with exceptions)
Not sure about that last error. I added the whole result (which runs successfully for me) to my answer.

Collectives™ on Stack Overflow

Invalid for loop in a function

2 Answers 2

2 Comments

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

4 Comments

Related