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
