1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
|
PL/sh Procedural Language Handler for PostgreSQL
================================================
PL/sh is a procedural language handler for PostgreSQL that allows you
to write stored procedures in a shell of your choice. For example,
CREATE FUNCTION concat(text, text) RETURNS text AS '
#!/bin/sh
echo "$1$2"
' LANGUAGE plsh;
The first line must be a `#!`-style line that indicates the shell to
use. The rest of the function body will be executed by that shell in
a separate process. The arguments are available as `$1`, `$2`, etc.,
as usual. (This is the shell's syntax. If your shell uses something
different then that's what you need to use.) The return value will
become what is printed to the standard output, with a newline
stripped. If nothing is printed, a null value is returned. If
anything is printed to the standard error, then the function aborts
with an error and the message is printed. If the script does not exit
with status 0 then an error is raised as well.
The shell script can do anything you want, but you can't access the
database directly. Trigger functions are also possible, but they
can't change the rows. Needless to say, this language should not be
declared as `TRUSTED`.
The distribution also contains a test suite in the directory `test/`,
which contains a simplistic demonstration of the functionality.
I'm interested if anyone is using this.
Peter Eisentraut <peter@eisentraut.org>
Database Access
---------------
You can't access the database directly from PL/sh through something
like SPI, but PL/sh sets up libpq environment variables so that you
can easily call `psql` back into the same database, for example
CREATE FUNCTION query (x int) RETURNS text
LANGUAGE plsh
AS $$
#!/bin/sh
psql -At -c "select b from pbar where a = $1"
$$;
Note: The "bin" directory is prepended to the path, but only if the `PATH` environment variable is already set.
Triggers
--------
In a trigger procedure, trigger data is available to the script
through environment variables (analogous to PL/pgSQL):
* `PLSH_TG_NAME`: trigger name
* `PLSH_TG_WHEN`: `BEFORE`, `INSTEAD OF`, or `AFTER`
* `PLSH_TG_LEVEL`: `ROW` or `STATEMENT`
* `PLSH_TG_OP`: `DELETE`, `INSERT`, `UPDATE`, or `TRUNCATE`
* `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
Event Triggers
--------------
In an event trigger procedure, the event trigger data is available to
the script through the following environment variables:
* `PLSH_TG_EVENT`: event name
* `PLSH_TG_TAG`: command tag
Inline Handler
--------------
PL/sh supports the `DO` command. For example:
DO E'#!/bin/sh\nrm -f /tmp/file' LANGUAGE plsh;
Installation
------------
You need to have PostgreSQL 8.4 or later, and you need to have the
server include files installed.
To build and install PL/sh, use this procedure:
make
make install
The include files are found using the `pg_config` program that is
included in the PostgreSQL installation. To use a different
PostgreSQL installation, point configure to a different `pg_config` like
so:
make PG_CONFIG=/else/where/pg_config
make install PG_CONFIG=/else/where/pg_config
Note that generally server-side modules such as this one have to be
recompiled for every major PostgreSQL version (that is, 8.4, 9.0,
...).
To declare the language in a database, use the extension system with
PostgreSQL version 9.1 or later. Run
CREATE EXTENSION plsh;
inside the database of choice. To upgrade from a previous
installation that doesn't use the extension system, use
CREATE EXTENSION plsh FROM unpackaged;
Use `DROP 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, use `droplang plsh`, or `DROP
FUNCTION plsh_handler(); DROP LANGUAGE plsh;` if you want to do it
manually.
Test suite
----------
[](http://travis-ci.org/petere/plsh)
To run the test suite, execute
make installcheck
after installation.
|
