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@ekno.com
____________________________________________________________________________
Lonely Planet's ekno - more than a phonecard
Get ekno before you go!
http://www.ekno.lonelyplanet.com