CODEWITHSUNDEEP
postgresqlpostgresql-9.3
MAK
MAK

Reputation: 7260

Prepare dynamic case statement using PostgreSQL 9.3

I have the following case statement to prepare as a dynamic as shown below:

Example:

I have the case statement:

case cola 
when cola between '2001-01-01' and '2001-01-05' then 'G1'
when cola between '2001-01-10' and '2001-01-15' then 'G2'
when cola between '2001-01-20' and '2001-01-25' then 'G3'
when cola between '2001-02-01' and '2001-02-05' then 'G4'
when cola between '2001-02-10' and '2001-02-15' then 'G5'
else '' 
end

Note: Now I want to create dynamic case statement because of the values dates and name passing as a parameter and it may change.

Declare 
dates varchar = '2001-01-01to2001-01-05,2001-01-10to2001-01-15,
                           2001-01-20to2001-01-25,2001-02-01to2001-02-05,
                           2001-02-10to2001-02-15';

names varchar = 'G1,G2,G3,G4,G5';

The values in the variables may change as per the requirements, it will be dynamic. So the case statement should be dynamic without using loop.

Upvotes: 0

Views: 1535

Answers (1)

pozs
pozs

Reputation: 36214

You may not need any function for this, just join to a mapping data-set:

with cola_map(low, high, value) as (
  values(date '2001-01-01', date '2001-01-05', 'G1'),
        ('2001-01-10', '2001-01-15', 'G2'),
        ('2001-01-20', '2001-01-25', 'G3'),
        ('2001-02-01', '2001-02-05', 'G4'),
        ('2001-02-10', '2001-02-15', 'G5')
        -- you can include as many rows, as you want
)
select    table_name.*,
          coalesce(cola_map.value, '') -- else branch from case expression
from      table_name
left join cola_map on table_name.cola between cola_map.low and cola_map.high

If your date ranges could collide, you can use DISTINCT ON or GROUP BY to avoid row duplication.

Note: you can use a simple sub-select too, I used a CTE, because it's more readable.

Edit: passing these data (as a single parameter) can be achieved by passing a multi-dimensional array (or an array of row-values, but that requires you to have a distinct, predefined composite type).

Passing arrays as parameters can depend on the actual client (& driver) you use, but in general, you can use the array's input representation:

-- sql
with cola_map(low, high, value) as (
  select  d[1]::date, d[2]::date, d[3]
  from    unnest(?::text[][]) d
)
select    table_name.*,
          coalesce(cola_map.value, '') -- else branch from case expression
from      table_name
left join cola_map on table_name.cola between cola_map.low and cola_map.high

// client pseudo code
query = db.prepare(sql);
query.bind(1, "{{2001-01-10,2001-01-15,G2},{2001-01-20,2001-01-25,G3}}");
query.execute();

Passing each chunk of data separately is also possible with some clients (or with some abstractions), but this is highly depends on your driver/orm/etc. you use.

Upvotes: 1

Related Questions