Re: Executing External Programs - Mailing list pgsql-general

> 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);
}