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...

Answer 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.

Answer 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.