update trigger registros para funciones example disparadores auditoria actualizar postgresql types plpgsql dynamic-sql triggers

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



trigger postgresql update example (1)

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.


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:

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

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.