the value of OLD on an initial row insert

From: James Sharrett <jsharrett(at)tidemark(dot)net>
To: <pgsql-sql(at)postgresql(dot)org>
Subject: the value of OLD on an initial row insert
Date: 2013-09-20 16:43:47
Message-ID: CE61F094.F8E3%jsharrett@tidemark.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

In response to

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Thomas Fanghaenel 2013-09-23 00:51:26 Comparison semantics of CHAR data type
Previous Message Thomas Kellerer 2013-09-20 16:42:08 Re: unique key problem on update