Thread: "OLD." || myColumnNameVar (How to generically access columns in a trigger's OLD or NEW records)

In PL/pgSQL, how does one generically access the fields of the OLD or NEW record?

I've tried code such as this:
  'NEW.' || quote_ident( myColumnNameVar ) || '::varchar'

But when run by an "EXECUTE" command, I get errors such as:
  ERROR:  missing FROM-clause entry for table "old"
  SQL state: 42P01

It seems that I cannot get PL/pgSQL to interpret the text of "NEW." + column name as text.

My goal is to loop each field in a trigger, comparing the "OLD." & "NEW." values of each field. If different I want to
logboth values in a history/audit-trail table. 

Is there some way to loop the fields of a trigger's Record? I've read other people's frustration at not being able to
getan array of fields from the Record. 

My approach is to fake it: Get the table's columns and data types by querying the meta-data tables (pg_attribute,
pg_class,pg_type). But I cannot get "NEW." || colNameVar to be interpreted. Perhaps there is a better approach. 

If anyone is curious, my source code is pasted below.

--Basil Bourque

------------
CREATE OR REPLACE FUNCTION "table_make_history_"() RETURNS "trigger" AS
$BODY$

DECLARE
    metadata_record RECORD;
    /* http://www.postgresql.org/docs/current/static/plpgsql-declarations.html#PLPGSQL-DECLARATION-RECORDS */
    table_dot_column VARCHAR;
    my_sql VARCHAR;
    column_is_loggable_var BOOLEAN;
    edited_var BOOLEAN;

BEGIN

IF (TG_OP = 'INSERT') THEN
    -- Ignore this case

ELSIF (TG_OP = 'UPDATE') THEN

    /* Get a list of column name, data type, and position with in table.
       attname = Name of column.
       atttypid = Data type of column (as an oid from pg_type.oid)
    */
    FOR metadata_record IN
     SELECT attname::varchar AS "nth_col_name", pg_type.typname::varchar as "nth_col_type", pg_attribute.attnum
     FROM pg_attribute, pg_class, pg_type
     WHERE attrelid = pg_class.oid
     AND pg_attribute.attisdropped = False
     AND relname = TG_TABLE_NAME
     AND attnum > 0
     AND atttypid = pg_type.oid
    LOOP -- For each table in the table calling this trigger.
        -- Now "metadata_record" has one record from resultset of SELECT query above.
        --table_dot_column := TG_TABLE_NAME::VARCHAR || '.' || metadata_record.nth_col_name ;
        column_is_loggable_var := position( '_x_' in metadata_record.nth_col_name ) < 1 ;
        IF column_is_loggable_var THEN
        -- The name of column in question does NOT contain "_x_". So, proceed to possibly log modified data.
        -- See if the NEW-OLD values are different.
            edited_var := true; -- Next line fails.
            --EXECUTE 'edited_var := ( OLD.' || quote_ident(metadata_record.nth_col_name) || ' = NEW.' ||
quote_ident(metadata_record.nth_col_name)|| ' );' ; 
            PERFORM 'edited_var := ( OLD.' || quote_ident(metadata_record.nth_col_name) || ' <> NEW.' ||
quote_ident(metadata_record.nth_col_name)|| ' );' ; 
            IF edited_var THEN
                EXECUTE 'INSERT INTO history_ ( operation_, table_oid_, table_name_, uuid_, column_name_, old_value_,
new_value_) ' 
                    || 'VALUES ( ' || quote_literal(TG_OP) || ', ' || TG_RELID || ', ' || quote_literal(TG_TABLE_NAME)
||', ' || quote_literal(OLD.pkey_) || '::uuid, '  
                    || quote_literal(metadata_record.nth_col_name)
                    || ', OLD.'
                    || quote_ident(metadata_record.nth_col_name) || '::varchar'
                    || ', NEW.'
                    || quote_ident(metadata_record.nth_col_name) || '::varchar'
                    || ' ); ' ;
            END IF;

        END IF;
    END LOOP;

    RETURN NEW;

ELSIF (TG_OP = 'DELETE') THEN
    -- ignore this case

END IF;

RETURN NULL; /* Should never reach this point. Perhaps we should raise an error here. */

END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
--------------

On Thu, Apr 28, 2011 at 12:46:50PM -0700, Basil Bourque wrote:
> In PL/pgSQL, how does one generically access the fields of the OLD or NEW record?
>
> I've tried code such as this:
>   'NEW.' || quote_ident( myColumnNameVar ) || '::varchar'
>
> But when run by an "EXECUTE" command, I get errors such as:
>   ERROR:  missing FROM-clause entry for table "old"
>   SQL state: 42P01
>
> It seems that I cannot get PL/pgSQL to interpret the text of "NEW." + column name as text.
>
> My goal is to loop each field in a trigger, comparing the "OLD." & "NEW." values of each field. If different I want
tolog both values in a history/audit-trail table. 
>
> Is there some way to loop the fields of a trigger's Record? I've read other people's frustration at not being able to
getan array of fields from the Record. 
>
> My approach is to fake it: Get the table's columns and data types by querying the meta-data tables (pg_attribute,
pg_class,pg_type). But I cannot get "NEW." || colNameVar to be interpreted. Perhaps there is a better approach. 

long story short - it's not possible.

a bit longer story:
there are couple of workarounds.

1. you can use hstore datatype and it's casts from record to hstore
2. you can use another pl/* language - like pl/perl - which doesn't have
this problem
3. you can use ready made tool for auditing that does what you want, so
you don't have to worry ( http://pgfoundry.org/projects/tablelog/ )
4. http://www.depesz.com/index.php/2010/03/10/dynamic-updates-of-fields-in-new-in-plpgsql/
but really, read, and understand the warnings.

depesz

--
The best thing about modern society is how easy it is to avoid contact with it.
                                                             http://depesz.com/

On Apr 28, 2011, at 3:46 PM, Basil Bourque wrote:

> It seems that I cannot get PL/pgSQL to interpret the text of "NEW." + column name as text.
>
> My goal is to loop each field in a trigger, comparing the "OLD." & "NEW." values of each field. If different I want
tolog both values in a history/audit-trail table. 
>
> Is there some way to loop the fields of a trigger's Record? I've read other people's frustration at not being able to
getan array of fields from the Record. 

This might help you:

http://wiki.postgresql.org/wiki/PL/pgSQL_Dynamic_Triggers




John DeSoi, Ph.D.

On 04/28/2011 10:46 PM, Basil Bourque wrote:

> In PL/pgSQL, how does one generically access the fields of the OLD or NEW record?
>
> I've tried code such as this:
>    'NEW.' || quote_ident( myColumnNameVar ) || '::varchar'
>
> But when run by an "EXECUTE" command, I get errors such as:
>    ERROR:  missing FROM-clause entry for table "old"
>    SQL state: 42P01
>
> It seems that I cannot get PL/pgSQL to interpret the text of "NEW." + column name as text.
>
> My goal is to loop each field in a trigger, comparing the "OLD."&  "NEW." values of each field. If different I want
tolog both values in a history/audit-trail table. 
>
> Is there some way to loop the fields of a trigger's Record? I've read other people's frustration at not being able to
getan array of fields from the Record. 
>
> My approach is to fake it: Get the table's columns and data types by querying the meta-data tables (pg_attribute,
pg_class,pg_type). But I cannot get "NEW." || colNameVar to be interpreted. Perhaps there is a better approach. 
>
> If anyone is curious, my source code is pasted below.
>
> --Basil Bourque
We use plpythonu for this as the new and old structures are dictionaries.

Sim

Hoorah! I was able to complete my single PL/pgSQL function to create history records tracking individual field value
changesgenerically for all my tables. Some developers call this an "audit trail", though an accountant might say
otherwise.

Thanks for all the replies.

Special thanks to John DeSoi for pointing me to a crucial code example to make PL/pgSQL interpret:
    "OLD." || myColumnNameVar
as:
    OLD.first_name    (for example)

The crucial line is:
    EXECUTE 'SELECT ($1).' || ri.column_name || '::text' INTO t USING NEW;
found here:
    http://wiki.postgresql.org/wiki/PL/pgSQL_Dynamic_Triggers
I'm new to SQL and Postgres, so I may be wrong but my interpretation of this is:
Since there appears no way to make the PL/pgSQL interpreter interpret our desired string, the trick is to look outside
ofPL/pgSQL and instead use the SQL interpreter. Brilliant! It works, and it seems to be fast enough, at least for my
needs. 

I'll share my current code & table structure below. Caveat: This code has not yet been thoroughly tested, nor has it
beendeployed. I only finalized it today.  

--Basil Bourque

[A] I'm working on a greenfield project, where:
• I've built every table to have a primary key of type uuid named exactly "pkey_".
• Every table has a TIMESTAMPTZ field named "record_modified_".
My approach below hard-codes these assumptions.

[B] I have this "history_" table:

CREATE TABLE history_
(
  pkey_ uuid NOT NULL DEFAULT uuid_generate_v1mc(), -- The primary key for this table, though no primary key constraint
wascreated (for the sake of performance and conservation). This column and timestamp_ column are the only two columns
aboutthis table itself. All other columns are about the inserted/modified/deleted record in some other table. 
  table_name_ character varying(120) NOT NULL, -- Name of table whose row is being affected (inserted, deleted, or
modified).
  column_name_ character varying(120) NOT NULL, -- Name of the column in some other table whose row value is being
modified.This column's value is empty string if the operation was DELETE. 
  timestamp_ timestamp with time zone NOT NULL DEFAULT clock_timestamp(), -- The moment this record was created. Using
theclock_timestamp() function as a default, to capture the actual moment in time rather than moment when transaction
began.
  db_user_name_ character varying(120) NOT NULL DEFAULT "current_user"(), -- The name of the Postgres user logged in to
thisdatabase connection/session. 
  app_name_ character varying(120) NOT NULL DEFAULT current_setting('application_name'::text), -- The name of the
applicationconnected to the database. May also include the version number of app, and the name of the human user
authenticatedwithin the app. 
  old_value_ character varying(120) NOT NULL DEFAULT ''::character varying,
  new_value_ character varying(120) NOT NULL DEFAULT ''::character varying,
  uuid_ uuid NOT NULL, -- The UUID of the row being affected, the row being inserted, updated, or deleted. Assumes
everytable whose history is being recorded uses the 'uuid' data type as its primary key. 
  operation_ character varying(120) NOT NULL, -- What database operation resulted in this trigger running: INSERT,
UPDATE,DELETE, or TRUNCATE. 
  table_oid_ oid NOT NULL, -- The oid of the table whose record is being modified. May be helpful if a table name
changesover time. 
  ordinal_position_of_column_ integer NOT NULL, -- The position of the affected column in its table. Every new column
getsa number, incremented by one for each. This may be helpful when analyzing changes across a stretch of time during
whicha column's name was changed. Apparently columns have no oid, so we are recording this number instead. 
  transaction_began_ timestamp with time zone NOT NULL DEFAULT transaction_timestamp() -- The time when the current
transactionbegan. Can act like a transaction identifier, to group multiple "history_" rows of the same transaction
together.This is not foolproof, as multiple transaction could possibly start in the same split second moment. Assuming
thecomputer's clock has a fine resolution, this chance of a coincidence should be quite miniscule. 
)

I do not have a primary constraint for this table. The "pkey_" column acts as a primary key, but there is no need for
anindex or uniqueness testing for this special table. 

[C] For every table I want to track field-level value changes, I create a trigger like this:
    CREATE TRIGGER XXX_trigger_history_
    AFTER INSERT OR UPDATE OR DELETE ON XXX_
    FOR EACH ROW EXECUTE PROCEDURE make_history_();
where 'XXX' is the name of the table.

[D] I created this function:

CREATE OR REPLACE FUNCTION make_history_()
RETURNS TRIGGER
LANGUAGE plpgsql
AS
$BODY$

/*     Purpose: Make a history of changes to most fields in the table calling this trigger function.
       This kind of history tracking is also known as an "audit trail".
       This function works by detecting each change in value for important fields in a certain table.
       This trigger function then calls another function to create a row in the "history_" table.

    This kind of feature is often called an "audit trail" by software developers. I avoid using that term in this
contextas a real 
    audit trail in accounting terms involves more than this simple field change tracking.
*/

/*    © 2011 Basil Bourque. This source code may be used freely forever by anyone taking full responsibility for doing
so,without warranty.  

    Thanks so very much to John DeSoi of the pgsql-general@postgresql.org mailing list for pointing me to this crucial
codeexample: 
    http://wiki.postgresql.org/wiki/PL/pgSQL_Dynamic_Triggers
    Before reading that example, my previous efforts led me to conclude a generic history facility written in PL/pgSQL
wasimpossible. 
*/

/*     We make these assumptions about any table using this function in its trigger:
           • Has a primary key named "pkey_" of type uuid.
           • Has a field tracking the datetime the record was last modified, named "record_modified_" of type
timestamptz.
           • Is in the default/current schema.

       While it might be nice to rewrite this function to escape these assumptions, I've spent all my energies to get
thisfar. 
    I welcome feedback from anyone who want to take this further.
*/

/*     Notes:

    The 'OLD' and 'NEW' variables represent the entire row whose INSERT/UPDATE/DELETE caused this trigger to run.

       The 'TG_xxx' variables are special variables created automatically by Postgres for the trigger function.
       For example, TG_OP indicates which modification operation is happening: INSERT, UPDATE, or DELETE.
       http://www.postgresql.org/docs/current/static/plpgsql-trigger.html

       "clock_timestamp()" gets the actual time at the moment of execution. In contrast, most other timestamp
       functions return the time when the current transaction began.
    For more information, see: http://www.postgresql.org/docs/current/static/functions-datetime.html

    The "history_" table also includes a column "transaction_began_" defaulting to "transaction_timestamp()". This
timestampcan act 
    like a transaction identifier, to group multiple "history_" rows of the same transaction together. This is not
foolproof,as  
    multiple transaction could possibly start in the same split second moment. Assuming the computer's clock has a fine
resolution,
    this chance of a coincidence should be quite miniscule. If someone knows a way to get a true transaction id, please
share.
*/

DECLARE
    ri RECORD; -- About this data type "RECORD":
http://www.postgresql.org/docs/current/static/plpgsql-declarations.html#PLPGSQL-DECLARATION-RECORDS
    oldValue TEXT;
    newValue TEXT;
    isColumnSignificant BOOLEAN;
    isValueModified BOOLEAN;
BEGIN
    /*RAISE NOTICE E'\n    Running function: make_history_for_row_ ----------------\n\n    Operation: %\n    Schema:
%\n   Table: %\n', 
        TG_OP,
        TG_TABLE_SCHEMA,
        TG_TABLE_NAME;*/

    IF (TG_OP = 'INSERT') OR (TG_OP = 'UPDATE') THEN
        NEW.record_modified_ = clock_timestamp(); -- Record the moment this row is being saved.

        FOR ri IN
            -- Fetch a ResultSet listing columns defined for this trigger's table.
            SELECT ordinal_position, column_name, data_type
            FROM information_schema.columns
            WHERE
                table_schema = quote_ident(TG_TABLE_SCHEMA)
            AND table_name = quote_ident(TG_TABLE_NAME)
            ORDER BY ordinal_position

        LOOP
            -- For each column in this trigger's table, copy the OLD & NEW values into respective variables.
            -- NEW value
            EXECUTE 'SELECT ($1).' || ri.column_name || '::text' INTO newValue USING NEW;
            -- OLD value
            IF (TG_OP = 'INSERT') THEN   -- If operation is an INSERT, we have no OLD value, so use an empty string.
                oldValue := ''::varchar;
            ELSE   -- Else operation is an UPDATE, so capture the OLD value.
                EXECUTE 'SELECT ($1).' || ri.column_name || '::text' INTO oldValue USING OLD;
            END IF;

            -- Make noise for debugging.
            /*RAISE NOTICE E'\n    Column #: %\n    Name: %\n    Type: %\n    Old: %\n    New: %\n',
                ri.ordinal_position,
                ri.column_name,
                ri.data_type,
                oldValue,
                newValue;*/

            --    ToDo: Add code to throw an Exception if the primary key value is changing (other than from NULL on an
INSERT).

            --     ToDo: Add code to ignore columns whose data type does not cast well to TEXT/VARCHAR.

            --    Ignore some columns:
            --         • Those whose names are marked with a trailing x.
            --        • The primary key.
            --         • Our timestamp fields recording the row's creation and most recent modification.
            isColumnSignificant := (position( '_x_' in ri.column_name ) < 1) AND (ri.column_name <> 'pkey_') AND
(ri.column_name<> 'record_created_') AND (ri.column_name <> 'record_modified_'); 
            IF isColumnSignificant THEN
                isValueModified := oldValue <> newValue;  -- If this nthField in the table was modified, make history.
                IF isValueModified THEN
                    /*RAISE NOTICE E'Inserting history_ row for INSERT or UPDATE.\n';*/
                    INSERT INTO history_ ( operation_, table_oid_, table_name_, uuid_, column_name_,
ordinal_position_of_column_,old_value_, new_value_ ) 
                    VALUES ( TG_OP, TG_RELID, TG_TABLE_NAME, NEW.pkey_, ri.column_name::VARCHAR, ri.ordinal_position,
oldValue::VARCHAR,newValue::VARCHAR ); 
                END IF;
            END IF;
        END LOOP;

        RETURN NEW;

    ELSIF (TG_OP = 'DELETE') THEN
        /*RAISE NOTICE E'Inserting history_ row for DELETE.\n';*/
        --    Similar to INSERT above, but refers to OLD instead of NEW, and passes empty values for last 4 fields.
        INSERT INTO history_ ( operation_, table_oid_, table_name_, uuid_, column_name_, ordinal_position_of_column_,
old_value_,new_value_ ) 
        VALUES ( TG_OP, TG_RELID, TG_TABLE_NAME, OLD.pkey_, ''::VARCHAR, 0, ''::VARCHAR, ''::VARCHAR );
        RETURN OLD;

    END IF;

     /* Should never reach this point. Branching in code above should always reach a call to RETURN. */
    RAISE EXCEPTION 'Unexpectedly reached the bottom of this function without calling RETURN.';

END;

$BODY$;

<end of code>

01.05.2011 12:58, Basil Bourque wrote:
> Hoorah! I was able to complete my single PL/pgSQL function to create history records tracking individual field value
changesgenerically for all my tables. Some developers call this an "audit trail", though an accountant might say
otherwise.
>
I made auditing based on triggers like aforementioned. And now I need
fill audit table with already presented data. But there is a problem.

within trigger
EXECUTE 'SELECT ($1)."name"::text' INTO newVal USING NEW;
works fine

but function (table "decor" has field "name")
CREATE OR REPLACE FUNCTION "odb_InitLog"()
   RETURNS void AS
DECLARE
   obj record;
BEGIN
   FOR obj IN (SELECT * FROM "decor") LOOP
     EXECUTE 'SELECT ($1)."name"::text' INTO newVal USING obj;
   END LOOP;
END;
doesn't work - ERROR: could not identify column "name" in record data type

Why?

On Wed, Aug 24, 2011 at 1:03 PM, ivan_14_32 <ivan_14_32@mail.ru> wrote:
> 01.05.2011 12:58, Basil Bourque wrote:
>>
>> Hoorah! I was able to complete my single PL/pgSQL function to create
>> history records tracking individual field value changes generically for all
>> my tables. Some developers call this an "audit trail", though an accountant
>> might say otherwise.
>>
> I made auditing based on triggers like aforementioned. And now I need fill
> audit table with already presented data. But there is a problem.
>
> within trigger
> EXECUTE 'SELECT ($1)."name"::text' INTO newVal USING NEW;
> works fine
>
> but function (table "decor" has field "name")
> CREATE OR REPLACE FUNCTION "odb_InitLog"()
>  RETURNS void AS
> DECLARE
>  obj record;
> BEGIN
>  FOR obj IN (SELECT * FROM "decor") LOOP
>    EXECUTE 'SELECT ($1)."name"::text' INTO newVal USING obj;
>  END LOOP;
> END;
> doesn't work - ERROR: could not identify column "name" in record data type

folks, this (dynamic field access of generic record in plpgsql) is the
number one FAQ on this list.  please check the archives before
searching (not picking on you specifically, it just gets asked in some
variant an awful lot).  First point:  hstore > execute.  if you _must_
use execute, you have to cast at some point.  when you pass a record
to something, it doesn't have the necessary context to know the field
names.   In your case, though, an explicit composite type is the way
to go:

DECLARE
 obj decor;
BEGIN
 FOR obj IN SELECT * FROM decor LOOP
   newVal := obj.name;
 END LOOP;
END;

merlin

Hi everybody,

I want to thank Basile for his function but I've problems with it.

1- When I tried to apply it in postgres SQL 8.4 on a DB in LATIN1 I've this
message :

ERROR:  character 0xe280a2 of encoding "UTF8" has no equivalent in "LATIN1"

2- I tried this function on a DB in UTF8 and I've this message :

ERROR:  syntax error at or near "USING"
LIGNE 34 : ...$1).' || ri.column_name || '::text' INTO newValue USING NEW;

3- So, I tried  I delete the words USING NEW and USING and when I want to
update or insert a data I've this message :

there is no parameter $1

I don't understand how we can using the parameter $1 in an sql request.

Thank you for your response and sorry for my English.

Geo-x

--
View this message in context:
http://postgresql.1045698.n5.nabble.com/OLD-myColumnNameVar-How-to-generically-access-columns-in-a-trigger-s-OLD-or-NEW-records-tp4347470p5685049.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Hello

this can help http://postgres.cz/wiki/PostgreSQL_SQL_Tricks#Iteration_over_RECORD_variable_inside_trigger

Regards

Pavel Stehule

2012/5/4 Geo-x <x-lalande@cc-lacq.fr>:
> Hi everybody,
>
> I want to thank Basile for his function but I've problems with it.
>
> 1- When I tried to apply it in postgres SQL 8.4 on a DB in LATIN1 I've this
> message :
>
> ERROR:  character 0xe280a2 of encoding "UTF8" has no equivalent in "LATIN1"
>
> 2- I tried this function on a DB in UTF8 and I've this message :
>
> ERROR:  syntax error at or near "USING"
> LIGNE 34 : ...$1).' || ri.column_name || '::text' INTO newValue USING NEW;
>
> 3- So, I tried  I delete the words USING NEW and USING and when I want to
> update or insert a data I've this message :
>
> there is no parameter $1
>
> I don't understand how we can using the parameter $1 in an sql request.
>
> Thank you for your response and sorry for my English.
>
> Geo-x
>
> --
> View this message in context:
http://postgresql.1045698.n5.nabble.com/OLD-myColumnNameVar-How-to-generically-access-columns-in-a-trigger-s-OLD-or-NEW-records-tp4347470p5685049.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

On Fri, May 4, 2012 at 8:04 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
> Hello
>
> this can help http://postgres.cz/wiki/PostgreSQL_SQL_Tricks#Iteration_over_RECORD_variable_inside_trigger

hstore has replaced information schema method for me when doing these
type of things:

see: http://archives.postgresql.org/pgsql-general/2012-02/msg00251.php

merlin