Can a PostgreSQL stored procedure call an external program?

Question

I'm looking through the PostgreSQL "Server Programming" documentation for writing stored procedures. I'd like to have a stored procedure call an existing external shell script to perform a task, and then return the results.

I haven't found this in the documentation. Is this supported?

Answer 1

"untrusted" PLs like PL/perlu or PL/Pythonu can, as can C user-defined functions.

It's not usually a great idea though. If you call an external program and then rollback the transaction, the external program won't know about the rollback. You're breaking out of transaction management.

It is typically better to use NOTIFY to send work to a daemon that is connected and LISTENing for events.

Answer 2

Here is how I did it on Ubuntu. Note that plperl is not installed by default when you install PostgreSQL.

> sudo apt-get install postgresql-plperl
> sudo --user=postgres psql my_database

# CREATE EXTENSION IF NOT EXISTS plperlu;
# CREATE OR REPLACE FUNCTION foo() RETURNS TEXT AS $$
    return `/bin/echo -n "hello world!"`;
    $$ LANGUAGE plperlu;

# select foo();
     foo
-------------
 hello world!
(1 row)