plsh

Overview

Version

Install

You can install plsh directly. First, make sure the PGDG repository is added and enabled:

pig repo add pgdg -u          # Add PGDG repo and update cache

Install the extension using pig or apt/yum/dnf:

pig install plsh;          # Install for current active PG version

pig ext install -y plsh -v 18  # PG 18
pig ext install -y plsh -v 17  # PG 17
pig ext install -y plsh -v 16  # PG 16
pig ext install -y plsh -v 15  # PG 15
pig ext install -y plsh -v 14  # PG 14

dnf install -y plsh_18       # PG 18
dnf install -y plsh_17       # PG 17
dnf install -y plsh_16       # PG 16
dnf install -y plsh_15       # PG 15
dnf install -y plsh_14       # PG 14

apt install -y postgresql-18-plsh   # PG 18
apt install -y postgresql-17-plsh   # PG 17
apt install -y postgresql-16-plsh   # PG 16
apt install -y postgresql-15-plsh   # PG 15
apt install -y postgresql-14-plsh   # PG 14

Create Extension:

CREATE EXTENSION plsh;

Usage

plsh: PL/sh procedural language

plsh allows writing PostgreSQL functions as shell scripts. The function body must start with a shebang line specifying the interpreter.

CREATE EXTENSION plsh;

Create Shell Functions

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

SELECT concat('hello ', 'world');  -- 'hello world'

Argument Passing

Function arguments are passed as positional shell variables ($1, $2, etc.):

CREATE FUNCTION file_line_count(filename text) RETURNS int AS '
#!/bin/sh
wc -l < "$1"
' LANGUAGE plsh;

Return Values

• Standard output becomes the return value (trailing newlines stripped)
• Empty output returns NULL
• Standard error output causes the function to abort with that error message
• Non-zero exit status triggers an error

CREATE FUNCTION system_uptime() RETURNS text AS '
#!/bin/sh
uptime
' LANGUAGE plsh;

Database Access

Direct SPI access is not available, but psql can be used since libpq environment variables are preconfigured:

CREATE FUNCTION query_db(x int) RETURNS text AS $$
#!/bin/sh
psql -At -c "SELECT name FROM users WHERE id = $1"
$$  LANGUAGE plsh;

Trigger Functions

Trigger context is available via environment variables:

Event trigger variables: PLSH_TG_EVENT, PLSH_TG_TAG.

Inline Execution

DO E'#!/bin/sh\necho "running shell command"' LANGUAGE plsh;

Security

plsh should not be declared as TRUSTED since shell scripts have full OS-level access under the PostgreSQL user. Only superusers should create plsh functions.