How PostgreSQL function will return a table format with either select case1 or case2 based on input?-CodePudding

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

CodePudding user response：

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) end is 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) end is 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 ;
 $$;

See demo