Re: a primer on trigger?

On Thu, May 03, 2001 at 10:00:38PM -0400, newsreader@mediaone.net
wrote:
> What I want to do is "trigger" a system call when a table is
> modified in any way.  But it appears that trigger idea in
> postgres is not capable.  Can any one confirm if that is the
> case?
>
> What I want to do is run an external perl script which needs
> the newly modified row as input.
>
> My web server modifies the database which is on a different
> physical machine.  Currently I'm doing it via a complicated
> mechanism of sending an email to an account at database
> machine, catching it using procmail and then perl script
> performing the action.  Perl script needs the newly modified
> row as input and then it makes system call.

wow. convoluted. it must've taken some doing!

postgresql servers can accept tcp connections on certain ports,
just as a web server listens to port 80. so you can have a script
written on perl on this.machine.over.here that inserts and
selects and updates on a database located on that.box.there
with a minimum of fuss.

and then you'd already have the data in a perl script, soyou'd be
off to the races.

look in /etc/postgresql/* and in /usr/share/doc/postgresql-doc/*
for how to enable tcp connections, and make sure your postgresql
user accounts (in pg_shadow) are comfortably secure, and then
from another machine instead of

    use DBI;
    my $DSN = 'dbi:Pg:dbname=lotsofdata';
    my $USER = 'onlyme';
    my $PASS = 'stupidity';
    my $dbh=DBI->connect($DSN,$USER,$PASS);

you'd change this, and you're done:

    my $DSN = 'dbi:Pg:dbname=lotsofdata;host=ip.address.over.there';

see "man DBD::Pg" for the full poop.

    dbi:Pg:dbname=$dbname;host=$host;port=$port;options=$options;tty=$tty

if you have perl doing the inserts to begin with, then you've
already got the information where you need it. have perl massage
or blend it, whatever pickles your tink.

--
don't visit this page. it's bad for you. take my expert word for it.
http://www.salon.com/people/col/pagl/2001/03/21/spring/index1.html

will@serensoft.com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!