From: | nolan(at)celery(dot)tssi(dot)com |
---|---|
To: | Paul(dot)Fontenot(at)bannerhealth(dot)com (Fontenot, Paul) |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: PL/pgSQL manual |
Date: | 2003-05-14 18:18:09 |
Message-ID: | 20030514181809.26940.qmail@celery.tssi.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
> 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
From | Date | Subject | |
---|---|---|---|
Next Message | Fontenot, Paul | 2003-05-14 19:18:32 | Password changes |
Previous Message | Fontenot, Paul | 2003-05-14 18:00:28 | PL/pgSQL manual |