Thread: Setting global vars for use with triggers
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
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
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/