Re: cascading an insert trigger/rule help

On Mon, 10 Jun 2002, s wrote:

> I tried to create a trigger/function set for postgres:
>
>
>     CREATE or REPLACE FUNCTION foo_insert_function() RETURNS opaque as
> '
>       DECLARE
>         rec_num INTEGER;
>       BEGIN
>         -- is the new name already in the fooplus table
>         -- here I check if it's there at all;
>         -- I'd really like to know if it's there for every name/attr
>
>         select count(*) into rec_num
>         from fooplus f
>         where f.name = new.name;
>
>         IF rec_num < 1
>         THEN
>           insert into fooplus(name, attr)
>           select new.name, a.attr
>           from attrib a;
>         END IF;
>       END;
>     ' LANGUAGE plpgsql;
>
>     CREATE TRIGGER foo_insert_trigger
>       AFTER INSERT ON foo
>       FOR EACH ROW
>       EXECUTE PROCEDURE foo_insert_function();
>
> I get errors on insert indicating that the end of the function is
> reached with no return value.  I thought opaque functions didn't
> return a value?

For trigger functions you still need a return, for an
after trigger, return NULL; should probably be fine.

> So I dropped the trigger and tried:
>
>     create rule foo_insert_rule as
>      on insert to foo do
>      insert into fooplus(name, attr)
>        select new.name, a.attr
>        from attrib a;
>
> I get a cache error on when I now try to insert into foo
>
> ERROR:  fmgr_info: function 18075: cache lookup failed
>
> Suggestions?  Pointers to documentation?

Are you absolutely sure you dropped all the triggers?  Often
that error occurs when a trigger function is dropped and the
trigger is not (so it's referencing a now removed function).

You might want to try to find the trigger in question.  Maybe
select * from pg_trigger where tgfoid=18075;
would give them?