Skip site navigation (1) Skip section navigation (2)

RULE vs TRIGGER

From: will trillich <will(at)serensoft(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: RULE vs TRIGGER
Date: 2001-07-30 06:05:36
Message-ID: 20010730010536.G23466@serensoft.com (view raw or flat)
Thread:
Lists: pgsql-general
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(at)serensoft(dot)com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!

Responses

pgsql-general by date

Next:From: Kovács PéterDate: 2001-07-30 07:12:04
Subject: RE: Large Objects
Previous:From: Ben-Nes MichaelDate: 2001-07-30 06:02:21
Subject: Re: readline and rh7.1

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group