agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed

From: James Sharrett <jsharrett@tidemark.net>
To: pgsql-sql@postgresql.org
Subject: the value of OLD on an initial row insert
Date: Fri, 20 Sep 2013 12:43:47 -0400
Message-ID: <CE61F094.F8E3%jsharrett@tidemark.net> (raw)
In-Reply-To: <l1ht05$9cq$1@ger.gmane.org>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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




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

view thread (2+ messages)  latest in thread

Message-ID: <CE61F094.F8E3%jsharrett@tidemark.net>
Permalink:  ../CE61F094.F8E3%25jsharrett@tidemark.net/
Also on:    postgresql.org/message-id/CE61F094.F8E3%jsharrett@tidemark.net

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: jsharrett@tidemark.net
  Subject: Re: the value of OLD on an initial row insert
  In-Reply-To: <CE61F094.F8E3%jsharrett@tidemark.net>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox