Received: from smtp012.mail.yahoo.com (smtp012.mail.yahoo.com [216.136.173.32]) by postgresql.org (8.11.3/8.11.4) with SMTP id f8B7EZ716206 for ; Tue, 11 Sep 2001 03:14:38 -0400 (EDT) (envelope-from janwieck@yahoo.com) Received: from pool-63.49.143.207.bltm.grid.net (HELO saturn.jw.home) (63.49.143.207) by smtp.mail.vip.sc5.yahoo.com with SMTP; 11 Sep 2001 07:14:30 -0000 X-Apparently-From: Received: (from wieck@localhost) by saturn.jw.home (8.11.2/8.11.2) id f8B78VV01263; Tue, 11 Sep 2001 03:08:31 -0400 From: Jan Wieck Message-Id: <200109110708.f8B78VV01263@saturn.jw.home> Subject: Re: calling a shell script from pl/pgsql In-Reply-To: <9nj3od$1u4c$1@news.tht.net> from Jeff Barrett at "Sep 10, 2001 03:15:50 pm" To: Jeff Barrett Date: Tue, 11 Sep 2001 03:08:31 -0400 (EDT) CC: pgsql-sql@postgresql.org X-Mailer: ELM [version 2.4ME+ PL68 (25)] MIME-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-Archive-Number: 200109/48 X-Sequence-Number: 4694 Jeff Barrett wrote: > Thanks for the suggestions. > > I am running 7.1.2 and going to 7.1.3 soon. > > If I use pl/tclu or pl/perlu I can call this executable from within the > code? > > I have a signifigant limitation, I cannot duplicate the action of the > programs I want to call in a program I write within postgres, I need to call > the executable (In this one case it is a shell script but I have others > where it is a binary). That's kind of a fuzzy explanation. If this "action" of your programs in any way affects data, that should be part of the triggers transaction, then what you want to do is broken by design. In detail, if the external program you're calling from the trigger connects to the database, updates it and terminates, the transaction that fired the trigger can still rollback, discarding all changes that caused the trigger to get fired. Thus, the database should look like the trigger never got fired - but how do you undo the changes of your external program? It connected to the database, did it's updates, committed and terminated. You'll have no chance to undo that. So if you really intend to do what it smells like, you're better off moving all the "external program"s logic into some function inside of the database, and call it from the external program as well as the trigger. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com # _________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com