I have the following problem, I need to make a bitacora and that for each modified data create a record in the bd.
Each table has a trigger that will call the bitacora function () and inside it I must capture the OLD and NEW and cycle so that for each modified data a record is added in old_value and new_value respectively.
I have a While I already do the cycle and I get all the fields from the table, the problem is that at each step of the cycle I save it in the variable Field. I do not know how to do so that when I register in the blog, I take NEW.Campo and I get the value that Campo has and not literally 'Campo'.
In other languages there are functions to take the value of the variable and not the name as such, is there something similar in SQL? But what solution do you propose? Thanks in advance
CREATE FUNCTION bitacora() RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE Usuario INT;
Tabla VARCHAR(100):= TG_TABLE_NAME;
Operacion VARCHAR(50) := TG_OP;
Campo VARCHAR(100);
Old_value VARCHAR(100);
New_value VARCHAR(100);
Fecha TIMESTAMP WITHOUT TIME ZONE := NOW();
Field INT := 0;
MaxField INT := MAX(ORDINAL_POSITION)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = Tabla;
BEGIN
Usuario := get_sesion( pg_backend_pid() );
WHILE Field < MaxField LOOP
SELECT MIN(ORDINAL_POSITION) INTO Field
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = Tabla AND ORDINAL_POSITION > Field;
SELECT COLUMN_NAME INTO Campo
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = Tabla AND ORDINAL_POSITION = Field;
IF OLD.Campo <> NEW.Campo THEN // Aqui el problema.
// necesito que campo se sustituya por el valor de la varible.
// no que busque Campo en NEW o OLD porque no existe ese campo.
INSERT INTO
bitacora (
idusuario,
tabla,
campo,
operacion,
old_value,
new_value,
fecha
) VALUES (
Usuario,
Tabla,
Campo,
Operacion,
cast(OLD.Campo AS VARCHAR(100)),
cast(NEW.Campo AS VARCHAR(100)),
Fecha
);
END IF;
END LOOP;
RETURN NEW;
END;
$$