Split given string and prepare case statement

Question

Table: table_name

create table table_name
(
given_dates timestamp,
set_name varchar
);

Insertion of records:

insert into table_name values('2001-01-01'),('2001-01-05'),('2001-01-10'),
                 ('2001-01-15'),('2001-01-20'),('2001-01-25'),
                 ('2001-02-01'),('2001-02-05'),('2001-02-10'),
                 ('2001-02-15');

Now I want to update set_name for some dates.

For example:

I want to update table like this:

given_dates    set_name 
----------------------
2001-01-01      s1
2001-01-05      s1
2001-01-10      s2
2001-01-15      s2
2001-01-20
2001-01-25
2001-02-01
2001-02-05
2001-02-10
2001-02-15

Note: The given_dates and set_name are pass a parameter because of they are dynamic. I may pass 2 sets as shown above s1,s2 or may pass 4 sets according to the requirement.

So I need the dynamic case statement for update the set_name.

Given two parameters:

declare p_dates varchar := '2001-01-01to2001-01-05,2001-01-10to2001-01-15';

declare p_sets varchar := 's1,s2';

Well I can do this by using following static script:

Static Update statement:

update table_name
SET set_name = 
CASE  
when given_dates between '2001-01-01' and '2001-01-05' then 's1'
when given_dates between '2001-01-10' and '2001-01-15' then 's2'
else '' 
end;

The above update statement does the job done but statically.

Like the same way to update table I want to prepare only case statement which should be dynamic which can change as per the parameters (p_dates,p_sets) changes.

Questions:

1. How to split the given dates that is p_dates? (I have to keyword in between two dates.)
2. How to split the given sets that is p_sets? (I have ',' comma in between two set_names.)
3. How to prepare dynamic case statement after splitting the p_dates and p_sets?

---

This question relates to Dynamic case statement using SQL Server 2008 R2, which is the same thing but for Microsoft SQL Server.

Injured

check out the string_to_array() function: postgresql.org/docs/current/static/functions-array.html – Heelandtoe

You might also want to check out regexp_split_to_table. But I am not sure if going directly to a table and bypassing the array is better in the end. – Ligetti

Answer 1

Clean setup:

CREATE TABLE tbl (
  given_date date
, set_name varchar
);

Use a singular term as column name for a single value.
The data type is obviously date and not a timestamp.

To transform your text parameters into a useful table:

SELECT unnest(string_to_array('2001-01-01to2001-01-05,2001-01-10to2001-01-15', ',')) AS date_range
     , unnest(string_to_array('s1,s2', ',')) AS set_name;

"Parallel unnest" is handy but has its caveats. Postgres 9.4 adds a clean solution, Postgres 10 eventually sanitized the behavior of this. See below.

Dynamic execution

Prepared statement

Prepared statements are only visible to the creating session and die with it. Per documentation:

Prepared statements only last for the duration of the current database session.

PREPARE once per session:

PREPARE upd_tbl AS
UPDATE tbl t
SET    set_name = s.set_name
FROM  (
   SELECT unnest(string_to_array($1, ',')) AS date_range
        , unnest(string_to_array($2, ',')) AS set_name
   ) s
WHERE t.given_date BETWEEN split_part(date_range, 'to', 1)::date
                       AND split_part(date_range, 'to', 2)::date;

Or use tools provided by your client to prepare the statement.
Execute n times with arbitrary parameters:

EXECUTE upd_tbl('2001-01-01to2001-01-05,2001-01-10to2001-01-15', 's1,s4');

Server-side function

Functions are persisted and visible to all sessions.

CREATE FUNCTION once:

CREATE OR REPLACE FUNCTION f_upd_tbl(_date_ranges text, _names text)
  RETURNS void
  LANGUAGE sql AS
$func$
UPDATE tbl t
SET    set_name = s.set_name
FROM  (
   SELECT unnest(string_to_array($1, ',')) AS date_range
        , unnest(string_to_array($2, ',')) AS set_name
   ) s
WHERE  t.given_date BETWEEN split_part(date_range, 'to', 1)::date
                        AND split_part(date_range, 'to', 2)::date
$func$;

Call n times:

SELECT f_upd_tbl('2001-01-01to2001-01-05,2001-01-20to2001-01-25', 's2,s5');

_{Old sqlfiddle}

Superior design

Use array parameters (can still be provided as string literals), a daterange type (both pg 9.3) and the new parallel unnest() (pg 9.4).

CREATE OR REPLACE FUNCTION f_upd_tbl(_dr daterange[], _n text[])
  RETURNS void
  LANGUAGE sql AS
$func$
UPDATE tbl t
SET    set_name = s.set_name
FROM   unnest($1, $2) s(date_range, set_name)
WHERE  t.given_date <@ s.date_range
$func$;

<@ being the "element is contained by" operator.

Call:

SELECT f_upd_tbl('{"[2001-01-01,2001-01-05]"
                  ,"[2001-01-20,2001-01-25]"}', '{s2,s5}');

Details:

• Unnest multiple arrays in parallel

Kosciusko

Great! Thank you so much. In the Superior design part in function f_upd_tbl() getting an error ERROR: function unnest(daterange[], text[]) does not exist LINE 7: FROM unnest($1, $2) s(date_range, set_name). I am using PostgreSQL 9.3 version. – Injured

But anyway I got clear with the concept. Thank you so much. I wish if there is 1000 up vote button I can click that. – Injured

@MAK: unnest() with multiple parameters is new in Postgres 9.4, as noted. – Kosciusko

Can you please help me for this : #28562744 – Injured

The omission of _dr and _n in the func of "Superior Design" threw me at first, but I realize those are being passed to unnest as $1 and $2. And then table s is created on the fly with two named columns (date_range and set_name) and dynamically typed based on the unnest , correct? How long / where does table s exist? – Oleary

@Oleary Function parameters can be referenced by name or ordinal position. Related: https://mcmap.net/q/28869/-pl-pgsql-column-name-the-same-as-variable, https://mcmap.net/q/28870/-return-multiple-ids-from-a-function-and-use-the-result-in-a-query The derived table s only exists within the scope of the same query. – Kosciusko

Answer 2

String_to_array

declare p_dates varchar[] := string_to_array('2001-01-01,2001-01-05,
2001-01-10,2001-01-15*2001-01-01,2001-01-05,2001-01-10,2001-01-15','*');
declare p_sets varchar[]  := string_to_array('s1,s2',',');
declare p_length integer=0;
declare p_str  varchar[];
declare i integer;
select array_length(p_dates ,1) into p_count;

for i in 1..p_count loop

  p_str  := string_to_array( p_dates[i],',')   

  execute 'update table_name
  SET set_name = 
  CASE  
  when given_dates between'''|| p_str  [1] ||''' and '''|| p_str  [2] 
  ||''' then ''' || p_sets[1] ||'''
  when given_dates between '''|| p_str  [3] ||''' and '''
  || p_str  [4] ||''' then ''' || p_sets[2] ||'''
  else '''' 
  end';
end loop;

Epilate

What if there is a 5 sets? You need to write again when and condition 5 time according to your script. – Injured

There is an error: invalid type name "array_length(p_dates ,1) into p_count". And even I don't want to use loop because of performance issues. There might be 100's of sets to update due to this the looping is not a good idea. – Injured

Answer 3

now we can use datemultirange.

create or replace function f_upd_tbl_multirange(_dr datemultirange , _n text[])
    returns void as
$func$
UPDATE tbl t
SET    set_name = s.set_name
FROM   unnest($1,$2) s(date_range,set_name)
WHERE  t.given_date <@ s.date_range
$func$ language sql;

run it.

SELECT f_upd_tbl_multirange(
    '{[''2022-01-01'',''2022-01-05''],[''2022-02-06'',''2022-02-25'']}', '{s2,s5}');

Stumble