Thread: 'record old is unassigned yet' when using trigger in 7.1.1
I have a function used in a trigger that looks like this:
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.
--
Joseph Shraibman
jks@selectacast.net
Increase signal to noise ratio. http://www.targabot.com
> 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
When creating the trigger you need to specify BEFORE UPDATE rather than BEFORE INSERT.
-----Original Message-----
From: Joseph Shraibman [SMTP:jks@selectacast.net]
Sent: Wednesday, May 09, 2001 2:56 PM
To: pgsql-sql@postgresql.org
Subject: 'record old is unassigned yet' when using trigger in 7.1.1
I have a function used in a trigger that looks like this:
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.
--
Joseph Shraibman
jks@selectacast.net
Increase signal to noise ratio. http://www.targabot.com
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to
majordomo@postgresql.org)
Thanks, I sort of figured it out after I sent the email, but I'm still working on it. 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
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
> ... 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'; There is no "else if" in plpgsql, if I remember correctly. Try: create function utbl_set_statchangedate() returns opaque as 'begin IF TG_OP = ''UPDATE'' THEN NEW.statchangedate := CURRENT_DATE; ELSE IF OLD.status <> NEW.statusTHEN NEW.statchangedate := CURRENT_DATE; END IF; END IF; return NEW; end; ' language 'plpgsql'; -- Joe
> 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 <snip> > ? What's with the \nABORT .. UPDATE ? I've seen this before also. I think psql gets confused and prints out some sort of keyword list. Try saving your function to a file, and then use \i, e.g. playpen'# \i /path/to/your/script/scriptname.sql -- Joe
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
I think you're short an END IF. Last I checked, plpgsql didn't have
an "else if" construct, so it wants an END IF to go with each IF.
regards, tom lane
Joseph Shraibman <jks@selectacast.net> writes:
> ? What's with the \nABORT .. UPDATE ?
In psql, TAB triggers autocompletion if you have compiled it with
readline. This is fairly annoying when copying-and-pasting from a text
message that happens to contain tabs...
Use psql -n to disable this feature.
regards, tom lane