Thread: plpgsql question...
I'm missing something critical in writing simple triggers on views using plpgsql. Almost every function I try produces an "ERROR during compile of ..." when it is triggered, but I can't see what I've got wrong. PostgreSQL 6.5.3 Here's a sample, where the function just transfers the insert on the view to an insert on a table. (This was just a test of trigger functions...): View is "attributes" Table is "attributes_table" ======================== appdb=> create function insert_or_update() returns opaque as ' appdb'> begin appdb'> insert into attributes_table values(new.id,new.name, appdb'> new.units,new.value); appdb'> return NULL; appdb'> end;' appdb-> language 'plpgsql'; CREATE appdb=> create trigger t before insert on attributes for each row appdb-> execute procedure insert_or_update(); CREATE appdb=> insert into attributes values('site','prefix','none','kp'); NOTICE: plpgsql: ERROR during compile of insert_or_update near line 2 ERROR: syntax error at or near "in" appdb=> ========================= Does anyone see what I've done wrong? Removing the "insert into" statement allows the insert to run, but of course nothing gets done... An even simpler function that just tries to return new also fails: ================ appdb=> create function insert_or_update() returns opaque as ' appdb'> begin appdb'> return new; appdb'> end;' appdb-> language 'plpgsql'; CREATE appdb=> create trigger t before insert on attributes for each row appdb-> execute procedure insert_or_update(); CREATE appdb=> insert into attributes values('site','prefix','none','kp'); NOTICE: plpgsql: ERROR during compile of insert_or_update near line 2 ERROR: return type mismatch in function returning table row at or near "n" appdb=> ================ Clearly I'm missing something obvious. Thanks! -- Steve Wampler- SOLIS Project, National Solar Observatory swampler@noao.edu
Steve Wampler <swampler@noao.edu> writes: > PostgreSQL 6.5.3 > ======================== > appdb=> create function insert_or_update() returns opaque as ' > appdb'> begin > appdb'> insert into attributes_table values(new.id,new.name, > appdb'> new.units,new.value); > appdb'> return NULL; > appdb'> end;' > appdb-> language 'plpgsql'; > CREATE > appdb=> create trigger t before insert on attributes for each row > appdb-> execute procedure insert_or_update(); > CREATE > appdb=> insert into attributes values('site','prefix','none','kp'); > NOTICE: plpgsql: ERROR during compile of insert_or_update near line 2 > ERROR: syntax error at or near "in" > appdb=> > ========================= > Does anyone see what I've done wrong? Nothing that I can see. I copied and pasted this trigger into current sources and it worked fine. Ditto for your other example. There must be something pretty broken about your copy of plpgsql; dunno what exactly. I'd recommend updating to 7.0.2 and then seeing if the problem persists. If it does we can dig deeper. regards, tom lane
Tom Lane wrote: > > Steve Wampler <swampler@noao.edu> writes: > > PostgreSQL 6.5.3 > > > ======================== > > appdb=> create function insert_or_update() returns opaque as ' > > appdb'> begin > > appdb'> insert into attributes_table values(new.id,new.name, > > appdb'> new.units,new.value); > > appdb'> return NULL; > > appdb'> end;' > > appdb-> language 'plpgsql'; > > CREATE > > appdb=> create trigger t before insert on attributes for each row > > appdb-> execute procedure insert_or_update(); > > CREATE > > appdb=> insert into attributes values('site','prefix','none','kp'); > > NOTICE: plpgsql: ERROR during compile of insert_or_update near line 2 > > ERROR: syntax error at or near "in" > > appdb=> > > ========================= > > > Does anyone see what I've done wrong? > > Nothing that I can see. I copied and pasted this trigger into current > sources and it worked fine. Ditto for your other example. > > There must be something pretty broken about your copy of plpgsql; > dunno what exactly. I'd recommend updating to 7.0.2 and then seeing > if the problem persists. If it does we can dig deeper. Just a followup to the group - upgrading from 6.5.3 to 7.0.2 fixed the problem, as Tom suggested (both on Linux [RH6.1 - really 7.0.3 here] and Solaris 2.7). Thanks, Tom! -- Steve Wampler- SOLIS Project, National Solar Observatory swampler@noao.edu