RULE vs TRIGGER

i have a solution using RULES and PLGPSQL functions (instead of
triggers) for insert-unless-found, using perl lingo:

    # perlish pseudoCode
    unless (select(tbl.fld == val)) { insert tbl.fld = val };

i'd love to hear the skinny on why the following is a bad idea,
which i presume it is because 1) it works and 2) i understand
it:

    -- mostly static lookup table:
    create TABLE lookup(
      id serial,
      val varchar(50),
      primary key( id )
    );

    -- dynamic data (lots of traffic here):
    create TABLE _real_data (
      -- ...
      lookup integer
        references lookup(id),
      -- ...
    );

    -- a view to tie them together:
    create VIEW see_data as
      select
        -- _real_data.* ...
        l.val as lookup, -- display text, not id
        -- ...
      from
        _real_data  r,
        lookup      l
      where
        r.lookup = l.id;

    -- here's the workhorse:
    create FUNCTION get_lookup(varchar) returns integer as '
      declare
        t alias for $1;
        i integer;
      begin
        -- maybe it exists already:
        select into i
          id
          from lookup
          where val = t;
        -- if not, create it:
        if not found then
          insert into lookup (val) values (t);
          i := currval(''lookup_id_seq'');
        end if;
        -- return its id:
        return i;
      end;' language 'plpgsql'; --'

    -- and here's the capstone:
    create RULE new_data as
      on insert to see_data
      do instead [
        insert into _real_data (
          -- ...
          lookup,
          -- ...
        ) values (
          -- ...
          get_lookup( NEW.lookup ), -- normalize text as ID# instead
          -- ...
        )
      ];

something tells me that calling a pl/pgsql function in the
middle of an INSERT -- a function that might do a SELECT and an
INSERT of its own -- might somehow be A Bad Thing, because it
works like a charm.

bad dog?

--
I'd concentrate on "living in the now" because it is fun
and on building a better world because it is possible.
    - Tod Steward

will@serensoft.com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!