Contents
plx User Guide
This guide shows how to write functions in the plx dialects. Each example is taken from the PL/pgSQL chapter of the PostgreSQL documentation and is shown as the plpgsql from the manual and then the plxruby, plxphp, plxjs, plxpython3, and plxcobol versions. Because plx transpiles to plpgsql, the plx versions produce plpgsql equivalent to the manual’s form; the notes under each example point out where they differ.
The dialects that restructure their input rather than rename keywords (plxplsql for Oracle PL/SQL, plxts for TypeScript, plxtsql for Transact-SQL, and plxgo for Go) are covered in full in their own chapters (see Further reading).
Install the extension first:
CREATE EXTENSION plx;
A scalar function
PL/pgSQL manual, “Structure of PL/pgSQL” (sales_tax).
plpgsql:
CREATE FUNCTION sales_tax(subtotal real) RETURNS real AS $$
BEGIN
RETURN subtotal * 0.06;
END;
$$ LANGUAGE plpgsql;
plxruby:
CREATE FUNCTION sales_tax(subtotal real) RETURNS real LANGUAGE plxruby AS $$
return subtotal * 0.06
$$;
plxphp:
CREATE FUNCTION sales_tax(subtotal real) RETURNS real LANGUAGE plxphp AS $$
return $subtotal * 0.06;
$$;
plxjs:
CREATE FUNCTION sales_tax(subtotal real) RETURNS real LANGUAGE plxjs AS $$
return subtotal * 0.06;
$$;
plxpython3:
CREATE FUNCTION sales_tax(subtotal real) RETURNS real LANGUAGE plxpython3 AS $$
return subtotal * 0.06
$$;
plxcobol:
CREATE FUNCTION sales_tax(subtotal real) RETURNS real LANGUAGE plxcobol AS $$
PROCEDURE DIVISION.
GOBACK RETURNING SUBTOTAL * 0.06.
$$;
Conditionals
PL/pgSQL manual, “IF-THEN-ELSIF”.
plpgsql:
CREATE FUNCTION classify(number int) RETURNS text AS $$
DECLARE
result text;
BEGIN
IF number = 0 THEN
result := 'zero';
ELSIF number > 0 THEN
result := 'positive';
ELSIF number < 0 THEN
result := 'negative';
ELSE
result := 'NULL';
END IF;
RETURN result;
END;
$$ LANGUAGE plpgsql;
plxruby:
CREATE FUNCTION classify(number int) RETURNS text LANGUAGE plxruby AS $$
result #:: text
if number == 0
result = "zero"
elsif number > 0
result = "positive"
elsif number < 0
result = "negative"
else
result = "NULL"
end
return result
$$;
plxphp:
CREATE FUNCTION classify(number int) RETURNS text LANGUAGE plxphp AS $$
$result = "" /*:: text */;
if ($number == 0) { $result = "zero"; }
elseif ($number > 0) { $result = "positive"; }
elseif ($number < 0) { $result = "negative"; }
else { $result = "NULL"; }
return $result;
$$;
plxjs:
CREATE FUNCTION classify(number int) RETURNS text LANGUAGE plxjs AS $$
let result /*:: text */;
if (number == 0) { result = "zero"; }
else if (number > 0) { result = "positive"; }
else if (number < 0) { result = "negative"; }
else { result = "NULL"; }
return result;
$$;
plxpython3:
CREATE FUNCTION classify(number int) RETURNS text LANGUAGE plxpython3 AS $$
result #:: text
if number == 0:
result = "zero"
elif number > 0:
result = "positive"
elif number < 0:
result = "negative"
else:
result = "NULL"
return result
$$;
plxcobol:
CREATE FUNCTION classify(number int) RETURNS text LANGUAGE plxcobol AS $$
WORKING-STORAGE SECTION.
01 WS-RESULT PIC X(8).
PROCEDURE DIVISION.
EVALUATE TRUE
WHEN NUMBER = 0
MOVE "zero" TO WS-RESULT
WHEN NUMBER > 0
MOVE "positive" TO WS-RESULT
WHEN NUMBER < 0
MOVE "negative" TO WS-RESULT
WHEN OTHER
MOVE "NULL" TO WS-RESULT
END-EVALUATE
GOBACK RETURNING WS-RESULT.
$$;
Note: == lowers to SQL =. A comparison with a NULL argument is unknown, so
none of the branches match and the else branch runs.
A loop over a query
PL/pgSQL manual, “Looping through query results”.
plpgsql:
CREATE FUNCTION order_total(g int) RETURNS bigint AS $$
DECLARE
total bigint := 0;
r record;
BEGIN
FOR r IN SELECT amount FROM orders WHERE grp = g LOOP
total := total + r.amount;
END LOOP;
RETURN total;
END;
$$ LANGUAGE plpgsql;
plxruby:
CREATE FUNCTION order_total(g int) RETURNS bigint LANGUAGE plxruby AS $$
total = 0 #:: bigint
query("SELECT amount FROM orders WHERE grp = #{g}").each do |r|
total = total + r.amount
end
return total
$$;
plxphp:
CREATE FUNCTION order_total(g int) RETURNS bigint LANGUAGE plxphp AS $$
$total = 0 /*:: bigint */;
foreach (query("SELECT amount FROM orders WHERE grp = {$g}") as $r) {
$total = $total + $r->amount;
}
return $total;
$$;
plxjs:
CREATE FUNCTION order_total(g int) RETURNS bigint LANGUAGE plxjs AS $$
let total = 0 /*:: bigint */;
for (const r of query(`SELECT amount FROM orders WHERE grp = ${g}`)) {
total = total + r.amount;
}
return total;
$$;
plxpython3:
CREATE FUNCTION order_total(g int) RETURNS bigint LANGUAGE plxpython3 AS $$
total = 0 #:: bigint
for r in query(f"SELECT amount FROM orders WHERE grp = {g}"):
total = total + r.amount
return total
$$;
plxcobol:
CREATE FUNCTION order_total(g int) RETURNS bigint LANGUAGE plxcobol AS $$
WORKING-STORAGE SECTION.
01 WS-TOTAL PIC 9(18) VALUE 0.
01 WS-R TYPE RECORD.
PROCEDURE DIVISION.
PERFORM WS-R OVER "SELECT amount FROM orders WHERE grp = $1" USING G
ADD WS-R.AMOUNT TO WS-TOTAL
END-PERFORM
GOBACK RETURNING WS-TOTAL.
$$;
An integer FOR loop
PL/pgSQL manual, “FOR (integer variant)”.
plpgsql:
CREATE FUNCTION sum_to(n int) RETURNS bigint AS $$
DECLARE
total bigint := 0;
BEGIN
FOR i IN 1..n LOOP
total := total + i;
END LOOP;
RETURN total;
END;
$$ LANGUAGE plpgsql;
plxruby:
CREATE FUNCTION sum_to(n int) RETURNS bigint LANGUAGE plxruby AS $$
total = 0 #:: bigint
for i in 1..n
total = total + i
end
return total
$$;
plxphp:
CREATE FUNCTION sum_to(n int) RETURNS bigint LANGUAGE plxphp AS $$
$total = 0 /*:: bigint */;
for ($i = 1; $i <= $n; $i++) {
$total = $total + $i;
}
return $total;
$$;
plxjs:
CREATE FUNCTION sum_to(n int) RETURNS bigint LANGUAGE plxjs AS $$
let total = 0 /*:: bigint */;
for (let i = 1; i <= n; i++) {
total = total + i;
}
return total;
$$;
plxpython3:
CREATE FUNCTION sum_to(n int) RETURNS bigint LANGUAGE plxpython3 AS $$
total = 0 #:: bigint
for i in range(1, n + 1):
total = total + i
return total
$$;
plxcobol:
CREATE FUNCTION sum_to(n int) RETURNS bigint LANGUAGE plxcobol AS $$
WORKING-STORAGE SECTION.
01 WS-TOTAL PIC 9(18) VALUE 0.
01 WS-I PIC 9(9).
PROCEDURE DIVISION.
PERFORM VARYING WS-I FROM 1 BY 1 UNTIL WS-I > N
ADD WS-I TO WS-TOTAL
END-PERFORM
GOBACK RETURNING WS-TOTAL.
$$;
A set-returning function
PL/pgSQL manual, “RETURN NEXT and RETURN QUERY”.
plpgsql:
CREATE FUNCTION squares(n int) RETURNS SETOF int AS $$
BEGIN
FOR i IN 1..n LOOP
RETURN NEXT i * i;
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql;
plxruby:
CREATE FUNCTION squares(n int) RETURNS SETOF int LANGUAGE plxruby AS $$
for i in 1..n
return_next i * i
end
return
$$;
plxphp:
CREATE FUNCTION squares(n int) RETURNS SETOF int LANGUAGE plxphp AS $$
for ($i = 1; $i <= $n; $i++) {
return_next($i * $i);
}
return;
$$;
plxjs:
CREATE FUNCTION squares(n int) RETURNS SETOF int LANGUAGE plxjs AS $$
for (let i = 1; i <= n; i++) {
return_next(i * i);
}
return;
$$;
plxpython3:
CREATE FUNCTION squares(n int) RETURNS SETOF int LANGUAGE plxpython3 AS $$
for i in range(1, n + 1):
return_next(i * i)
return
$$;
plxcobol:
CREATE FUNCTION squares(n int) RETURNS SETOF int LANGUAGE plxcobol AS $$
WORKING-STORAGE SECTION.
01 WS-I PIC 9(9).
PROCEDURE DIVISION.
PERFORM VARYING WS-I FROM 1 BY 1 UNTIL WS-I > N
RETURN-NEXT WS-I * WS-I
END-PERFORM
GOBACK.
$$;
Trapping errors
PL/pgSQL manual, “Trapping errors”.
plpgsql:
CREATE FUNCTION safe_divide(a int, b int) RETURNS int AS $$
BEGIN
RETURN a / b;
EXCEPTION
WHEN division_by_zero THEN
RAISE NOTICE 'caught division_by_zero';
RETURN -1;
END;
$$ LANGUAGE plpgsql;
plxruby:
CREATE FUNCTION safe_divide(a int, b int) RETURNS int LANGUAGE plxruby AS $$
begin
return a / b
rescue => e
raise notice: "caught: #{e.message}"
return -1
end
$$;
plxphp:
CREATE FUNCTION safe_divide(a int, b int) RETURNS int LANGUAGE plxphp AS $$
try {
return $a / $b;
} catch (\Exception $e) {
raise('notice', "caught: " . $e->message);
return -1;
}
$$;
plxjs:
CREATE FUNCTION safe_divide(a int, b int) RETURNS int LANGUAGE plxjs AS $$
try {
return a / b;
} catch (e) {
raise("notice", `caught: ${e.message}`);
return -1;
}
$$;
plxpython3:
CREATE FUNCTION safe_divide(a int, b int) RETURNS int LANGUAGE plxpython3 AS $$
try:
return a / b
except Exception as e:
raise("notice", f"caught: {e.message}")
return -1
$$;
plxcobol:
CREATE FUNCTION safe_divide(a int, b int) RETURNS int LANGUAGE plxcobol AS $$
PROCEDURE DIVISION.
BEGIN-TRY
GOBACK RETURNING A / B
WHEN DIVISION-BY-ZERO
DISPLAY "caught division by zero"
GOBACK RETURNING -1
END-TRY.
$$;
Note: the plxruby, plxphp, plxjs, and plxpython3 forms catch every error
(WHEN OTHERS), not only division_by_zero, and report the error text with
e.message. The plxcobol form catches only division_by_zero, like the
plpgsql.
Raising errors
PL/pgSQL manual, “Errors and messages”.
plpgsql:
RAISE EXCEPTION 'Nonexistent ID --> %', user_id USING ERRCODE = 'no_data_found';
plxruby:
raise exception: "Nonexistent ID --> #{user_id}", errcode: "P0002"
plxphp:
throw new Exception("Nonexistent ID --> {$user_id}");
plxjs:
throw new Error(`Nonexistent ID --> ${user_id}`);
plxpython3:
raise ValueError(f"Nonexistent ID --> {user_id}")
plxcobol:
RAISE EXCEPTION concat("Nonexistent ID --> ", USER-ID) SQLSTATE "P0002"
Note: throw in plxphp and plxjs and raise ValueError(...) in plxpython3
raise SQLSTATE P0001 (raise_exception). Only the plxruby and plxcobol forms
set a specific SQLSTATE such as P0002 (no_data_found).
Anonymous code blocks
plxruby:
DO LANGUAGE plxruby $$
for i in 1..3
raise notice: "row #{i}"
end
$$;
plxphp:
DO LANGUAGE plxphp $$
for ($i = 1; $i <= 3; $i++) { raise('notice', "row {$i}"); }
$$;
plxjs:
DO LANGUAGE plxjs $$
for (let i = 1; i <= 3; i++) { raise("notice", `row ${i}`); }
$$;
plxpython3:
DO LANGUAGE plxpython3 $$
for i in range(1, 4):
raise("notice", f"row {i}")
$$;
plxcobol:
DO LANGUAGE plxcobol $$
WORKING-STORAGE SECTION.
01 WS-I PIC 9(9).
PROCEDURE DIVISION.
PERFORM VARYING WS-I FROM 1 BY 1 UNTIL WS-I > 3
DISPLAY "row " WS-I
END-PERFORM.
$$;
Procedures and transaction control
PL/pgSQL manual, “Transaction Management”. A body can also be a procedure,
created with CREATE PROCEDURE and run with CALL. A procedure invoked outside
an explicit transaction block can commit and roll back; a function cannot.
plpgsql:
CREATE PROCEDURE load_batches(n int) AS $$
BEGIN
FOR i IN 1..n LOOP
INSERT INTO batch_log VALUES (i);
COMMIT;
END LOOP;
END;
$$ LANGUAGE plpgsql;
plxruby:
CREATE PROCEDURE load_batches(n int) LANGUAGE plxruby AS $$
for i in 1..n
execute("INSERT INTO batch_log VALUES ($1)", i)
commit()
end
$$;
plxpython3:
CREATE PROCEDURE load_batches(n int) LANGUAGE plxpython3 AS $$
for i in range(1, n + 1):
execute("INSERT INTO batch_log VALUES ($1)", i)
commit()
$$;
plxphp, plxjs and plxts use the same commit() and rollback() calls,
plxcobol uses the COMMIT and ROLLBACK verbs, and plxplsql uses COMMIT; as
in PL/SQL. plxtsql and plxgo do not support transaction control. Calling
commit() from a function, or from a procedure called inside an explicit transaction block,
raises invalid transaction termination, as in plpgsql.
Trust and security
The plx languages are declared TRUSTED. This is correct for the following
reasons:
- A plx function is transpiled to plpgsql and executed by plpgsql’s call
handler. It can do what a plpgsql function can do, and nothing more: no direct
filesystem access, no network access, no arbitrary native code. SQL runs with
the privileges of the calling role, or of the function owner for a
SECURITY DEFINERfunction, exactly as in plpgsql. - plx does not embed a language runtime. This is the difference from the native PL/PHP and PL/Ruby, which are untrusted because they load a full PHP or Ruby interpreter into the backend.
Who can use the languages:
- Installing plx (
CREATE EXTENSION plx) requires a superuser. - Once installed, every role can create functions in the plx languages, because
PostgreSQL grants USAGE on a trusted language to PUBLIC, as it does for
plpgsql. Restrict this per language, for example
REVOKE USAGE ON LANGUAGE plxruby FROM PUBLIC. - Creating a function or running a
DOblock in a plx language also requires USAGE on plpgsql. plx is a syntax layer over plpgsql, so a role with USAGE on a plx language could otherwise run any plpgsql: a body that already begins with the/*plx:v1:...*/header is stored without transpiling, so that dump and restore work, and plxplsql accepts a body that is already close to plpgsql. SoREVOKE USAGE ON LANGUAGE plpgsql FROM PUBLICalso stops such a role from using every plx language. It then gets:
ERROR: permission denied for language plpgsql
DETAIL: Language plxplsql is executed as plpgsql, so using it requires USAGE on plpgsql.
As with plpgsql, functions that already exist keep running, and superusers are not affected.
Points to keep in mind:
- The transpiler is C code that parses the function body at
CREATE FUNCTIONtime. It limits expression and statement nesting depth, rejecting deeper input with anested too deeplyerror, and has been fuzzed with mutation and pathological inputs across all dialects (seetest/fuzz.py). Continued fuzzing is recommended before relying on it in a hostile multi-tenant deployment. - An interpolated value in the SQL argument of
query,fetch_one,execute,performand the other SQL calls never becomes SQL text. plx binds it as a parameter, with or without bind arguments, so data cannot inject SQL:
r = fetch_one("SELECT count(*) AS c FROM ug WHERE name = '#{n}' LIMIT $1", lim)
becomes
EXECUTE 'SELECT count(*) AS c FROM ug WHERE name = ($2) LIMIT $1' INTO r USING lim, n;
To put text into the SQL itself, write ident(e) for an identifier (it
calls quote_ident), literal(e) for a quoted literal (quote_nullable),
or raw(e) for a SQL fragment. raw() inserts the text unquoted, so it
carries the same SQL-injection responsibility as plpgsql
EXECUTE ... || value: never pass it untrusted input. A SQL string built
earlier and passed to execute in a variable is ordinary string
concatenation, and plx does not bind the values in it. See
Interpolation in SQL.
- Functions that use the string-append operators name the extension’s
plx_strbuild type and its append function with the schema plx is installed
in. They work whatever the function’s search_path is, and a function
planted earlier on the path cannot take their place. Two limits follow:
- Functions created before 3.0.0 keep the unqualified names they were
generated with. Run their CREATE FUNCTION again to get the qualified
names.
- A restored function with a string append needs plx in the same schema as
in the source database. So does a plxtsql function with a conversion to
an integer type of a value whose type plx cannot see, which calls
plx_tsql_int4 (or plx_tsql_int2, plx_tsql_int8) the same way. See
LIMITATIONS.md.
- Grant CREATE on plx’s schema only to roles you trust. No function
created there can take the place of these calls, but one with the same
name can make them ambiguous: a plx_tsql_int4(record, int DEFAULT 0)
makes every such plxtsql conversion fail with
function ...plx_tsql_int4(record) is not unique.
- Pin the path on every SECURITY DEFINER function, as the PostgreSQL manual
advises, because the SQL in its body still finds your own tables and
functions through search_path. List only trusted schemas, with pg_temp
last. The schema plx is installed in does not have to be on it:
ALTER FUNCTION app.note_list() SET search_path = app, pg_temp;
Further reading
- Home: build and install.
- Per-dialect chapters: plxruby, plxphp, plxjs, plxpython3, plxcobol, plxplsql, plxts, plxtsql, plxgo (full syntax, supported and rejected constructs, semantic differences).
- PARITY.md: the plpgsql construct parity matrix.
- ARCHITECTURE.md: how plx maps to the plpgsql engine.
- TRANSPILER.md: the original transpiler design specification.