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',
'TraderAnalytics@gmail.com,
'@kjvfhjh88976',
ARRAY['86578657865','Natali','Vladimirov','Natali.Vladimirov1990@gmail.com', '+@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?
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?)