User-Agent: Microsoft-MacOutlook/14.3.7.130812
Date: Fri, 20 Sep 2013 12:43:47 -0400
Subject: the value of OLD on an initial row insert
From: James Sharrett <jsharrett@tidemark.net>
To: <pgsql-sql@postgresql.org>
Message-ID: <CE61F094.F8E3%jsharrett@tidemark.net>
Thread-Topic: the value of OLD on an initial row insert
In-Reply-To: <l1ht05$9cq$1@ger.gmane.org>
Mime-version: 1.0
Content-type: text/plain;
	charset="US-ASCII"
Content-transfer-encoding: 7bit
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

I have a number of trigger functions on a table that are performing
various calculations.  The table is a column-wise orientation with
multiple columns that could be updated on a single row.  In one of the
triggers, I'm performing a calculation but don't want the code to run if
the OLD and NEW values are the same value.  This can be resulting from
other triggers that are running on the table.  If there is a truly NEW
(non-NULL) value, I want to run the code.

To deal with this, I'm using the following test in my code where I loop
through the columns that could be updated and test to determine which
column on the row is getting a value assigned.


EXECUTE 'SELECT (' ||quote_literal(NEW) || '::' || TG_RELID::regclass
||').' || quote_ident(metric_record.column_name) INTO changed_metric;
	       
if not changed_metric is null then

EXECUTE 'SELECT (' ||quote_literal(OLD) || '::' || TG_RELID::regclass
||').' || quote_ident(metric_record.column_name) INTO old_value;

if changed_metric <> old_value then

{calculation code}


This is all doing exactly what I want when the row exists.  However, I
think I'm getting an error if there is a new row getting generated.  I'm
getting the following error when the code runs sometimes:

ERROR: record "old" is not assigned yet
SQL state: 55000
Detail: The tuple structure of a not-yet-assigned record is indeterminate.

Is this what's happening?  If so, how can I avoid the issue.


Thanks,
James


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql