Thread: Executing External Programs

Is there a way (maybe creating a function) to execute an external program from within postgre with a query?

Thanks

Andrew

On Wed, May 14, 2003 at 10:06:13 -0500,
  Andrew Brink <abrink@netstandard.net> wrote:
>
>
> Is there a way (maybe creating a function) to execute an external program from within postgre with a query?

You can do this in c and probably untrusted perl.
It may not be the best way to handle things depending on what you are
doing. This is especially true if you were planning on waiting for the
program to complete before returning from the function.
In many cases having a program poll (or run from cron) and check for
work to do is a better way to do this.

Yes there is, but it should be "C" function I think and you have to write it
yourself.
There is also pl/sh procedural language (I do not remember where I get the
sources -sourceforge maybe ???) that alows to embed functionality of sh
within postgres procedural language.


----- Original Message -----
From: "Andrew Brink" <abrink@netstandard.net>
To: <pgsql-general@postgresql.org>
Sent: Wednesday, May 14, 2003 5:06 PM
Subject: [GENERAL] Executing External Programs




Is there a way (maybe creating a function) to execute an external program
from within postgre with a query?

Thanks

Andrew

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Andrew Brink wrote:
>
> Is there a way (maybe creating a function) to execute an external program from within postgre with a query?

Yes, there are ways doing so with PL/TclU, PL/PerlU, PL/sh and last but
not least C.

No, you should not do it unless you know about all the consequences and
sign the void product warranty agreement on the backside of this article:

     http://archives.postgresql.org/pgsql-sql/2001-09/msg00051.php


Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1



> Is there a way (maybe creating a function) to execute an external program
> from within postgre with a query?

You will need to create a function written in a procedural language. The best
ones to do this is probably PL/Tcl or PL/Perl. Both come bundled with Postgres,
and are fairly easy to learn and use, especially if you have tcl or perl experience.

Information on installing languages:
http://www.postgresql.org/docs/view.php?version=7.3&file=xplang-install.html

Information on PL/Tcl:
http://www.postgresql.org/docs/view.php?version=7.3&file=pltcl.html
(you probably want "untrusted": PL/TclU)

Information on PL/Perl:
http://www.postgresql.org/docs/view.php?version=7.3&file=plperl.html

Again, you may need the "untrusted" version, PL/PerlU:
http://www.postgresql.org/docs/view.php?version=7.3&file=plperl-trusted.html


- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200305200935
-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html

iD8DBQE+yjKQvJuQZxSWSsgRAo1WAKCw9UyE5lrqAnm+erspeRmr5BDwcwCg/PTP
uet5zwqP1/ZTvN1+8za5SNI=
=tc3u
-----END PGP SIGNATURE-----

> Is there a way (maybe creating a function) to execute an external program
> from within postgre with a query?

Yes there is. This C function executes a command with the system  function,
system waits for your command to complete and then returns the exit status.
Rember your command will run with postgres effective UID and GID.

When you've compiled and loaded it, try something like :

SELECT sys_ex('echo hello >> /usr/local/pgsql/testfile');

Regards, Tor.

#include "postgres.h"
#include "fmgr.h"
#include <string.h>
#include "executor/spi.h"
#include "utils/elog.h"

extern Datum sys_ex(PG_FUNCTION_ARGS);
PG_FUNCTION_INFO_V1(sys_ex);
Datum
sys_ex(PG_FUNCTION_ARGS)
{
        char       *command;
        int32      returns;
        char       *wrn;
        char       *say = "Executes command : ";

        command = (char *) DatumGetCString(DirectFunctionCall1(textout,
PointerGetDatum(PG_GETARG_TEXT_P(0))));

        wrn = (char *) palloc (sizeof(say) + sizeof(command));
        strcpy(wrn,say);
        strcat(wrn,command);
        elog(NOTICE, wrn);
        returns = (int32) system(command);
        if (returns != 0 )
        {
        elog(NOTICE,"EXTERNAL COMMAND FAILED!");
        }
PG_RETURN_INT32(returns);
}