Tables and functions and triggers oh my...

Поиск
Список
Период
Сортировка
От Fontenot, Paul
Тема Tables and functions and triggers oh my...
Дата
Msg-id 230BCAB977308F43BD851AFAE55A86413D6CC6@phx01018.bannerhealth.com
обсуждение исходный текст
Ответы Re: Tables and functions and triggers oh my...  (Joe Conway <mail@joeconway.com>)
Список pgsql-novice
I'm going nuts, when I have this table/function/trigger combo my table
never updates - works great as soon as I drop the trigger. Can someone
not so green in PostgreSQL take a gander and tell me what I've done
wrong?

CREATE TABLE syslogTB (
    facility char(10),
      priority char(10),
      date date,
      time time,
      host varchar(128),
      message  text
);

create function PIXMail() returns opaque as '
DECLARE
        logRec RECORD;
        textMessage text;
BEGIN
        select into logRec date,time,message from syslogtb where message
= NEW.message;
        if logRec.message = NEW.message then
                textMessage := ''The PIX has generated the following: ''
logRec.message '''';
                perform pgmail(''PIX <some_email_addy>'',''Paul Fontenot
<some_email_addy>'',''PIX Message'', t
extMessage);
        end if;
        return NEW;
END;' language 'plpgsql';

CREATE TRIGGER trgPIXMail
        BEFORE INSERT OR UPDATE ON syslogtb FOR EACH ROW
        EXECUTE PROCEDURE pixmail();

 ***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.

В списке pgsql-novice по дате отправления:

Предыдущее
От: Josh Berkus
Дата:
Сообщение: Re: 7.2.1 => 7.3.2
Следующее
От: Oliver Elphick
Дата:
Сообщение: Re: 7.2.1 => 7.3.2