CODEWITHSUNDEEP
arrayspostgresqlplpgsql
Maks.Burkov
Maks.Burkov

Reputation: 626

Syntax error for array parameter in a function?

When I ran this function script I got a syntax error near the parameter.

Currently I am using Data Grip and don't have any syntax errors in my IDE.

Can you explain why I got the syntax error in my case?

ERROR: syntax error at or near "employees"

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;

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);

    FOR e IN employees LOOP

        CASE WHEN e ~ r_id THEN em_counter := +1; END CASE;
        CASE WHEN e ~ r_name THEN em_counter := +1; END CASE;
        CASE WHEN e ~ r_name THEN em_counter := +1; END CASE;
        CASE WHEN e ~ r_email THEN em_counter := +1; END CASE;
        CASE WHEN e ~ r_password THEN em_counter := +1; END CASE;

    END LOOP ;

    FOR cr IN creators LOOP

        FOR i IN cr LOOP

            CASE WHEN  i ~ r_name THEN cr_counter :=+ 1; END CASE;

        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;

Upvotes: 1

Views: 100

Answers (1)

klin
klin

Reputation: 121834

The error concerns the line:

FOR e IN employees LOOP

There are two issues with it. As employees is an array you should use 

FOREACH e IN ARRAY employees LOOP

You should also declare the loop variable e. The same concerns the second loop with creators.

Upvotes: 1

Related Questions