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