拆分给定的字符串并准备大小写语句 [英] Split given string and prepare case statement

问题描述

表:表名

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的某些日期.

Now I want to update set_name for some dates.

例如:

我想这样更新表:

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套.

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.

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

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

给出两个参数:

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:

静态更新声明:

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;

上面的update语句静态地完成了工作.

The above update statement does the job done but statically.

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

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.

问题:

1. 如何拆分给定日期p_dates? (我在两个日期之间有to关键字.)
2. 如何拆分给定的集p_sets? (我在两个set_name之间使用逗号，".)
3. p_dates和p_sets拆分后如何准备动态案例语句?

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?

---

此问题与使用SQL的动态案例语句Server 2008 R2 ，但对于Microsoft SQL Server来说是相同的.

---

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

推荐答案

清洁设置:

CREATE TABLE tbl (
  given_date date
, set_name varchar
);

使用单数术语作为单值的列名.
数据类型显然是 date，而不是timestamp

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;

平行嵌套"很方便，但有一些警告. Postgres 9.4 添加了一个干净的解决方案，Postgres 10 最终消除了这种行为.见下文.

"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.

准备的语句仅对创建会话可见，并因此而消失. 每个文档:

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 每个会话一次 em>:

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次:

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

服务器端功能

功能将保留并在所有会话中可见.

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提琴

使用数组参数(仍可以作为字符串文字形式提供)， daterange 类型(均为9.3版)和

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

详细信息:

• 同时嵌套多个数组

这篇关于拆分给定的字符串并准备大小写语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！