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