From: Joseph Shraibman <jks@selectacast.net>
To: Joe Conway <jconway2@home.com>
To: pgsql-sql@postgresql.org
Subject: Re: 'record old is unassigned yet' when using trigger in 7.1.1
Date: Wed, 09 May 2001 19:44:03 -0400
Message-ID: <3AF9D643.56296A4A@selectacast.net> (raw)
References: <3AF9AECE.DE93E098@selectacast.net>
<000c01c0d8d2$cefddf90$54d410ac@jecw2k1>
<3AF9D542.88C9FACD@selectacast.net>
Two more things:
1) Yes, I know that in the second function I should be looking for
INSERT, that isn't the problem.
2) Why when I use psql does it look like this:
playpen=# create function utbl_set_statchangedate() returns opaque as
playpen-# 'begin
playpen'# IF TG_OP = \'INSERT\'
playpen'# THEN
playpen'# NEW.statchangedate := CURRENT_DATE;
playpen'# ELSE IF OLD.status <> NEW.status
playpen'# THEN
playpen'#
ABORT BEGIN CLUSTER COMMIT CREATE DELETE EXPLAIN
GRANT LISTEN LOCK NOTIFY REVOKE SELECT SHOW
UNLISTEN VACUUM
ALTER CLOSE COMMENT COPY DECLARE DROP FETCH
INSERT LOAD MOVE RESET ROLLBACK SET TRUNCATE
UPDATE
playpen'# NEW.statchangedate := CURRENT_DATE;
playpen'# END IF;
playpen'# return NEW;
playpen'# end;
playpen'# ' language 'plpgsql';
CREATE
playpen=#
? What's with the \nABORT .. UPDATE ?
Joseph Shraibman wrote:
>
> OK I tried:
> create function utbl_set_statchangedate() returns opaque as '
> begin
> IF TG_OP = \'INSERT\' OR OLD.status <> NEW.status
> THEN
> NEW.statchangedate = CURRENT_DATE;
> END IF;
> return NEW;
> end;
> ' language 'plpgsql';
>
> ... but that had the same problem. So then I tried:
>
> create function utbl_set_statchangedate() returns opaque as
> 'begin
> IF TG_OP = \'UPDATE\'
> THEN
> NEW.statchangedate := CURRENT_DATE;
> ELSE IF OLD.status <> NEW.status
> THEN
> NEW.statchangedate := CURRENT_DATE;
> END IF;
> return NEW;
> end;
> ' language 'plpgsql';
>
> but now I'm getting:
> NOTICE: plpgsql: ERROR during compile of utbl_set_statchangedate near
> line 10
> ERROR: parse error at or near ";"
>
> Joe Conway wrote:
> >
> > > begin
> > > IF OLD.status <> NEW.status
> > > THEN
> > > NEW.statchangedate = CURRENT_DATE;
> > > END IF;
> > > return NEW;
> > > end;
> > >
> > > The problem is when a new row is inserted I get this error message:
> > > ERROR: record old is unassigned yet
> > > ... and the insert fails.
> > >
> > > It doesn't matter if the trigger is before or after.
> >
> > OLD doesn't exist on inserts, only on update and delete. If you want to use
> > the same function for both insert and update use the TG_OP variable and an
> > if statement. See
> > http://www.postgresql.org/idocs/index.php?plpgsql-trigger.html
> >
> > Hope this helps,
> >
> > Joe
>
> --
> Joseph Shraibman
> jks@selectacast.net
> Increase signal to noise ratio. http://www.targabot.com
--
Joseph Shraibman
jks@selectacast.net
Increase signal to noise ratio. http://www.targabot.com
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: jks@selectacast.net, jconway2@home.com
Subject: Re: 'record old is unassigned yet' when using trigger in 7.1.1
In-Reply-To: <3AF9D643.56296A4A@selectacast.net>
References: <3AF9AECE.DE93E098@selectacast.net> <000c01c0d8d2$cefddf90$54d410ac@jecw2k1> <3AF9D542.88C9FACD@selectacast.net> <3AF9D643.56296A4A@selectacast.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox