Обсуждение: PL/pgSQL manual

Sorry for the last post, finger error.

Anyways, I find all types of example like this:

CREATE FUNCTION somefunc() RETURNS INTEGER AS

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.

I'm sure I am just making this harder than it really is...

 ***PRIVILEGED & CONFIDENTIAL***
Unless expressly stated otherwise, this message (and any attachment(s)
thereto) is confidential and may be privileged. It is intended for the
addressee(s) only. If you are not an addressee, any disclosure or
copying of the contents of this e-mail or any action taken (or not
taken) in reliance on it is strictly prohibited. If you are not an
addressee, please inform sender immediately and delete this message from
your system.

> 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