Обсуждение: 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