Executing Shell Command

Wow, you got me on this one. You checked the permissions, and even
tried a hard-coded string. Have you looked in the server logs to see if
it has any more information?

---------------------------------------------------------------------------

Nicholas Walker wrote:

I have been trying to execute a shell command from within postgresql

My C Function

#include <stdlib.h>
#include<postgres.h>

void shell_exec(text arg)
{
system(arg.vl_dat);
}

The function in postgres

CREATE FUNCTION xp_shellexec(text) RETURNS void
AS '/home/psql/cfuncs/shellexec', 'shell_exec'
LANGUAGE C STRICT;

Then run SELECT xp_shellexec('mkdir /home/psql/testing')

No error message, it runs, but the directory isn't created.

The C function works if I compile it, and run myself from the shell,
And I even just tried doing this:
void shell_exec(text arg)
{
system("mkdir /home/psql/testing");
}

and it still didn't work.
the directory has rwx permissions for everyone
I am running freebsd 5.1, and the most recent version of postgres

Can someone give me some help?

Thanks

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Nicholas Walker wrote:

I have been trying to execute a shell command from within postgresql

You ought to be using Version 1 calling conventions -- see:
http://www.postgresql.org/docs/current/static/xfunc-c.html#AEN29226

The following works fine for me on RH9:

#define GET_STR(textp) \
DatumGetCString(DirectFunctionCall1(textout, PointerGetDatum(textp)))
extern Datum shell_exec(PG_FUNCTION_ARGS);

PG_FUNCTION_INFO_V1(shell_exec);
Datum
shell_exec(PG_FUNCTION_ARGS)
{
char *cmd = GET_STR(PG_GETARG_TEXT_P(0));
int32 result;
result = system(cmd);
PG_RETURN_INT32(result);
}

CREATE OR REPLACE FUNCTION xp_shellexec(text)
RETURNS int
AS '$libdir/shell_exec','shell_exec'
LANGUAGE 'C' VOLATILE STRICT;

SELECT xp_shellexec('mkdir /tmp/testing123');

[root@dev tmp]# ls -ld /tmp/test*
drwx------ 2 postgres postgres 4096 Nov 28 19:31 /tmp/testing123

HTH,

Joe