干净的设置:
CREATE TABLE tbl (
given_date date
, set_name varchar
);
使用单数术语作为列名称single value.
数据类型很明显date而不是一个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;
“并行解除嵌套”很方便,但也有其注意事项。 Postgres9.4添加了一个干净的解决方案,Postgres10最终净化了这种行为。见下文。
动态执行
准备好的声明
准备好的语句仅对创建会话可见,并随之消失。根据文档:
准备好的语句仅在当前数据库会话期间持续。
PREPARE 每次会话一次:
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');
服务器端功能
函数被持久化并且可见all会议。
CREATE FUNCTION once:
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小提琴
卓越的设计
使用数组参数(仍然可以作为字符串文字提供),daterange类型(均第 9.3 页)和新平行unnest() (pg 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;
<@是“元素包含于”运算符。
Call:
SELECT f_upd_tbl('{"[2001-01-01,2001-01-05]"
,"[2001-01-20,2001-01-25]"}', '{s2,s5}');
Details: