desarrollando mi propio sistema de auditoria para las BD internas

[cacr]

Saludos

Estoy tratando de hacer una especie de mini fórmula para que se guarden los registros de mi BD, no está completado, por lo que falta algunas cosas (como el usuario, la IP de dónde se conecta)

Tabla para guardar unos nombre cualesquiera:

Código:

CREATE TABLE nombres (
    nombres_id serial NOT NULL,
    nombre1 character varying(20),
    nombre2 character varying(20)
);

Tabla para guardar los cambios (léase auditoria):

Código:

CREATE TABLE control (
    tabla character varying NOT NULL,
    campo character varying NOT NULL,
    valor_anterior character varying,
    valor_nuevo character varying,
    opcion character varying NOT NULL,
    ocurrencia time without time zone NOT NULL
);

Función para obtener el nombre del primer campo de una tabla (el nombre puede variar, pero se sabe que es el primer campo):

Código:

CREATE FUNCTION captura(character varying) RETURNS character varying
    AS $_$SELECT column_name FROM information_schema.columns WHERE table_name = $1 AND ordinal_position = 1;$_$
    LANGUAGE sql;

Función disparadora que almacena en la tabla control:

Código:

CREATE FUNCTION controlador() RETURNS "trigger"
    AS $$DECLARE
  nombre_campo character varying;

BEGIN
        
  nombre_campo := captura('TG_RELNAME');
  IF (TG_OP = 'DELETE') THEN
	
    INSERT INTO control (tabla, campo, valor_nuevo, opcion, ocurrencia) VALUES (TG_RELNAME, 'nombre_campo', NEW.nombre_campo, TG_OP, now());
    RETURN OLD;

  ELSIF ((TG_OP = 'UPDATE')  OR (TG_OP = 'INSERT')) THEN

    INSERT INTO control (tabla, campo, valor_nuevo, opcion, ocurrencia) VALUES (TG_RELNAME, 'nombre_campo', NEW.nombres_id, TG_OP, now());
    RETURN NEW;

    END IF;

END;

	$$
    LANGUAGE plpgsql;

Ahora bien, sí en la cónsola ejecuto:

Código:

pruebas=# SELECT captura('nombres');
  captura
------------
 nombres_id
(1 fila)

se observa que funciona. Pero sí, ejecuto un insert sobre la tabla de prueba nombres:

Código:

insert into nombres (nombre1, nombre2) values ('cccc','dddd');

cuando hago una consulta sobre la tabla control, obtengo:

Código:

pruebas=# SELECT * FROM control ;
  tabla  |    campo     | valor_anterior | valor_nuevo | opcion |   ocurrencia
---------+--------------+----------------+-------------+--------+-----------------
 nombres | nombre_campo |                | 27          | INSERT | 17:25:07.398235
(3 filas)

donde se observa que el nombre del primer campo de la tabla, no se está guardando correctamente.

alguna luz? de antemano, gracias

[huesos52]

Quitale las comillas cacr.

Fijate que dentro del trigger, nombre_campo es el nombre de la variable de tipo character varying, por lo que lleva las comillas implicitas. fijate que está insertando en control, la palabra 'nombre_campo' y no el contenido de la variable con el resultado de la función captura('TG_RELNAME').

Quedaría así:

INSERT INTO control (tabla, campo, valor_nuevo, opcion, ocurrencia) VALUES (TG_RELNAME, nombre_campo, NEW.nombres_id, TG_OP, now());

Es ese el error?
Entendí bien?

[cacr]

Qué tal @huesos52

Ya había intentado con las comillas, asumiendo lo mismo, pero obtengo:
Error de SQL:

ERROR: el valor null para la columna «campo» viola la restricción not null
CONTEXT: sentencia SQL: «INSERT INTO control (tabla, campo, valor_nuevo, opcion, ocurrencia) VALUES ( $1 , $2 , $3 , $4 , now())»
PL/pgSQL function "controlador" line 14 at SQL statement

En la declaración:
INSERT INTO nombres (nombre1, nombre2) VALUES ('carmen','maria')

con lo cual, entreleo, que la función que debería devolver el nombre del campo, no lo está haciendo...

[huesos52]

un select captura('TG_RELNAME'); te retorna el campo?

Tu dices que esto te funciona.

pruebas=# SELECT captura('nombres');
captura
------------
nombres_id
(1 fila)

Que resultado tiene este?
select captura('TG_RELNAME');?
Te devuelve un null?

[cacr]

sí ejecuto sólo el select captura('TG_RELNAME'); obviamente me devuelve nulo, pero al ser llamado por el triggert, devuelve el nombre de la tabla.

Es posible, que esté utilizando mal esta combinación de funciones SQL y Plpgsql...

[huesos52]

Se me ocurre, asignes primero a una variable character varying la variable tg_relname.
Posteriormente, llamar en la función nombre_campo := captura(variable_asignada);

No tenía ni idea que era tg_relname, pero según leo, es el nombre de la tabla que invoca el trigger. Si es así, no creo que deba ir entre comillas, por que lo que está haciendo, es enviando la palabra tg_relname a la consulta en information_schema y está comparando ejecutando dicha consulta con la palabra como tal. Por eso te devuelve un null.
Prueba ejecutando así:
nombre_campo := captura(TG_RELNAME);

[cacr]

http://www.postgresql.org/docs/curre...l-trigger.html

lo de nombre_campo := captura(TG_RELNAME); ya lo probé, y devuelve error, porque captura requiere una cadena, lo de pasarla antes a una variable, ya lo probaré...

Me voy a ausentar un par de horas, mi bebe de 2 añitos tiene una presentación de finde año escolar... la felicidad (de padre) de sentirse orgulloso

[huesos52]

Te felicito por tu pequeño hijo.

No se si funcione, pero tqmbién puedes probar así:
nombre_campo := captura(TG_RELNAME::character varying);

[cacr]

así mismo! gracias huesos52

Sigo con la otra parte de la funcioncita...

[huesos52]

te funcionó?

[cacr]

Me funcionó nombre_campo := captura(TG_RELNAME::character varying);
Sólo que me conseguí con otro "pequeño" detalle, pero ahora justo, estoy revisando cómo darle la vuelta...