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 plpgsql form is also what the plx versions produce and run.

The dialects that restructure their input rather than rename keywords — plxplsql (Oracle PL/SQL), plxts (TypeScript), plxtsql (Transact-SQL), and plxgo (Go) — are covered in full in their own chapters (see Further reading); each produces the same plpgsql shown here.

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.
$$;

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"

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.
$$;

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. All SQL runs with the privileges of the calling role.
• 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.

Two points to keep in mind:

• The transpiler is C code that parses the function body at CREATE FUNCTION time. It has a recursion-depth limit and has been fuzzed with mutation and pathological inputs across all dialects (see test/fuzz.py) with no crashes or hangs. Continued fuzzing is recommended before relying on it in a hostile multi-tenant deployment.
• query, perform, execute, and fetch_one with interpolated values carry the same SQL-injection responsibility as plpgsql EXECUTE ... || value. Use bind parameters (execute(sql, a, b)) for untrusted input.

Further reading

• README.md: build and install.
• Per-dialect chapters: doc/plxruby.md, doc/plxphp.md, doc/plxjs.md, doc/plxpython3.md, doc/plxcobol.md, doc/plxplsql.md, doc/plxts.md, doc/plxtsql.md, doc/plxgo.md (full syntax, supported and rejected constructs, semantic differences).
• doc/PARITY.md: the plpgsql construct parity matrix.
• doc/ARCHITECTURE.md: how plx maps to the plpgsql engine.
• doc/TRANSPILER.md: transpiler specification.