Repository files navigation

PL/sh is a procedural language handler for PostgreSQL that allows youto write stored procedures in a shell of your choice. For example,

CREATE FUNCTION concat(text, text) RETURNS text AS '#!/bin/shecho "$1$2"' LANGUAGE plsh;

The first line must be a#!-style line that indicates the shell touse. The rest of the function body will be executed by that shell ina separate process. The arguments are available as$1,$2, etc.,as usual. (This is the shell's syntax. If your shell uses somethingdifferent then that's what you need to use.) The return value willbecome what is printed to the standard output, with a newlinestripped. If nothing is printed, a null value is returned. Ifanything is printed to the standard error, then the function abortswith an error and the message is printed. If the script does not exitwith status 0 then an error is raised as well.

The shell script can do anything you want, but you can't access thedatabase directly. Trigger functions are also possible, but theycan't change the rows. Needless to say, this language should not bedeclared asTRUSTED.

The distribution also contains a test suite in the directorytest/,which contains a simplistic demonstration of the functionality.

I'm interested if anyone is using this.

Peter Eisentrautpeter@eisentraut.org

You can't access the database directly from PL/sh through somethinglike SPI, but PL/sh sets up libpq environment variables so that youcan easily callpsql back into the same database, for example

CREATE FUNCTION query (x int) RETURNS textLANGUAGE plshAS $$#!/bin/shpsql -At -c "select b from pbar where a = $1"$$;

Note: The "bin" directory is prepended to the path, but only if thePATH environment variable is already set.

In a trigger procedure, trigger data is available to the scriptthrough environment variables (analogous to PL/pgSQL):

• PLSH_TG_NAME: trigger name
• PLSH_TG_WHEN:BEFORE,INSTEAD OF, orAFTER
• PLSH_TG_LEVEL:ROW orSTATEMENT
• PLSH_TG_OP:DELETE,INSERT,UPDATE, orTRUNCATE
• PLSH_TG_TABLE_NAME: name of the table the trigger is acting on
• PLSH_TG_TABLE_SCHEMA: schema name of the table the trigger is acting on

In an event trigger procedure, the event trigger data is available tothe script through the following environment variables:

• PLSH_TG_EVENT: event name
• PLSH_TG_TAG: command tag

PL/sh supports theDO command. For example:

DO E'#!/bin/sh\nrm -f /tmp/file' LANGUAGE plsh;

You need to have PostgreSQL 8.4 or later, and you need to have theserver include files installed.

To build and install PL/sh, use this procedure:

makemake install

The include files are found using thepg_config program that isincluded in the PostgreSQL installation. To use a differentPostgreSQL installation, point configure to a differentpg_config likeso:

make PG_CONFIG=/else/where/pg_configmake install PG_CONFIG=/else/where/pg_config

Note that generally server-side modules such as this one have to berecompiled for every major PostgreSQL version (that is, 8.4, 9.0,...).

To declare the language in a database, use the extension system withPostgreSQL version 9.1 or later. Run

CREATE EXTENSION plsh;

inside the database of choice. To upgrade from a previousinstallation that doesn't use the extension system, use

CREATE EXTENSION plsh FROM unpackaged;

UseDROP EXTENSION to remove it.

With versions prior to PostgreSQL 9.1, use

psql -d DBNAME -f .../share/contrib/plsh.sql

with a server running. To drop it, usedroplang plsh, orDROP FUNCTION plsh_handler(); DROP LANGUAGE plsh; if you want to do itmanually.

To run the test suite, execute

make installcheck

after installation.