sql - Dividir la cadena dada y preparar la declaración del caso
postgresql postgresql-9.3 (2)
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'');
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:
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 :
-
¿Cómo dividir las fechas dadas que son
p_dates
? (Tengoto
escribirto
palabra clave entre dos fechas). -
¿Cómo dividir los conjuntos dados que son
p_sets
? (Tengo una coma '','' entre dos nombres_conjunto). -
¿Cómo preparar una declaración de caso dinámica después de dividir las
p_dates
yp_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.
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;