CODEWITHSUNDEEP
postgresql
Maks.Burkov
Maks.Burkov

Reputation: 626

Why syntax error using case and array functions in postgreSQL ?

I am new to postgreSQL database , can you explain me why i got syntax error ? I can't find any answer in documentation docs 

  CREATE OR REPLACE FUNCTION validation(string_to_match varchar [], pattern 
    varchar , validation_type varchar) RETURNS boolean AS $$  
    DECLARE a_length ALIAS FOR $1;
    DECLARE result_validation ALIAS FOR $2;
    BEGIN

    CASE validation_type
      WHEN 'login' THEN array_length (string_to_match , 1) INTO $1 RAISE NOTICE 
    'Array length is %', $1; 
      WHEN 'register' THEN array_length(string_to_match,1) INTO $1 RAISE NOTICE 
    'Array length is %', $1;
      WHEN 'contact' THEN array_length(string_to_match,1) INTO $1 RAISE NOTICE 
    'Array length is %', $1;
    END CASE; 

    END;    
    $$ lANGUAGE plpgsql;

ERROR: syntax error at or near "array_length" LINE 7: WHEN 'login' THEN array_length (string_to_match , 1) INTO... ^ SQL state: 42601 Character: 258

Upvotes: 0

Views: 885

Answers (3)

Oto Shavadze
Oto Shavadze

Reputation: 42753

Probably something like this you want(?) :

CREATE OR REPLACE FUNCTION validation(string_to_match varchar [], pattern 
varchar , validation_type varchar) 
RETURNS boolean 
AS $$  
DECLARE a_length INT;
BEGIN

    IF validation_type =  'login' THEN
        a_length := array_length(string_to_match , 1); 
        RAISE NOTICE 'Array length is %', a_length; 
    ELSIF validation_type = 'register' THEN 
        a_length := array_length(string_to_match , 1); 
        RAISE NOTICE 'Array length is %', a_length; 
    ELSIF validation_type =  'contact' THEN 
        a_length := array_length(string_to_match , 1); 
        RAISE NOTICE 'Array length is %', a_length; 
    END IF; 

    RETURN true; -- or false ? 
END;    
$$ lANGUAGE plpgsql;

Upvotes: 2

Usagi Miyamoto
Usagi Miyamoto

Reputation: 6289

  • CASE WHEN THEN END is not a statement, but an expression.
  • Therefore it does not need those semicolons inside...
  • And also RAISE cannot be used inside.
  • What does expression INTO variable means?

Upvotes: 0

ines bargach
ines bargach

Reputation: 11

it's just a first intuiton but I think it's because of the space between array_length and (string_to_match, 1)

Upvotes: 0

Related Questions