CODEWITHSUNDEEP
sqlpostgresqlsyntax-errorcaseplpgsql
beoliver
beoliver

Reputation: 5759

Return value from case statement in plpgsql

How can I set a value (or just return a value directly) from a user defined function that uses a case statement?

create function is_bar(email varchar) returns boolean as
$$
declare returnValue boolean;
begin
  select case when exists
  (select * from (users join user_roles on users.userID = user_roles.userID)
  where user_email=email and user_role='bar')
    then (returnValue := TRUE);
    else (returnValue := FALSE);
  end;
  return returnValue;
end;
$$ language plpgsql;

gives me:

ERROR:  syntax error at or near ":="
LINE 8: then (returnValue := TRUE);

Upvotes: 3

Views: 5454

Answers (2)

Pavel Stehule
Pavel Stehule

Reputation: 45940

The reason of described issue is change of SQL (functional) CASE statement and PLpgSQL (procedural) CASE statement.

The SQL CASE (functional):

BEGIN
   RETURN CASE WHEN EXISTS(..)
               THEN true /* value */
               ELSE false END; /* ended by END */
END;

The PLpgSQL (procedural) CASE:

BEGIN
  CASE WHEN EXISTS(..)
    THEN
       RETURN true; /* statement */ 
    ELSE
       RETURN false;
  END CASE; /* ended by END CASE */
END;

There are some other examples (same result):

a := CASE WHEN b < 10 THEN true ELSE false END;
a := b < 10;
CASE WHEN b < 10 THEN
  a := true;
ELSE
  a := false;
END CASE;

Upvotes: 5

Mureinik
Mureinik

Reputation: 312289

It would be much easier to return the result of the exists operator itself:

CREATE FUNCTION is_bar(email VARCHAR) RETURNS BOOLEAN AS
$$
BEGIN
  RETURN EXISTS (SELECT *
                 FROM   users
                 JOIN   user_roles ON users.userID = user_roles.userID
                 WHERE  user_email = email AND user_role='bar')
END;
$$ LANGUAGE plpgsql;

Upvotes: 4

Related Questions