Skip site navigation (1) Skip section navigation (2)

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

From: Basil Bourque <basil(dot)list(at)me(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: "OLD." || myColumnNameVar (How to generically access columns in a trigger's OLD or NEW records)
Date: 2011-04-28 19:46:50
Message-ID: (view raw or flat)
Lists: pgsql-general
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 log 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 get an 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 

    metadata_record RECORD; 
	/* */
	table_dot_column VARCHAR;
	my_sql VARCHAR;
	column_is_loggable_var BOOLEAN;
	edited_var BOOLEAN;


	-- Ignore this case 


	/* 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;


	-- ignore this case


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



pgsql-general by date

Next:From: hubert depesz lubaczewskiDate: 2011-04-28 19:57:10
Subject: Re: "OLD." || myColumnNameVar (How to generically access columns in a trigger's OLD or NEW records)
Previous:From: Robert TreatDate: 2011-04-28 19:34:00
Subject: Re: SSDs with Postgresql?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group