tricl
asked on
Using the OLD record in a trigger to pull back dynamic column names
I am trying to create a trigger in postgresql using the OLD record
basically the column I want to refer to will be in a string for example:
myColumn = "some_column_name";
OLD.myColumn
however doing it like this would mean that it would be looking for a column called myColumn
Does anyone know if it is possible to pull back an OLD record with a dynamic record as the column name?
basically the column I want to refer to will be in a string for example:
myColumn = "some_column_name";
OLD.myColumn
however doing it like this would mean that it would be looking for a column called myColumn
Does anyone know if it is possible to pull back an OLD record with a dynamic record as the column name?
ASKER
for example if there is a table with column names:
col1, col2, col3, col4
I could then refer to the fields with some kind of loop
for (i=0;i<4;i++) {
OLD.col || i
}
if that makes any more sense to refer to the OLD record dynamically (I know that the loop is not a valid one for plpgsql but it just an example of a loop to show an example of the problem
col1, col2, col3, col4
I could then refer to the fields with some kind of loop
for (i=0;i<4;i++) {
OLD.col || i
}
if that makes any more sense to refer to the OLD record dynamically (I know that the loop is not a valid one for plpgsql but it just an example of a loop to show an example of the problem
Let's step back and figure out what you are actually trying to accomplish with your trigger(s) rather than looking at a detailed technique you may not need. There may be any number of ways to accomplish the overall goal.
What, in general, do the triggers do? How many do you have to create?
Regards,
Bill
What, in general, do the triggers do? How many do you have to create?
Regards,
Bill
ASKER
Ok,
the idea is to log every update made to any single field
so there will be a table called table_log with the fields:
id, ref_id, table_name, fieldname, oldfieldvalue, newfieldvalue
so I will create a function which will essentially be the same code for each table that is used on an update trigger to store the fieldname, table name the row id and the new and old value for that field
essentially that is what I want to be able to do, this way I am then able to store and refer back to any historical record.
I will plan to do the same kind of method for delete operation too so nothing will be deleted or changed without having a audit log of it
Hope this makes more sense for what I am needing it for. I have the function ready so all of the logic / code is there just need to refer to a dynamic OLD record
the idea is to log every update made to any single field
so there will be a table called table_log with the fields:
id, ref_id, table_name, fieldname, oldfieldvalue, newfieldvalue
so I will create a function which will essentially be the same code for each table that is used on an update trigger to store the fieldname, table name the row id and the new and old value for that field
essentially that is what I want to be able to do, this way I am then able to store and refer back to any historical record.
I will plan to do the same kind of method for delete operation too so nothing will be deleted or changed without having a audit log of it
Hope this makes more sense for what I am needing it for. I have the function ready so all of the logic / code is there just need to refer to a dynamic OLD record
O.K. Take a look at the entire discussion on the following EE Question. This person had a similar requirement in Sybase ASA.
https://www.experts-exchange.com/questions/22860493/ASA-Audit-Trigger-Get-Column-Name.html
The point made in Q_22860493 is that it is not such a good idea to try and log every field change anyway. I presented a workable solution to keeping history/audit information that will work in PostgreSQL as well.
Check it out and let us know what you think.
Regards,
Bill
https://www.experts-exchange.com/questions/22860493/ASA-Audit-Trigger-Get-Column-Name.html
The point made in Q_22860493 is that it is not such a good idea to try and log every field change anyway. I presented a workable solution to keeping history/audit information that will work in PostgreSQL as well.
Check it out and let us know what you think.
Regards,
Bill
ASKER
Hi,
I have read through that question/answers however I dont think it totally relates to my issue. The reason for wanting to be able to refer to the OLD and NEW records dynamically is that I can then use the same function for all tables. Rather than having to declare each column for each table to be inserted.
Also by it being just one function, if I then want to advance or change it I only have to change it in one place (modifications to the function)
For example in PHP this would be done with
$stringName = "mystring";
$stringValue = "mystringvalue";
$evalstring="$".$stringNam e."=".$str ingValue." ;";
eval($evalstring);
I understand what you are saying with regards to performance of storing every field that is changed on a table, however for this particular project / database use the majority of queries will be SELECT or INSERT UPDATES will not happen as a main task, but need to be logged to protect ourselves
Is there any similar way to achieve what I need as there is in PHP ? the language I am using for PostgreSQL is plpgsql but can use another language if required as well if it offers the solution to what I need
I have given more points to this issue as it appears to be more complex than I originally thought it would be
Cheers
I have read through that question/answers however I dont think it totally relates to my issue. The reason for wanting to be able to refer to the OLD and NEW records dynamically is that I can then use the same function for all tables. Rather than having to declare each column for each table to be inserted.
Also by it being just one function, if I then want to advance or change it I only have to change it in one place (modifications to the function)
For example in PHP this would be done with
$stringName = "mystring";
$stringValue = "mystringvalue";
$evalstring="$".$stringNam
eval($evalstring);
I understand what you are saying with regards to performance of storing every field that is changed on a table, however for this particular project / database use the majority of queries will be SELECT or INSERT UPDATES will not happen as a main task, but need to be logged to protect ourselves
Is there any similar way to achieve what I need as there is in PHP ? the language I am using for PostgreSQL is plpgsql but can use another language if required as well if it offers the solution to what I need
I have given more points to this issue as it appears to be more complex than I originally thought it would be
Cheers
Why not write a little code generator that creates the Update/Delete triggers automatically based on the meta-data for each target table? That way you are not stuck writing a custom trigger for each procedure yet you still get reasonable performance and query capabilities of storing an entire record per delta.
Bill
Bill
ASKER
I didnt really want to do that because then changing any element means changing every function so one for each table
I have come up with a possible solution (code attached) this actually works, yes it is a bit of a "bodge" to a solution but it does store the information I need (although it actually stores more as it will store all of the table column names and values not just the ones that have been adjusted)
The issue is that it actually doesnt update the table I am editing for example
if I edit a table called table1 (id, name)
it will not actually update the value I changed in table1 but it will log the changes I made in the below function into _sys_record_log
failing any other solution I will have to go with the one I have mentioned, however does anyone know any reason why it will not let me update the actual table itself (for example table1) ?
I have come up with a possible solution (code attached) this actually works, yes it is a bit of a "bodge" to a solution but it does store the information I need (although it actually stores more as it will store all of the table column names and values not just the ones that have been adjusted)
The issue is that it actually doesnt update the table I am editing for example
if I edit a table called table1 (id, name)
it will not actually update the value I changed in table1 but it will log the changes I made in the below function into _sys_record_log
failing any other solution I will have to go with the one I have mentioned, however does anyone know any reason why it will not let me update the actual table itself (for example table1) ?
DECLARE
colrec RECORD;
colrecsub RECORD;
colname VARCHAR;
colnamet VARCHAR;
coll VARCHAR;
begin
FOR colrec IN
select attname from pg_attribute where attrelid = (select oid from pg_class where relname = TG_RELNAME) and attnum > 0
LOOP
FOR colrecsub IN
EXECUTE 'SELECT text(' || colrec.attname || ') as coltest FROM ' || TG_RELNAME || ' WHERE id=' || OLD.id || ';'
LOOP
coll = colrecsub.coltest;
if coll is not null THEN
INSERT INTO _sys_record_log (ref_table,ref_id,event,field_name,field_value) VALUES (TG_RELNAME,OLD.id,TG_OP,colrec.attname,coll);
END IF;
END LOOP;
END LOOP;
return null;
end;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I have now come up with a work around, it is not ideal but it will store all of the columns for an updated row, I can see your solution worth implementing in the future when my work around has started to fill the table up with too many row that are not needed which in turns slows down any future requirements of it. I have not used plpythonu before but will look into how to do it, I am sure it will be easy enough to pick up
what do you mean by dynamic ??
old.x at the end of the day refers to a table record.