Hola Alvaro:
Esta es la función:
CREATE OR REPLACE FUNCTION audit.audit_table (
target_table pg_catalog.regclass,
audit_rows boolean,
audit_query_text boolean,
ignored_cols text []
)
RETURNS void AS
$body$
DECLARE
stm_targets text = 'INSERT OR UPDATE OR DELETE OR TRUNCATE';
_q_txt text;
_ignored_cols_snip text = '';
BEGIN
EXECUTE 'DROP TRIGGER IF EXISTS audit_trigger_row ON ' ||
quote_ident(target_table::text);
EXECUTE 'DROP TRIGGER IF EXISTS audit_trigger_stm ON ' ||
quote_ident(target_table::text);
IF audit_rows THEN
IF array_length(ignored_cols,1) > 0 THEN
_ignored_cols_snip = ', ' || quote_literal(ignored_cols);
END IF;
_q_txt = 'CREATE TRIGGER audit_trigger_row AFTER INSERT OR UPDATE OR
DELETE ON ' ||
quote_ident(target_table::text) ||
' FOR EACH ROW EXECUTE PROCEDURE audit.if_modified_func('
||
quote_literal(audit_query_text) || _ignored_cols_snip ||
');';
RAISE NOTICE '%',_q_txt;
EXECUTE _q_txt;
stm_targets = 'TRUNCATE';
ELSE
END IF;
_q_txt = 'CREATE TRIGGER audit_trigger_stm AFTER ' || stm_targets || '
ON ' ||
quote_ident(target_table::text) ||
' FOR EACH STATEMENT EXECUTE PROCEDURE
audit.if_modified_func('||
quote_literal(audit_query_text) || ');';
RAISE NOTICE '%',_q_txt;
EXECUTE _q_txt;
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;
COMMENT ON FUNCTION audit.audit_table(target_table pg_catalog.regclass,
audit_rows boolean, audit_query_text boolean, ignored_cols text [])
IS '
Add auditing support to a table.
Arguments:
target_table: Table name, schema qualified if not on search_path
audit_rows: Record each row change, or only audit at a statement level
audit_query_text: Record the text of the client query that triggered the
audit event?
ignored_cols: Columns to exclude from update diffs, ignore updates that
change only ignored cols.
';
Mario Soto Cordones
e-mail:mario.alberto.soto.cordo...@gmail.com
Por favor antes de Imprimir éste correo, piense en los árboles de
nuestro planeta.
" Estamos tan acostumbrados a pedir felicidad, exito y fortuna, que nos
olvidamos de pedir lo mas importante: humildad, fortaleza, y sinceridad.
(Autor Desconocido)
-----Mensaje original-----
De: Alvaro Herrera [mailto:alvhe...@2ndquadrant.com]
Enviado el: lunes, 15 de abril de 2013 10:35
Para: Mario Soto Cordones
CC: 'Jaime Casanova'; 'Martín Marqués'; 'Ayuda'
Asunto: Re: [pgsql-es-ayuda] Consulta Migrar a 9.1 SOLUCIONADO
Mario Soto Cordones escribió:
> Hola Jaime:
>
> Hice lo que me indicas:
>
> SELECT audit.audit_table('compras."eMarketing_dispor"'::regclass,
> 'true', 'true', '{version_col, changed_by, changed_timestamp}');
>
>
> Pero me indica el siguiente error:
>
>
> ERROR: relation ""eMarketing_dispor"" does not exist
> CONTEXT: SQL statement "DROP TRIGGER IF EXISTS audit_trigger_row ON
> """eMarketing_dispor""""
> PL/pgSQL function "audit_table" line 7 at EXECUTE statement
Eso podría pasar si el código de audit_table estuviera tratando de invocar
quote_identifier() dos veces ...
--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
-
Enviado a la lista de correo pgsql-es-ayuda (pgsql-es-ayuda@postgresql.org)
Para cambiar tu suscripción:
http://www.postgresql.org/mailpref/pgsql-es-ayuda
