Type error while looping through array in PL/pgSQL

Question

I am a beginner with PostgreSQL. Trying to loop here the values that I pass:

companyregistration just calling the companyRegistrationValidator

The function call:

SELECT companyregistration(
'876786576544', 
'TraderAnalytics',
'[email protected],
'@kjvfhjh88976',
ARRAY['86578657865','Natali','Vladimirov','[email protected]', '+@jvfhjh88976'],
ARRAY [['Maks','Burkov'],['Yan','Burkov']],
'Netherlands',
'Company');

[2018-10-28 18:29:15] [42804] ERROR: FOREACH expression must yield an array, not type text
[2018-10-28 18:29:15] Where: PL/pgSQL function companyregistrationvalidator(character varying,character varying,character varying,character varying,text[],text[],character varying) line 28 at FOREACH over array

The function definition:

CREATE OR REPLACE FUNCTION companyRegistrationValidator (company_id VARCHAR, comp_name VARCHAR, comp_email VARCHAR, comp_password VARCHAR, employees text[], creators text[], country VARCHAR)
  RETURNS BOOLEAN AS $$
DECLARE
    checked BOOLEAN := FALSE ;
    r_id VARCHAR; r_name VARCHAR; r_email VARCHAR; r_password VARCHAR; r_country VARCHAR;
    cr_counter INTEGER = 0; em_counter INTEGER = 0; c_ar_length INTEGER  = 0; e_ar_length INTEGER = 0;
    ar_index text; creator text; val text;

BEGIN
    SELECT id_r , email_r , password_r , country_r , firstname_r INTO r_id , r_email , r_password, r_country , r_name FROM regex;

    c_ar_length := cardinality(creators);
    e_ar_length := cardinality(employees);

    FOREACH val IN ARRAY employees LOOP
        IF val ~ r_id THEN em_counter := +1;
        ELSEIF val ~ r_name THEN  em_counter := +1;
        ELSEIF val ~ r_email THEN em_counter := +1;
        ELSEIF val ~ r_password THEN em_counter := +1;
        END IF;
    END LOOP;

    FOREACH creator IN ARRAY creators LOOP
        FOREACH ar_index IN ARRAY creator LOOP
            IF ar_index ~ r_name THEN cr_counter := +1;
            END IF;
        END LOOP;
    END LOOP;

    IF cr_counter = c_ar_length AND em_counter = e_ar_length AND company_id ~ r_id AND comp_name ~ r_name AND comp_email ~ r_email AND comp_password ~ r_password AND country ~ r_country
        THEN checked := TRUE;
    END IF;

    RETURN checked;
 END;
$$ LANGUAGE plpgsql;

What's the error in my code?

Oh I see. I had a similar issue not too long ago. It's when you're iterating through the array of arrays in a nested FOREACH. That won't work. The first FOREACH already has unnested everything in a single "level", so it yields text, not arrays, so you can't do another FOREACH on its results. Can't remember off the top of my head how I got around it. — 404
– 404, Commented Oct 28, 2018 at 17:04
If you can give me answer with code example it would be great — Maks.Burkov
– Maks.Burkov, Commented Oct 28, 2018 at 17:30

Erwin Brandstetter · Accepted Answer · 2024-09-04 21:23:59Z

2

This nested loop construct is good for the error you quote:

    FOREACH creator IN ARRAY creators LOOP
        FOREACH ar_index IN ARRAY creator LOOP
            IF ar_index ~ r_name THEN cr_counter := +1;
            END IF;
        END LOOP;
    END LOOP;

The loop variable creator in the outer loop is defined as type text, which is fine. But you cannot nest another loop through creator like you try - nor do you need to.
The manual on "Looping Through Arrays":

The elements are visited in storage order, regardless of the number of array dimensions.

So, to loop through all base elements, regardless of array dimensions, all you need is:

    FOREACH creator IN ARRAY creators LOOP
       IF creator ~ r_name THEN cr_counter := cr_counter + 1;
       END IF;
    END LOOP;

Also note that cr_counter := +1 would not increment cr_counter like you seem to be trying. Would just assign 1 repeatedly. I replaced it with an actual increment.

Unless the array is trivially tiny, a set-based approach is typically (much) faster:

    SELECT INTO cr_counter
           count(*) FILTER (WHERE _creator ~ r_name)::int
    FROM   unnest(creators) _creator;

(And are you sure you want the regexp operator ~ there?)

edited Sep 4, 2024 at 21:23

answered Oct 29, 2018 at 3:36

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

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

1 Comment

Maks.Burkov Over a year ago

Thanks for the full answer i appreciate it i will test your examples ..

Super Kai - Kazuya Ito · Accepted Answer · 2024-02-03 07:31:15Z

I got the same error below:

ERROR: FOREACH expression must yield an array, not type character varying

When I tried to iterate an array with a FOREACH statement as shown below:

DO $$
DECLARE
  temp VARCHAR;
  arr VARCHAR := ARRAY['a','b','c','d'];
BEGIN
  FOREACH temp SLICE 0 IN ARRAY arr LOOP
    RAISE INFO '%', temp;
  END LOOP;
END
$$;

So, I set [] just after VARCHAR as shown below:

DO $$
DECLARE
  temp VARCHAR;
  arr VARCHAR[] := ARRAY['a','b','c','d'];
BEGIN     -- ↑↑ Here
  FOREACH temp SLICE 0 IN ARRAY arr LOOP
    RAISE INFO '%', temp;
  END LOOP;
END
$$;

Then, I could iterate the array with the FOREACH statement as shown below:

INFO:  a
INFO:  b
INFO:  c
INFO:  d
DO

In addition, I set SLICE 0 with temp of array type (VARCHAR[]) as shown below:

DO $$
DECLARE -- ↓ Here
  temp VARCHAR[];
  arr VARCHAR[] := ARRAY['a','b','c','d'];
BEGIN       -- ↓ ↓ ↓ ↓
  FOREACH temp SLICE 0 IN ARRAY arr LOOP
    RAISE INFO '%', temp;
  END LOOP;
END
$$;

Then, I got the error below:

ERROR: FOREACH loop variable must not be of an array type

And, I set SLICE 1 with temp of non-array type (VARCHAR) as shown below:

DO $$
DECLARE -- ↓ Here
  temp VARCHAR;
  arr VARCHAR[] := ARRAY['a','b','c','d'];
BEGIN       -- ↓ ↓ ↓ ↓
  FOREACH temp SLICE 1 IN ARRAY arr LOOP
    RAISE INFO '%', temp;
  END LOOP;
END
$$;

Then, I got the error below:

ERROR: FOREACH ... SLICE loop variable must be of an array type

Collectives™ on Stack Overflow

Type error while looping through array in PL/pgSQL

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related