Разбить заданную строку и подготовить оператор case

Таблица: имя_таблицы

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 комплекта в соответствии с требованием.

Так что мне нужен динамический регистр для обновления 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_names.)
  3. Как подготовить динамический регистр после разбиения p_dates а также p_sets?

Этот вопрос относится к динамическому описанию случая с использованием SQL Server 2008 R2, что то же самое, но для Microsoft SQL Server.

Источник

3 ответа

Решение

Чистая настройка:

CREATE TABLE tbl (
  given_date date
, set_name varchar
);

Используйте единственный термин в качестве имени столбца для одного значения.
Тип данных явно 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;

"Параллельное гнездо" удобно, но имеет свои недостатки. Postgres 9.4 добавляет чистое решение. Увидеть ниже.

Динамическое исполнение

Подготовленное заявление

Подготовленные операторы видны только сеансу создания и погибают вместе с ним. По документации:

Подготовленные операторы действуют только в течение текущего сеанса работы с базой данных.

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

Функция на стороне сервера

Функции сохраняются и видны для всех сессий.

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) и новая параллель 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;

<@ будучи оператором "элемент содержится".

Вызов:

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

Подробности:

Источник

теперь мы можем использовать 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;

запустить его.

      SELECT f_upd_tbl_multirange(
    '{[''2022-01-01'',''2022-01-05''],[''2022-02-06'',''2022-02-25'']}', '{s2,s5}');
Источник

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;
Источник
Другие вопросы по тегам