CODEWITHSUNDEEP
postgresqlfunctionloops
Andrey Deineko
Andrey Deineko

Reputation: 52347

Invalid for loop in a function

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)

Upvotes: 0

Views: 614

Answers (2)

csd
csd

Reputation: 1784

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;

Upvotes: 1

user330315
user330315

Reputation:

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;

Upvotes: 1

Related Questions