Received: from p1.selectacast.net (p1.selectacast.net [206.41.1.251]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f49NdhA74415 for ; Wed, 9 May 2001 19:39:43 -0400 (EDT) (envelope-from jks@selectacast.net) Received: from selectacast.net (IDENT:jks@xtenit.dialup.access.net [166.84.206.91]) by p1.selectacast.net (8.9.3/8.9.3) with ESMTP id TAA09213; Wed, 9 May 2001 19:39:36 -0400 Message-ID: <3AF9D542.88C9FACD@selectacast.net> Date: Wed, 09 May 2001 19:39:46 -0400 From: Joseph Shraibman Organization: Xtenit, Inc. http://www.xtenit.com X-Mailer: Mozilla 4.76 [en] (X11; U; Linux 2.2.15 i686) X-Accept-Language: en MIME-Version: 1.0 To: Joe Conway CC: pgsql-sql@postgresql.org Subject: Re: 'record old is unassigned yet' when using trigger in 7.1.1 References: <3AF9AECE.DE93E098@selectacast.net> <000c01c0d8d2$cefddf90$54d410ac@jecw2k1> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200105/69 X-Sequence-Number: 2870 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