Thread: Email and Postgres

Hi all,
Does anyone know if you can send email using pgsql and triggers or some
other way?
Thanks

I don't think so (when I looked anyway), but I hacked the PHP mail
function and compiled it as a user function... It doesn't do any checking
(never got around to it) but it works.  Someday I'll do that...

The first comment is what I used to compiled it... you'll need to tweak
some of the options to point to the right place.  I don't remember the
exact syntax to add it to the database, but it's in the manual and was
easy...  you might also needto change the path to sendmail...

--------------------------
/*
gcc -I/local/src/postgresql-7.0/src/include -I/local/src/postgresql-7.0/src/backend   -O2 -m486 -pipe -Wall
-Wmissing-prototypes-Wmissing-declarations -I/local/src/postgresql-7.0/src/interfaces/libpq
-I/local/src/postgresql-7.0/src/include-fpic -DPIC   -c -o send_email.o send_email.c 
ld -x -shared -o send_email.so send_email.o
rm send_email.o
*/


#include <stdio.h>
#include "postgres.h"
#include "utils/builtins.h"

int send_email(text *from, text *to, text *subject, text *body)  {
    int success;
    FILE *fd;

    fd = popen("/usr/sbin/sendmail -oi -t", "w");

    if( fd )  {
        fprintf(fd, "From: %s\n", textout(from));
        fprintf(fd, "To: %s\n", textout(to));
        fprintf(fd, "Subject: %s\n", textout(subject));
        fprintf(fd, "\n");
        fprintf(fd, "%s", textout(body));

        success = pclose(fd);
    }else {
        success = 0;
    }

    return(success);
}

On Mon, 6 Nov 2000, Ian Kulmatycki wrote:

> Hi all,
> Does anyone know if you can send email using pgsql and triggers or some
> other way?
> Thanks
>

On Mon, 6 Nov 2000, Ian Kulmatycki wrote:

> Hi all,
> Does anyone know if you can send email using pgsql and triggers or some
> other way?
> Thanks

Some other way:  I don't use triggers much.  My interface does the
work...so:

I have perl do it.  Using the binding operator ( =~ ) I search a
text str for a regex.  If found the perl script looks up a list of
addresses in a db relation and sends them mail.   If you want a
look at my src say the word.

Hmm...thinking on this abit:  you could insert into a table (named
shell or whatever) a unix command.  Then, when your expr evals to
true you could do a system() call...at least in C or perl...
just thinking aloud!

Good hunting,
Tom

--------------------------------------------------------------------
               SVCMC - Center for Behavioral Health
--------------------------------------------------------------------
Thomas Good                          tomg@ { admin | q8 } .nrnet.org
IS Coordinator / DBA                 Phone: 718-354-5528
                                     Fax:   718-354-5056
--------------------------------------------------------------------
Powered by:  PostgreSQL     s l a c k w a r e          FreeBSD:
               RDBMS       |---------- linux      The Power To Serve
--------------------------------------------------------------------