cascading an insert trigger/rule help

Hi,

I'm using postgres after not using it for more than a year. I have
searched the documentation, but I've been unable to figure out whether
I need a rule or a trigger to cascade an insert. I had an old
postgres database that I ported to oracle, and now I need to port a
modified version back! I *have* searched the documentation and list
archives. Any suggestions are appreciated.

I have a toy example. I realize I could use a view to get the desired
effect in my example, but this is just a toy.

I have 3 tables. For two of the tables, if I insert a row I want to
automatically insert a row into the 3rd table.

create table foo (
name char(3) primary key,
value varchar(25) not null
);

create table fooplus (
name char(3) not null,
attr varchar(25) not null
);

create table attrib (
attr varchar(25) primary key
);

I don't want to rollback the insert if there's a duplicate value on
the index, i.e. the row already exists in fooplus. This is possible
in my real world example. My oracle triggers on foo & attrib looked
like this:

CREATE TRIGGER foo_insert_trigger
AFTER INSERT ON foo
FOR EACH ROW
BEGIN
insert into fooplus(name, attr)
select :new.name, a.attr
from attrib a;
EXCEPTION
when DUP_VAL_ON_INDEX then
null;
END;

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?

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?

Thanks,

Sarah
smarie(at)ekno(dot)com

____________________________________________________________________________
Lonely Planet's ekno - more than a phonecard
Get ekno before you go!
http://www.ekno.lonelyplanet.com