pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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



view thread (10+ messages)  latest in thread

Message-ID: <3AF9D643.56296A4A@selectacast.net>
Permalink:  ../3AF9D643.56296A4A@selectacast.net/
Also on:    postgresql.org/message-id/3AF9D643.56296A4A@selectacast.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 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