Prepare dynamic case statement using PostgreSQL 9.3

Question

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.

pozs · Accepted Answer · 2015-01-16T13:16:08.003

1

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.

edited Jan 16 '15 at 13:16

answered Jan 14 '15 at 17:33

pozs

34,608
5
57
63

But how can I split those dates and assign name to them? – MAK Jan 16 '15 at 06:06
@MAK what exactly want you to do with them? Do you want to pass all these rows in parameter(s) in JDBC f.ex.? Or are these data will be available in the database already? – pozs Jan 16 '15 at 08:58
Yeah! I want to pass all those rows as a parameter(s). And want to prepare a case statement like above shown. – MAK Jan 16 '15 at 12:15
I apologize for late reply! I have posted new question with more details. I hope this time you can understand it better. http://stackoverflow.com/questions/28018991/split-given-string-and-prepare-case-statement – MAK Jan 19 '15 at 06:27
After running your edited script I get the error: ERROR: `syntax error at or near "::"` – MAK Jan 19 '15 at 12:22
`LINE: from unnest(?::text[][]) d` – MAK Jan 19 '15 at 12:30
@MAK usually `?` is used as a parameter placeholder (f.ex. in JDBC), but this also depends on your actual client (it may be `$1` in case of libpq, f.ex.) -- please provide more details about your platform/environment. – pozs Jan 19 '15 at 14:04
I am really not getting how to overcome with this. From front end I am just getting two strings which I had posted in the question. And I am really don't know the JDBC and libpg concept. The same question has been answer by srutzky but using Microsoft SQL Server 2008 R2: http://stackoverflow.com/questions/27428544/dynamic-case-statement-using-sql-server-2008-r2 – MAK Jan 20 '15 at 05:48

Prepare dynamic case statement using PostgreSQL 9.3

1 Answers1