sql - 拆分给定的字符串并准备 case 语句

Question

表:表名

create table table_name
(
given_dates timestamp,
set_name varchar
);

插入记录:

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');

现在我想为某些日期更新 set_name。

例如:

我想像这样更新表格:

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

注意:given_dates 和set_name 是传递参数，因为它们是动态的。我可能会通过2套如上图s1,s2也可以根据需要传4组。

所以我需要动态 case 语句来更新 set_name。

给定两个参数:

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

declare p_sets varchar := 's1,s2';

好吧，我可以使用以下静态脚本来做到这一点:

静态更新语句:

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;

上面的更新语句完成了静态的工作。

就像更新表的方式一样，我只想准备 case 语句，它应该是动态的，可以根据参数 (p_dates,p_sets) 的变化而变化。

问题:

1. 如何拆分给定的日期 p_dates？ (我在两个日期之间有 to 关键字。)
2. 如何拆分给定的 p_sets 集合？ (我在两个 set_name 之间有 ',' 逗号。)
3. 拆分p_dates和p_sets后如何准备动态case语句？

---

这个问题与Dynamic case statement using SQL Server 2008 R2有关，这对于 Microsoft SQL Server 是一样的。

Answer 1

清理设置:

CREATE TABLE tbl (
  given_date date
, set_name varchar
);

使用单数术语作为单个值的列名称。
数据类型显然是 date and not a timestamp .

将文本参数转换为有用的表格:

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”很方便，但也有其注意事项。 Postgres 9.4 添加了一个干净的解决方案，Postgres 10 最终消除了这种行为。见下文。

动态执行

准备好的声明

准备好的语句仅对创建 session 可见并随其消亡。 Per documentation:

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

PREPARE 每个 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;

或使用客户提供的工具准备报表。
以任意参数执行n次:

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

服务端函数

函数被持久化并且对所有 session 可见。

CREATE FUNCTION 一次:

CREATE OR REPLACE FUNCTION f_upd_tbl(_date_ranges text, _names text)
  RETURNS void 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$  LANGUAGE sql;

调用n次:

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

SQL Fiddle

卓越的设计

使用数组参数(仍然可以作为字符串文字提供)， daterange 类型(第 9.3 页)和 new parallel unnest() (第 9.4 页)。

CREATE OR REPLACE FUNCTION f_upd_tbl(_dr daterange[], _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;

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

调用:

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

详细信息:

• Unnest multiple arrays in parallel