plsh

plsh : PL/sh procedural language

Overview

Packages

Source

Install

Make sure PGDG repo available:

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

Install this extension with pig:

pig install plsh;		# install via package name, for the active PG version

pig install plsh -v 18;   # install for PG 18
pig install plsh -v 17;   # install for PG 17
pig install plsh -v 16;   # install for PG 16
pig install plsh -v 15;   # install for PG 15
pig install plsh -v 14;   # install for PG 14

Create this extension with:

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.