RE: Consulta Migrar a 9.1 SOLUCIONADO -> FUNCION SOLUCIONADO

Hola Alvaro:

Veo cuál es tu punto, lo solucione cambiando el tipo da dato de entrada de
la función:

CREATE OR REPLACE FUNCTION audit.audit_table (
target_table text,
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 ' ||
target_table::text;
EXECUTE 'DROP TRIGGER IF EXISTS audit_trigger_stm ON ' ||
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 ' ||
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 ' ||
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 text, 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.
';

Muchas Gracias

Mario Soto Cordones

-----Mensaje original-----
De: Alvaro Herrera [mailto:alvherre(at)2ndquadrant(dot)com]
Enviado el: lunes, 15 de abril de 2013 11:09
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 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);

Hm, esto está mal. Si target_table es un regclass, entonces podría estar
calificado con el nombre del esquema, y por lo tanto tratar de pasarlo a
través de quote_ident sería un error. Observa:

alvherre=# create schema "Foo bar";
CREATE SCHEMA
alvherre=# create table "Foo bar"."bar Baz" (); CREATE TABLE

alvherre=# select '"Foo bar"."bar Baz"'::regclass;
regclass
---------------------
"Foo bar"."bar Baz"
(1 fila)

alvherre=# select quote_ident('"Foo bar"."bar Baz"'::regclass::text);
quote_ident
---------------------------
"""Foo bar"".""bar Baz"""
(1 fila)

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

-
Enviado a la lista de correo pgsql-es-ayuda (pgsql-es-ayuda(at)postgresql(dot)org)
Para cambiar tu suscripción:
http://www.postgresql.org/mailpref/pgsql-es-ayuda