CODEWITHSUNDEEP
sqlpostgresqlcase
enneenne
enneenne

Reputation: 219

case inside function

Here is my code:

CREATE OR REPLACE FUNCTION getNumberOfPersons(num int)
RETURNS numeric LANGUAGE sql AS
$$
CASE WHEN (SELECT * FROM getTotalPersons(num)) is not null THEN SELECT getPerson(num)+getTotalPersons(num)*getPerson(num) ELSE SELECT getPerson(num) END;
$$;

Before I used CASE with the same code (and only first branch), everything worked fine but now with CASE I get an error 'syntax error at or near CASE'. I do not see where my mistake is.

Upvotes: 0

Views: 45

Answers (1)

user330315
user330315

Reputation:

In a language sql functions you can't have a PL/pgSQL CASE statement only a CASE expression

But neither is needed here. A simple coalesce will do:

CREATE OR REPLACE FUNCTION getNumberOfPersons(num int)
RETURNS numeric LANGUAGE sql AS
$$
  select coalesce(getTotalPersons(num),1) * getPerson(num);
$$;

Btw: a scalar function shouldn't be used like a table function.
So, SELECT * FROM getTotalPersons(num) should be select getTotalPersons(num)

Upvotes: 1

Related Questions