From: | Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> |
---|---|
To: | s <smarie(at)ekno(dot)com> |
Cc: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: cascading an insert trigger/rule help |
Date: | 2002-06-11 00:00:18 |
Message-ID: | 20020610165505.M72309-100000@megazone23.bigpanda.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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?
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2002-06-11 02:00:27 | Re: Help with data transfer please |
Previous Message | s | 2002-06-10 23:42:04 | cascading an insert trigger/rule help |