agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedthe value of OLD on an initial row insert
2+ messages / 2 participants
[nested] [flat]
* the value of OLD on an initial row insert
@ 2013-09-20 16:43 James Sharrett <jsharrett@tidemark.net>
2013-09-23 06:45 ` Re: the value of OLD on an initial row insert Luca Ferrari <fluca1978@infinito.it>
0 siblings, 1 reply; 2+ messages in thread
From: James Sharrett @ 2013-09-20 16:43 UTC (permalink / raw)
To: 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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: the value of OLD on an initial row insert
2013-09-20 16:43 the value of OLD on an initial row insert James Sharrett <jsharrett@tidemark.net>
@ 2013-09-23 06:45 ` Luca Ferrari <fluca1978@infinito.it>
0 siblings, 0 replies; 2+ messages in thread
From: Luca Ferrari @ 2013-09-23 06:45 UTC (permalink / raw)
To: James Sharrett <jsharrett@tidemark.net>; +Cc: pgsql-sql
On Fri, Sep 20, 2013 at 6:43 PM, James Sharrett <jsharrett@tidemark.net> wrote:
> 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.
If I get it right you are running the trigger also for an insert,
which of course does not have an old value. You should either set the
trigger to run only on update statements or enforce your check to see
if the trigger has been invoked for something different than insert
statements.
Luca
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2013-09-23 06:45 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-09-20 16:43 the value of OLD on an initial row insert James Sharrett <jsharrett@tidemark.net>
2013-09-23 06:45 ` Luca Ferrari <fluca1978@infinito.it>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox