NEW and OLD variables postgres

1

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;
$$
    
asked by Elias Tutungi 06.10.2017 в 19:31
source

2 answers

1

I have managed to finish the log and it was the way I wanted it to work with all the tables that I need and simply make a call with a trigger, to which I am interested I leave the link here: link

    
answered by 07.10.2017 в 23:59
0

Let's see, I do not control anything of postgre, but I see that it is not so different either. Then I'm going to comment on some things that I see that I squeak a little, in case some could help you.

In VALUES, is that syntax correct? If you want to enter the variable, you should not use @campo? (I say @ because I think I remember that it was in mysql). I already tell you that I'm giving you ideas, I'm not an expert xD. That and specify the type there again when I see that you have done the DECLARE above. You also have a variable called Field and a field called field xD. Use table.field to also specify.

Another thing that I see rare, is why you compare old and new values of the variable in the IF. In any case, you should do it on COLUMN_NAME, right?

And the last one, is that where you put COLUMN_NAME do you mean the data, or are you trying to enter the name of the column?

I'm telling you, I'm not very into this, so you're going to tell me if I'm wrong or I help you in something and so I learn too:)

    
answered by 07.10.2017 в 01:32