Postgres select concatenated column name

Question

For the sake of example, there's five columns in a table month:

• month.week1
• month.week2
• month.week3
• month.week4
• month.week5

The number of col is determined by a function

EXTRACT(WEEK FROM NOW()) - EXTRACT(WEEK FROM DATE_TRUNC('month', NOW())) + 1

How can I select the column colX? This is what I have so far

SELECT month.week || (
    EXTRACT(WEEK FROM NOW()) 
    - EXTRACT(WEEK FROM DATE_TRUNC('month', NOW())) + 1
)::text 
FROM month

But that gives me the error

ERROR: column month.week doesn't exist SQL state: 42703

Answer 1

Use case statement

SELECT
    CASE WHEN (week expresion) = 1 THEN month.week1
         WHEN (week expresion) = 2 THEN month.week2
         WHEN (week expresion) = 3 THEN month.week3
         WHEN (week expresion) = 4 THEN month.week4
         ELSE month.week5
    END as WeekValue
FROM month

OR

SELECT
    CASE (week expresion) 
         WHEN 1 THEN month.week1
         WHEN 2 THEN month.week2
         WHEN 3 THEN month.week3
         WHEN 4 THEN month.week4
         ELSE month.week5
    END as WeekValue
FROM month

Answer 2

dynamic sql sample:

t=# create table so58(i int,w1 text);
CREATE TABLE
t=# create or replace function so59(_n int) returns table (i int,w text) as $$begin
return query execute format('select i,w%s from so58',_n);
end;
$$ language plpgsql;
CREATE FUNCTION
t=# select * from so59(1);
 i | w
---+---
(0 rows)