Re: 'record old is unassigned yet' when using trigger in 7.1.1
От | Joseph Shraibman |
---|---|
Тема | Re: 'record old is unassigned yet' when using trigger in 7.1.1 |
Дата | |
Msg-id | 3AF9D643.56296A4A@selectacast.net обсуждение исходный текст |
Ответ на | 'record old is unassigned yet' when using trigger in 7.1.1 (Joseph Shraibman <jks@selectacast.net>) |
Ответы |
Re: 'record old is unassigned yet' when using trigger in 7.1.1
(Tom Lane <tgl@sss.pgh.pa.us>)
|
Список | pgsql-sql |
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
В списке pgsql-sql по дате отправления:
Предыдущее
От: "Joe Conway"Дата:
Сообщение: Re: 'record old is unassigned yet' when using trigger in 7.1.1
Следующее
От: Tom LaneДата:
Сообщение: Re: 'record old is unassigned yet' when using trigger in 7.1.1