sql - Dividir la cadena dada y preparar la declaración del caso

postgresql postgresql-9.3

Question

Tabla : nombre_tabla

create table table_name
(
given_dates timestamp,
set_name varchar
);

Inserción de registros :

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

Ahora quiero actualizar set_name para algunas fechas.

Por ejemplo :

Quiero actualizar la tabla de esta manera:

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

Nota : Las given_dates y set_name pasan un parámetro porque son dinámicas. Puedo pasar 2 juegos como se muestra arriba s1,s2 o puedo pasar 4 juegos según el requisito.

Entonces necesito la declaración de caso dinámica para actualizar el set_name .

Dados dos parámetros :

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

Bueno, puedo hacer esto usando el siguiente script estático:

Declaración de actualización estática :

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;

La declaración de actualización anterior hace el trabajo pero estáticamente.

Al igual que para actualizar la tabla, quiero preparar solo una declaración de caso que debería ser dinámica y que pueda cambiar según cambien los parámetros (p_dates,p_sets) .

Preguntas :

1. ¿Cómo dividir las fechas dadas que son p_dates ? (Tengo to escribir to palabra clave entre dos fechas).
2. ¿Cómo dividir los conjuntos dados que son p_sets ? (Tengo una coma '','' entre dos nombres_conjunto).
3. ¿Cómo preparar una declaración de caso dinámica después de dividir las p_dates y p_sets ?

Esta pregunta se relaciona con la declaración de caso dinámica usando SQL Server 2008 R2 , que es lo mismo pero para Microsoft SQL Server.

Answer 1

Configuración limpia:

CREATE TABLE tbl (
given_date date
, set_name varchar
);

Use un término singular como nombre de columna para un solo valor.
El tipo de datos es obviamente date y no una timestamp .

Para transformar sus parámetros de texto en una tabla útil:

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" es útil pero tiene sus advertencias. Postgres 9.4 agrega una solución limpia, Postgres 10 eventualmente desinfecta el comportamiento de esto. Vea abajo.

Ejecución dinámica

Declaración preparada

Las declaraciones preparadas solo son visibles para la sesión de creación y mueren con ella. Por documentación:

Las declaraciones preparadas solo duran la duración de la sesión actual de la base de datos.

PREPARE una vez por sesión :

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;

O utilice las herramientas proporcionadas por su cliente para preparar la declaración.
Ejecute n veces con parámetros arbitrarios:

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

Función del lado del servidor

Las funciones son persistentes y visibles para todas las sesiones.

CREATE FUNCTION una vez :

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;

Llame n veces:

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

Violín de SQL

Diseño superior

Utilice los parámetros de la matriz (aún se pueden proporcionar como literales de cadena), un tipo de daterange (ambos, página 9.3) y el nuevo paralelo unnest() (página 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;

<@ siendo el operador "elemento contenido por".

Llamada:

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

Detalles:

• Unnest múltiples arreglos en paralelo

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;