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?