Thread: Setting global vars for use with triggers

Setting global vars for use with triggers

From
CSN
Date:
I'd like to create a trigger which deletes associated
files whenever their corresponding row is deleted.
Problem is, I don't want to hard-code directory
locations in the trigger function. Is there a way to
set a DOCUMENT_ROOT-like variable in Postgresql which
triggers could access and use? I'm using PHP for the
trigger (and PG 8.x).

Thanks,
CSN

PS - Scott and I have been tossing around a few other
ideas here:
http://www.phpbuilder.com/board/showthread.php?s=&threadid=10302693




____________________________________________________
Yahoo! Sports
Rekindle the Rivalries. Sign up for Fantasy Football
http://football.fantasysports.yahoo.com

Re: Setting global vars for use with triggers

From
Douglas McNaught
Date:
CSN <cool_screen_name90001@yahoo.com> writes:

> I'd like to create a trigger which deletes associated
> files whenever their corresponding row is deleted.
> Problem is, I don't want to hard-code directory
> locations in the trigger function. Is there a way to
> set a DOCUMENT_ROOT-like variable in Postgresql which
> triggers could access and use? I'm using PHP for the
> trigger (and PG 8.x).

It's probably best to keep it in a one-row table--that way it'll be
saved when you back up the database, and you can change it with
standard SQL.

-Doug

Re: Setting global vars for use with triggers

From
Michael Fuhr
Date:
On Wed, Jun 22, 2005 at 01:36:20PM -0700, CSN wrote:
>
> I'd like to create a trigger which deletes associated
> files whenever their corresponding row is deleted.

I looked at the thread you posted and saw that you were considering
using LISTEN/NOTIFY.  That's probably a good idea because if you
delete a file in a trigger, you don't have a way to undelete it if
the transaction rolls back.  If you use NOTIFY then the notification
should be delivered to the listener only if the transaction commits.

Ideally the listener wouldn't repeatedly sleep and check for
notifications -- you'd use a function like select() or poll() in C
(socket_select() in PHP) that would tell you immediately when data
is available on the database connection's socket (and only then).
Unfortunately I don't see a way for the PHP PostgreSQL interface
to get the connection's file descriptor as you would in C with
PQsocket().  If you use PHP then you might be stuck with sleep/check,
so you might want to consider writing the listener in another
language.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/