pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
'record old is unassigned yet' when using trigger in 7.1.1
10+ messages / 4 participants
[nested] [flat]

* 'record old is unassigned yet' when using trigger in 7.1.1
@ 2001-05-09 20:55  Joseph Shraibman <jks@selectacast.net>
  0 siblings, 1 reply; 10+ messages in thread

From: Joseph Shraibman @ 2001-05-09 20:55 UTC (permalink / raw)
  To: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: 'record old is unassigned yet' when using trigger in 7.1.1
@ 2001-05-09 21:55  Joe Conway <jconway2@home.com>
  parent: Joseph Shraibman <jks@selectacast.net>
  0 siblings, 2 replies; 10+ messages in thread

From: Joe Conway @ 2001-05-09 21:55 UTC (permalink / raw)
  To: Joseph Shraibman <jks@selectacast.net>; pgsql-sql

>        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






^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* RE: 'record old is unassigned yet' when using trigger in 7.1.1
@ 2001-05-09 21:58  Michael Davis <mdavis@sevainc.com>
  0 siblings, 0 replies; 10+ messages in thread

From: Michael Davis @ 2001-05-09 21:58 UTC (permalink / raw)
  To: 'Joseph Shraibman' <jks@selectacast.net>; pgsql-sql

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)




^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: 'record old is unassigned yet' when using trigger in 7.1.1
@ 2001-05-09 22:25  Joseph Shraibman <jks@selectacast.net>
  parent: Joe Conway <jconway2@home.com>
  1 sibling, 0 replies; 10+ messages in thread

From: Joseph Shraibman @ 2001-05-09 22:25 UTC (permalink / raw)
  To: Joe Conway <jconway2@home.com>; +Cc: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: 'record old is unassigned yet' when using trigger in 7.1.1
@ 2001-05-09 23:39  Joseph Shraibman <jks@selectacast.net>
  parent: Joe Conway <jconway2@home.com>
  1 sibling, 3 replies; 10+ messages in thread

From: Joseph Shraibman @ 2001-05-09 23:39 UTC (permalink / raw)
  To: Joe Conway <jconway2@home.com>; +Cc: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: 'record old is unassigned yet' when using trigger in 7.1.1
@ 2001-05-09 23:44  Joseph Shraibman <jks@selectacast.net>
  parent: Joseph Shraibman <jks@selectacast.net>
  2 siblings, 2 replies; 10+ messages in thread

From: Joseph Shraibman @ 2001-05-09 23:44 UTC (permalink / raw)
  To: Joe Conway <jconway2@home.com>; 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



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: 'record old is unassigned yet' when using trigger in 7.1.1
@ 2001-05-09 23:49  Joe Conway <jconway2@home.com>
  parent: Joseph Shraibman <jks@selectacast.net>
  2 siblings, 0 replies; 10+ messages in thread

From: Joe Conway @ 2001-05-09 23:49 UTC (permalink / raw)
  To: Joseph Shraibman <jks@selectacast.net>; +Cc: pgsql-sql

> ... 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.status THEN
             NEW.statchangedate := CURRENT_DATE;
         END IF;
     END IF;
return NEW;
end;
' language 'plpgsql';

-- Joe




^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: 'record old is unassigned yet' when using trigger in 7.1.1
@ 2001-05-09 23:55  Joe Conway <jconway2@home.com>
  parent: Joseph Shraibman <jks@selectacast.net>
  1 sibling, 0 replies; 10+ messages in thread

From: Joe Conway @ 2001-05-09 23:55 UTC (permalink / raw)
  To: Joseph Shraibman <jks@selectacast.net>; pgsql-sql

> 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




^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: 'record old is unassigned yet' when using trigger in 7.1.1
@ 2001-05-10 00:12  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Joseph Shraibman <jks@selectacast.net>
  2 siblings, 0 replies; 10+ messages in thread

From: Tom Lane @ 2001-05-10 00:12 UTC (permalink / raw)
  To: Joseph Shraibman <jks@selectacast.net>; +Cc: Joe Conway <jconway2@home.com>; pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: 'record old is unassigned yet' when using trigger in 7.1.1
@ 2001-05-10 00:27  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Joseph Shraibman <jks@selectacast.net>
  1 sibling, 0 replies; 10+ messages in thread

From: Tom Lane @ 2001-05-10 00:27 UTC (permalink / raw)
  To: Joseph Shraibman <jks@selectacast.net>; +Cc: Joe Conway <jconway2@home.com>; pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 10+ messages in thread


end of thread, other threads:[~2001-05-10 00:27 UTC | newest]

Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-05-09 20:55 'record old is unassigned yet' when using trigger in 7.1.1 Joseph Shraibman <jks@selectacast.net>
2001-05-09 21:55 ` Joe Conway <jconway2@home.com>
2001-05-09 22:25   ` Joseph Shraibman <jks@selectacast.net>
2001-05-09 23:39   ` Joseph Shraibman <jks@selectacast.net>
2001-05-09 23:44     ` Joseph Shraibman <jks@selectacast.net>
2001-05-09 23:55       ` Joe Conway <jconway2@home.com>
2001-05-10 00:27       ` Tom Lane <tgl@sss.pgh.pa.us>
2001-05-09 23:49     ` Joe Conway <jconway2@home.com>
2001-05-10 00:12     ` Tom Lane <tgl@sss.pgh.pa.us>
2001-05-09 21:58 RE: 'record old is unassigned yet' when using trigger in 7.1.1 Michael Davis <mdavis@sevainc.com>

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