Run external command as part of an sql statement ?

On Mon, May 7, 2018 at 2:35 PM, David Gauthier <davegauthierpg@gmail.com>
wrote:

Hi:

At the psql prompt, I can do something like...
"select \! id -nu"
...to get the uid of whoever's running psql.

I want to be able to run a shell command like this from within a stored
procedure. Is there a way to do this ?

​In core, you can probably use the untrusted​ version of Perl, Python, or
Tcl to accomplish your goal. SQL and pl/pgSQL do not provide that
capability.​

​David J.

"David G. Johnston" <david.g.johnston@gmail.com> writes:

On Mon, May 7, 2018 at 2:35 PM, David Gauthier <davegauthierpg@gmail.com>
wrote:

I want to be able to run a shell command like this from within a stored
procedure. Is there a way to do this ?

In core, you can probably use the untrusted​ version of Perl, Python, or
Tcl to accomplish your goal. SQL and pl/pgSQL do not provide that
capability.​

Depending on what you want to do, COPY TO/FROM PROGRAM might be a
serviceable option. But, just like the untrusted-PL variants, you
need to be superuser. Keep in mind that the program will run as
the database server owner (which is the reason for the superuser
restriction).

regards, tom lane

On 05/07/2018 02:35 PM, David Gauthier wrote:

Hi:

At the psql prompt, I can do something like...
   "select  \! id -nu"
...to get the uid of whoever's running psql.
I want to be able to run a shell command like this from within a stored
procedure.  Is there a way to do this ?

PL/sh?:

https://github.com/petere/plsh

CREATE OR REPLACE FUNCTION id() RETURNS text AS '
#!/bin/sh
id -nu
' LANGUAGE plsh;

aklaver@tito:~> psql -d test -U postgres

test=# select * from id();
id
----------
postgres

It is an untrusted language so you will need to run as a superuser.

Thanks

--
Adrian Klaver
adrian.klaver@aklaver.com