Re: PL/pgSQL manual

> 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