> I'm looking for an example of a function that will prevent an insert
> based on a pattern match. Is this possible and if so DO I need to return
> something? I guess to sum to it up, I need a function that will match a
> pattern and then a trigger that will not allow an insert based on what
> that function matches.
As of 7.3, a trigger function should always 'return trigger'.
> I'm sure I am just making this harder than it really is...
It took me several months to learn how to write robust triggers when I
was learning Oracle in 1993/4, and there was a lot less material available
on how to do that then.
Presumably you have a testbed pgsql setup you can use to practice on.
Here's a trigger function I wrote when I was evaluating pgsql, it
uses the 'raise exception' call to disallow an update based on
the existing data value of a column, which is what you appear to be
struggling with.
create or replace function test_trigger() returns trigger
security invoker
as '
DECLARE
this_user varchar(20);
BEGIN
select into this_user current_user;
if TG_OP = ''UPDATE'' then
if OLD.name != NEW.name then
perform test_logwrite (NEW.id,this_user,''name'',
OLD.name, NEW.name, TG_WHEN || TG_OP);
end if;
end if;
if TG_OP = ''DELETE'' then
if OLD.name = ''Mike Nolan'' then
raise exception ''Cannot Delete This Record'';
end if;
perform test_logwrite (OLD.id,this_user,''name'',
OLD.name,null, TG_WHEN || TG_OP);
end if;
if TG_OP = ''INSERT'' then
perform test_logwrite (NEW.id,this_user,''name'',
null,NEW.name, TGWHEN || TG_OP);
end if;
return null;
END;
' language 'plpgsql';
--
Mike Nolan
