postgresql - registros - PL/pgSQL: forma general de actualizar columnas N en Trigger?

trigger postgresql update example

Question

Estoy intentando crear una función que tomará una tabla general y convertirá N columnas en mayúsculas. No he tenido suerte para encontrar una solución a este tipo de problema, pero pude encontrar lo siguiente:

create or replace function uc_on_insert()
returns trigger as
$$
declare
p_tbl varchar = TG_TABLE_NAME;
p_sch varchar = TG_TABLE_SCHEMA;
i varchar;
begin
for i in
(select column_name
from INFORMATION_SCHEMA.COLUMNS
where 1=1
and table_name ilike p_tbl
and table_schema ilike p_sch
and data_type =''character varying'')
loop
execute ''new.'' || i || '' = upper(new.'' || i || '');'';
return new;
end loop;
end;
$$ language plpgsql;

Actualmente recibo este error:

ERROR: syntax error at or near "new"
LINE 1: new.c1 = upper(new.c1);
^
QUERY: new.c1 = upper(new.c1);

La entrada esperada sería, en cualquier tabla, tengo este disparador:

insert into table_one(''a'', ''b'');
>> A, B

y si pongo este disparador en otra mesa:

insert into table_two (''a'', 3);
>> A, 3

etc.

Answer 1

Esta es una pregunta muy difícil.

Su intento está destinado a fallar, porque la NEW variable de fila actual NO es visible dentro de EXECUTE . E incluso si lo fuera, NEW es un tipo de fila (un registro), no una matriz. A diferencia de los elementos de matriz, los campos de una fila no pueden referenciarse por índice numérico. Esto causaría todo tipo de problemas en SQL, porque (a diferencia de una matriz) cada campo puede tener un tipo de datos diferente, y SQL espera saber el tipo de datos para tratar de antemano. Muy complicado de hecho.

Método genérico dirigido solo a los tipos seleccionados

Afortunadamente, hemos tratado con un problema similar antes:

• Cómo establecer el valor del campo variable compuesto usando SQL dinámico

Encontrarás una amplia explicación allí.
Adaptado para una función de activación y dependiendo del tipo de datos de las columnas, podría verse así:

Función de disparo

CREATE OR REPLACE FUNCTION trg_uc_on_insert()
RETURNS trigger AS
$func$
BEGIN
EXECUTE ''SELECT '' || array_to_string(ARRAY(
SELECT CASE WHEN atttypid = ''varchar''::regtype
-- atttypid = ANY(''{text, bpchar, varchar}''::regtype[])
THEN ''upper(($1).'' || quote_ident(attname)
|| '')::'' || atttypid::regtype::text
ELSE ''($1).'' || quote_ident(attname)
END AS fld
FROM pg_catalog.pg_attribute
WHERE attrelid = pg_typeof(NEW)::text::regclass
AND attnum > 0
AND attisdropped = FALSE
ORDER BY attnum
), '','')
USING NEW
INTO NEW;
RETURN NEW;
END
$func$ LANGUAGE plpgsql;

Si desea aplicar la misma regla a otros tipos de caracteres básicos también, use la alternativa comentada.

Desencadenar

CREATE TRIGGER trg_t_insbef
BEFORE INSERT
ON t -- works for any table
FOR EACH ROW
EXECUTE PROCEDURE trg_uc_on_insert();

SQL Fiddle.

Método simple e inespecífico

Siempre que use tipos simples en sus tablas solamente y desee utilizar todos los caracteres en mayúsculas, existe otro método crudo, rápido y simple: enviar toda la fila al text , la representación de texto en mayúsculas, enviar de nuevo al tipo de fila y actualizar NEW con el resultado. Detalles sobre el tipo de fila de una tabla

• Verifique una tabla completa por un solo valor

Función de disparo

CREATE OR REPLACE FUNCTION trg_uc_simple_on_insert()
RETURNS trigger AS
$func$
BEGIN
EXECUTE ''SELECT ($1::'' || pg_typeof(NEW) || '').*''
USING upper(NEW::text)
INTO NEW;
RETURN NEW;
END
$func$ LANGUAGE plpgsql;

SQL Fiddle.

Tenemos que descomponer el tipo de fila, porque SELECT INTO asigna campos individuales de un tipo de fila de destino uno a uno. No podemos asignar toda la fila a la vez. Si observa detenidamente, la solución "genérica" ​​hace lo mismo, es menos obvio.

Mientras que los datos de caracteres distinguen entre mayúsculas y minúsculas en la representación de texto, otros tipos de datos básicos numéricos o de fecha / hora no lo son. Entonces el método simple funciona de manera confiable. Probablemente con la mayoría de los otros tipos, también. Pero no probé con otros, y ciertamente hay una excepción. Deberá verificar los tipos de datos que usa.

Además, aunque el código es mucho más corto y más simple que con el método genérico, esto no es necesariamente más rápido, especialmente con muchas columnas no afectadas. Sin embargo, es probablemente mucho más rápido en casos simples.