[SOLVED] How PostgreSQL function will return a table format with either select case1 or case2 based on input?


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 $$
            WHEN age > 30 THEN SELECT
                    WHEN (age >= 60) THEN 0
                    WHEN (age >= 50) THEN 10
                    WHEN (age >= 40) THEN 20
                    ELSE 30
                    WHEN (age >= 60) THEN "BABY BOOMER"
                    WHEN (age >= 40) THEN "BOOMER"
                    WHEN (age >= 40) THEN "NOOBER"
                    ELSE "LONER"
            WHEN age < 30 THEN SELECT
                    WHEN (age >= 20) THEN 20
                    WHEN (age >= 10) THEN 10 
                    ELSE 0
                    WHEN (age >= 20) THEN "MILLENIAL"
                    WHEN (age >= 10) THEN "GEN X"
                    ELSE "CERELAX"
    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:

  1. 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).
  2. 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

Answered By – Belayer

Answer Checked By – Timothy Miller (BugsFixing Admin)

Leave a Reply

Your email address will not be published.