Thread: calling a shell script from pl/pgsql
How can I call a shell script from within a pl/pgsql function that is called as from a trigger. I do not want to interact with the script I just want it to run. I do want the trigger to wait for the script it called to complete before returning a value. Any ideas would be greatly appreciated. Thanks, Jeff Barrett
On Mon, 10 Sep 2001, Jeff Barrett wrote: > How can I call a shell script from within a pl/pgsql function that is called > as from a trigger. I do not want to interact with the script I just want it > to run. I do want the trigger to wait for the script it called to complete > before returning a value. > > Any ideas would be greatly appreciated. I don't think you can do that from within pl/pgsql. You'd probably need a function in pl/tclu or c.
* Stephan Szabo <sszabo@megazone23.bigpanda.com> [010910 12:37]: > On Mon, 10 Sep 2001, Jeff Barrett wrote: > > > How can I call a shell script from within a pl/pgsql function that is called > > as from a trigger. I do not want to interact with the script I just want it > > to run. I do want the trigger to wait for the script it called to complete > > before returning a value. > > > > Any ideas would be greatly appreciated. > > I don't think you can do that from within pl/pgsql. You'd probably need a > function in pl/tclu or c. You can also look at Peter Eisentraut's PL/sh, but there are portability issues with it: I can't seem to find the correct page at the moment, but it's out there. LER > > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: ler@lerctr.org US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749
On Mon, 10 Sep 2001, Stephan Szabo wrote: > On Mon, 10 Sep 2001, Jeff Barrett wrote: > > > How can I call a shell script from within a pl/pgsql function that is called > > as from a trigger. I do not want to interact with the script I just want it > > to run. I do want the trigger to wait for the script it called to complete > > before returning a value. > > > > Any ideas would be greatly appreciated. > > I don't think you can do that from within pl/pgsql. You'd probably need a > function in pl/tclu or c. Or pl/perlu! (Sorry, had to say it). Actually, I remember that Jan once mentioned something about pl/SH. I don't know what's the status of it?
On Mon, 10 Sep 2001, Alex Pilosov wrote: > On Mon, 10 Sep 2001, Stephan Szabo wrote: > > > On Mon, 10 Sep 2001, Jeff Barrett wrote: > > > > > How can I call a shell script from within a pl/pgsql function that is called > > > as from a trigger. I do not want to interact with the script I just want it > > > to run. I do want the trigger to wait for the script it called to complete > > > before returning a value. > > > > > > Any ideas would be greatly appreciated. > > > > I don't think you can do that from within pl/pgsql. You'd probably need a > > function in pl/tclu or c. > Or pl/perlu! (Sorry, had to say it). I thought perlu was added after 7.1? I considered mentioning it, but realized that it wouldn't help if it wasn't there yet.
On Mon, 10 Sep 2001, Stephan Szabo wrote: > > On Mon, 10 Sep 2001, Alex Pilosov wrote: > > > On Mon, 10 Sep 2001, Stephan Szabo wrote: > > > > > On Mon, 10 Sep 2001, Jeff Barrett wrote: > > > > > > > How can I call a shell script from within a pl/pgsql function that is called > > > > as from a trigger. I do not want to interact with the script I just want it > > > > to run. I do want the trigger to wait for the script it called to complete > > > > before returning a value. > > > > > > > > Any ideas would be greatly appreciated. > > > > > > I don't think you can do that from within pl/pgsql. You'd probably need a > > > function in pl/tclu or c. > > Or pl/perlu! (Sorry, had to say it). > > I thought perlu was added after 7.1? I considered mentioning it, but > realized that it wouldn't help if it wasn't there yet. Sorry, my fault, I thought pl/tclu is also a new language, but it apparently is in 7.1...Yes, for existing postgres, its pltclu or C, sorry :) -alex
Stephan Szabo wrote: > > On Mon, 10 Sep 2001, Jeff Barrett wrote: > > > How can I call a shell script from within a pl/pgsql function that is called > > as from a trigger. I do not want to interact with the script I just want it > > to run. I do want the trigger to wait for the script it called to complete > > before returning a value. > > > > Any ideas would be greatly appreciated. > > I don't think you can do that from within pl/pgsql. You'd probably need a > function in pl/tclu or c. > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org if its typical perl you should be able to use backticks my $system=`date`; $system should be set to the date or if you dont need any data back as you say then just system('date');
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). I cannot find the pl/sh module. The google links that came up brought me to pages that no longer exist on postgresql.org. I will have to look around some more. Thanks for the advice... great help! Jeff Barrett "Jeff Barrett" <jbarrett@familynetwork.com> wrote in message news:9nip2p$1s5o$1@news.tht.net... > How can I call a shell script from within a pl/pgsql function that is called > as from a trigger. I do not want to interact with the script I just want it > to run. I do want the trigger to wait for the script it called to complete > before returning a value. > > Any ideas would be greatly appreciated. > > Thanks, > > Jeff Barrett > >
* Jeff Barrett <jbarrett@familynetwork.com> [010910 14:48]: > 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). > > I cannot find the pl/sh module. The google links that came up brought me to > pages that no longer exist on postgresql.org. I will have to look around > some more. I've reported the missing pages to the postgresql.org webmaster. > > Thanks for the advice... great help! > > Jeff Barrett > > "Jeff Barrett" <jbarrett@familynetwork.com> wrote in message > news:9nip2p$1s5o$1@news.tht.net... > > How can I call a shell script from within a pl/pgsql function that is > called > > as from a trigger. I do not want to interact with the script I just want > it > > to run. I do want the trigger to wait for the script it called to complete > > before returning a value. > > > > Any ideas would be greatly appreciated. > > > > Thanks, > > > > Jeff Barrett > > > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html > -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: ler@lerctr.org US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749
Alex Pilosov writes: > Actually, I remember that Jan once mentioned something about pl/SH. I > don't know what's the status of it? http://webmail.postgresql.org/~petere/plsh.html It's a toy project of mine. It's usable, but there are probably some portability problems. -- Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter
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 partof 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 triggerto get fired. Thus, the database should look like the trigger never got fired - but how do you undo thechanges of your external program? It connected to the database, did it's updates, committed and terminated. You'llhave 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 intosome 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