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
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 |