Why syntax error using case and array functions in postgreSQL ?

Question

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

Try writing it as when validation_type = 'login' ...when validation_type = 'register' ...etc I'm not sure why but I've had this error before and that has worked as a work around. — Lio Elbammalf
– Lio Elbammalf, Commented Oct 18, 2018 at 9:55
INTO $1 makes no sense at the place where you put it. What exactly do you expect that to do? You are also missing a return statement. You have to explain to us what that function is supposed to do. As far as I can tell, the CASE is completely unnecessary as you simply output the (same) array length in all cases — user330315
– user330315, Commented Oct 18, 2018 at 10:04

Oto Shavadze · Accepted Answer · 2018-10-18 10:07:37Z

2

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;

answered Oct 18, 2018 at 10:07

Oto Shavadze

43.3k56 gold badges168 silver badges248 bronze badges

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

3 Comments

Maks.Burkov Over a year ago

Thanks !! Yes but i didn't understood mine error .. Can you explain me the logic behind array_length(string_to_match , 4); For example this function need to calculate length but i need to pass as parameter the array dimension and if not it will be error .. So what the logic behind length calculation if i have to pass the length in the beginning ?

Oto Shavadze Over a year ago

@Maks.Burkov - You tried assigning variable value using INTO, but you missed SELECT, also you tried to insert array length (which is int type) into array type; you have missed semi colons before RAISE..; also missed return from function

Oto Shavadze Over a year ago

@Maks.Burkov - I'm not sure I understand your question about array_length. 4 here is not length you're passing in the beginning, it is dimension. so if you need calculate one dimensional array, second parameter always will be 1

ines bargach · Accepted Answer · 2018-10-18 09:52:38Z

0

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

answered Oct 18, 2018 at 9:52

ines bargach

113 bronze badges

1 Comment

user330315 Over a year ago

No, that's fine. This is not MySQL

Usagi Miyamoto · Accepted Answer · 2018-10-18 10:04:24Z

0

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?

answered Oct 18, 2018 at 10:04

Usagi Miyamoto

6,3471 gold badge21 silver badges37 bronze badges

2 Comments

user330315 Over a year ago

It is an a statement in PL/pgSQL: postgresql.org/docs/current/static/…

Oto Shavadze Over a year ago

@UsagiMiyamoto - Why RAISE cannot be used inside ?

Collectives™ on Stack Overflow

Why syntax error using case and array functions in postgreSQL ?

3 Answers 3

3 Comments

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related