cascading an insert trigger/rule help - Mailing list pgsql-general

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