I am trying to do something like this. What is the right way to do it
CREATE OR REPLACE function func(age bigint) RETURNS TABLE(years int, category text) AS $$ SELECT CASE WHEN age > 30 THEN SELECT CASE WHEN (age >= 60) THEN 0 WHEN (age >= 50) THEN 10 WHEN (age >= 40) THEN 20 ELSE 30 END, CASE WHEN (age >= 60) THEN "BABY BOOMER" WHEN (age >= 40) THEN "BOOMER" WHEN (age >= 40) THEN "NOOBER" ELSE "LONER" END, WHEN age < 30 THEN SELECT CASE WHEN (age >= 20) THEN 20 WHEN (age >= 10) THEN 10 ELSE 0 END, CASE WHEN (age >= 20) THEN "MILLENIAL" WHEN (age >= 10) THEN "GEN X" ELSE "CERELAX" END END $$ language sql stable strict;
The function should return data in the form of a table but what I need is based on input the returning set of values to be chosen. Basically I need to add conditional return
First a couple concepts need to be cleared up. In Postgres:
- double quotes indicate identifiers. Therefore in your query the double quoted "values" are undefined identifiers. String/Text values are indicated by tick marks (single quotes).
- A case expression can only return a single result. Thus your form
case when x then (case ... end, case ... end) endis invalid as the then would be returning 2 values. While a case expression can return only a single result that result may be a tuple. IE
case when ... then (a,b) endis valid.
Further as is your returns null if age = 30. That is kept.
create or replace function func(age bigint) returns table(years int, category text) language sql stable strict as $$ select case when age >= 60 then ( 0,'BABY BOOMER'::text) when age >= 50 then (10,'BOOMER'::text) when age >= 40 then (20,'BOOMER'::text) when age > 30 then (30,'LONER'::text) -- << ??? when age = 30 then (null::int, null::text) -- ??? >>> when age >= 20 then (20,'MILLENIAL'::text) when age >= 10 then (10,'GEN X'::text) else (0, 'CERELAX'::text) end ; $$;
Answered By – Belayer
Answer Checked By – Timothy Miller (BugsFixing Admin)