Thread: Functions and triggers

Hopefully I can do what I want with a combination of functions and
triggers, the drawback being that I am coming from several years of
MySQL and know nothing about PostgreSQL functions or triggers. I
currently use msyslogd on linux to log syslog messages to a postgresql
database. I want to be able to do things - like send an email, etc... -
when certain messages are inserted into the database.

I have this for the table:

syslog=> \d syslogtb
               Table "syslogtb"
  Column  |          Type          | Modifiers
----------+------------------------+-----------
 facility | character(10)          |
 priority | character(10)          |
 date     | date                   |
 time     | time without time zone |
 host     | character varying(128) |
 message  | text                   |

syslog=>

And this is an example of the message field data:

%PIX-3-106011: Deny inbound (No xlate) udp src inside:10.10.1.160/1035
dst inside:10.29.0.45/161

How would I create and use a function/trigger to key on the PIX error
message code (%PIX-3-106011)?

Thanks in advance for your help

-Paul

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

> And this is an example of the message field data:
>
> %PIX-3-106011: Deny inbound (No xlate) udp src inside:10.10.1.160/1035
> dst inside:10.29.0.45/161
>
> How would I create and use a function/trigger to key on the PIX error
> message code (%PIX-3-106011)?

You can write a triggered procedure on insert and check the error message
code within that procedure. Check the documentation on howto use triggered
procedure.

Moreover, you can use pgmail to email the details directly from the
triggered procedure. For details of pgmail, please refer
http://pgmail.sourceforge.net .

regards,
bhuvaneswaran