How can I call a shell command from within a pl/pgsql function?

Question

I would like to do something like this, but it doesn't work...

CREATE FUNCTION kill(integer) RETURNS integer AS $$
DECLARE
    pid ALIAS FOR $2;
BEGIN
    -- Do some stuff here...

    return kill TERM, pid;
END;
$$ LANGUAGE plpgsql;

Postgresql 8.3

UPGRADING is not an option, sorry...

For others landing on this page who are using PostgreSQL 9.3 or above, see dba.stackexchange.com/a/128254/104401 — Wildcard
– Wildcard, Commented Nov 23, 2017 at 9:32

bma · Accepted Answer · 2013-08-16 22:09:14Z

2

What are you trying to kill? A query or a backend? If only a query, pg_cancel_backend() existed in Postgresql 8.3. If you really need to execute a shell command, you'll need to use one of the "untrusted" procedural languages like plperlu or plpythonu etc. See http://www.postgresql.org/docs/8.3/static/plperl-trusted.html for more details.

edited Aug 16, 2013 at 22:09

answered Aug 16, 2013 at 16:51

bma

9,8562 gold badges35 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Craig Ringer Over a year ago

pg_terminate_backend didn't, though, and that's probably what they really want.

Craig Ringer · Accepted Answer · 2013-08-17 16:43:57Z

1

You can't do it in PL/PgSQL, but you can in most of the other PLs:

CREATE LANGUAGE plperlu;

CREATE OR REPLACE FUNCTION signal(signum integer, pid integer) RETURNS integer AS $$
return kill $_[0], $_[1];
$$ LANGUAGE plperlu;

or use PL/Python if you prefer. Or even PL/TCL.

Alternately, write a simple C extension to expose the kill function to SQL. It'll be quite trivial to adapt the examples in the documentation for the purpose.

answered Aug 17, 2013 at 16:43

Craig Ringer

329k84 gold badges743 silver badges821 bronze badges

2 Comments

Stephan Over a year ago

Do you need super user access to run the code in your answer ?

Craig Ringer Over a year ago

@Alex Of course. If there was any built-in way to signal a process without having superuser access that'd be monsterous security hole. If you want to add that in your database you can make the function SECURITY DEFINER, REVOKE access to it from PUBLIC, and GRANT rights to only the user(s) or role(s) you want to be able to send signals.

Collectives™ on Stack Overflow

How can I call a shell command from within a pl/pgsql function?

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related