X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 7691B3A4AEC for ; Sun, 23 Jan 2005 18:47:14 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 20664-04 for ; Sun, 23 Jan 2005 18:47:11 +0000 (GMT) Received: from out002.verizon.net (out002pub.verizon.net [206.46.170.141]) by svr1.postgresql.org (Postfix) with ESMTP id 300B13A2CC8 for ; Sun, 23 Jan 2005 18:47:11 +0000 (GMT) Received: from RavChaim.rishonim.com ([68.237.193.32]) by out002.verizon.net (InterMail vM.5.01.06.06 201-253-122-130-106-20030910) with ESMTP id <20050123184710.TMUP12052.out002.verizon.net@RavChaim.rishonim.com> for ; Sun, 23 Jan 2005 12:47:10 -0600 From: Mitchell Laks To: pgsql-admin@postgresql.org Subject: how to make a trigger to copy files. Date: Sun, 23 Jan 2005 13:47:53 -0500 User-Agent: KMail/1.7 MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200501231347.53304.mlaks@verizon.net> X-Authentication-Info: Submitted using SMTP AUTH at out002.verizon.net from [68.237.193.32] at Sun, 23 Jan 2005 12:47:10 -0600 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.44 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE, DNS_FROM_RFC_POST, FORGED_RCVD_HELO X-Spam-Level: * X-Archive-Number: 200501/238 X-Sequence-Number: 16097 Hi. I have a postgresql database backed application. As soon as the application stores a file on my server, it creates a entry in a table LOCATION_TABLE which stores the file location in the table in my postgresql database (eg. in LOCATION_TABLE in database MASTER). I have read about triggers and wondered if I could use them. I would like my server to immediately "scp" the file to another machine, as soon as the application updates the table with the new entry. (right now i have a cron job that runs a perl script that queries the database for new entries every 5 minutes, but i would like to have the new file (whose location is stored in the new LOCATION_TABLE entry) to be "scp"'d immediately, not after 5 minutes. Also a trigger seems more elegant... But when i look for sample triggers on the web they seem to only do things internal to the database (change field entries etc, check them for business logic rules etc). I am running postgresql 7.4.6 on debian sarge. I am most familiar with perl. Could i create a database trigger to run a script like system("scp $filename $destination"); where $filename is extracted from the entry just made into the database, and $destination is my other pc? Thanks, Mitchell Laks