Postgres : using computed variable in a SELECT statement (part 2/2)-CodePudding

I'm having a SELECT statement as follow (doesn't work):

SELECT foo,
    extract(day from CAST (date as TIMESTAMP) - CAST (birth_date as TIMESTAMP)) / 365.25 as age_norm,
    CASE 
       WHEN age_norm >= 0 AND age_norm <1 THEN '00'
       WHEN age_norm >= 1 AND age_norm <5 THEN '01-4'
       --etc
    END as age_group
FROM bar

Is there a way to "inject" here the "variable" age_normin the query ?

NOTE

Asked a similar question here, but without the parameter foopresent in this question

EDIT

Tried:

SELECT foo,
    (
    SELECT t.age_norm,
    CASE 
       WHEN t.age_norm >= 0 AND t.age_norm <1 THEN '00'
       WHEN t.age_norm >= 1 AND t.age_norm <5 THEN '01-4'
       --etc
    END 
    FROM (SELECT extract(day from CAST (date as TIMESTAMP) - CAST (birth_date as TIMESTAMP)) / 365.25 as age_norm FROM bar) t
    )
   as age_group
FROM bar

But getting:

ERROR:  subquery must return only one column
LINE 10:     (  SELECT t.age_norm,
             ^
SQL state: 42601
Character: 212

CONCLUSION

Tried some of proposed solutions but either the resulting query is too slow (compared to hard coding the extract function in each where clause) or the resulting query become too complicate (it is in fact more complicate than expressed in this post with group by clauses and others things).

So will implements each when clause as follow:

WHEN (extract(day from CAST (date as TIMESTAMP) - CAST (birth_date as TIMESTAMP)) / 365.25) >=0 AND (extract(day from CAST (date as TIMESTAMP) - CAST (birth_date as TIMESTAMP)) / 365.25) <1 THEN '00' 
etc.

Thank you for your replies!

CodePudding user response：

Use a derived table (a query in the FROM clause):

SELECT 
    foo,
    age_norm,
    CASE 
       WHEN age_norm >= 0 AND age_norm <1 THEN '00'
       WHEN age_norm >= 1 AND age_norm <5 THEN '01-4'
    END as age_group
FROM (
    SELECT 
        foo,
        extract(day from CAST (date as TIMESTAMP) - CAST (birth_date as TIMESTAMP)) / 365.25 as age_norm
    FROM bar
    ) s

CodePudding user response：

perhaps a lateral join like this....

SELECT foo,age_group.*
FROM 
FROM bar
, LATERAL (
   SELECT t.age_norm,
   CASE 
      WHEN t.age_norm >= 0 AND t.age_norm <1 THEN '00'
      WHEN t.age_norm >= 1 AND t.age_norm <5 THEN '01-4'
      --etc
   END 
    FROM (SELECT extract(day from CAST (bar.date as TIMESTAMP) - CAST bar.birth_date as TIMESTAMP)) / 365.25 as age_norm ) t
) as age_group;