i've heard that TRIGGERs are the best solution for "if it
doesn't exist, insert it; now, select it anyway."
but here's what i use instead -- RULE with FUNCTION. i'd love to
hear the skinny on why this 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!
