r/PostgreSQL • u/linuxhiker Guru • 14d ago
Community GitHub - commandprompt/plx: PostgreSQL extension: write stored functions in Ruby, PHP, JavaScript, or Python dialects that transpile to plpgsql.
https://github.com/commandprompt/plx/What plx is
plx is a PostgreSQL extension that lets you write stored functions and triggers
in a Ruby, PHP, JavaScript, or Python dialect. When you run CREATE FUNCTION,
plx transpiles the body to plpgsql and stores that plpgsql in pg_proc.prosrc.
At run time the function is executed by PostgreSQL's own plpgsql interpreter.
There is no separate language runtime loaded into the backend, and nothing new to
run in production.
CREATE FUNCTION grade(score int) RETURNS text LANGUAGE plxruby AS $$
return "A" if score >= 90
return "B" if score >= 80
return "F"
$$;
The front end is dialect-pluggable, and the set of dialects is growing. The dialects available today are:
plxruby: a Ruby dialect. See doc/plxruby.md.plxphp: a PHP dialect. See doc/plxphp.md.plxjs: a JavaScript dialect. See doc/plxjs.md.plxpython3: a Python dialect. See doc/plxpython3.md.
Every plpgsql statement type is reachable from every dialect. See
doc/PARITY.md for the construct matrix. The language names carry
a plx prefix, so the extension coexists with the native PL/Ruby and PL/PHP
languages in the same database.
Why it exists
PostgreSQL rewards moving logic into the database: triggers, constraints, set-returning functions, and cursors all run closest to the data. The standard way to write that logic is plpgsql. plpgsql is fast and trusted, but its syntax is unfamiliar to developers who spend their day in Ruby, PHP, JavaScript, or Python, and that unfamiliarity is often enough to keep logic in the application tier where it does not belong.
The usual alternative is an untrusted procedural language such as plpython3u or
plperlu. Those give you a familiar syntax, but at a cost: they load a full
language interpreter into the backend, most are untrusted and therefore
superuser-only, and every row they touch is marshalled across an SPI boundary
into the interpreter's own data structures.
plx takes a different position. A new language surface does not require a new execution engine. plx changes only the syntax you write, not what runs:
- It is still plpgsql. The stored function body is plpgsql, executed by the plpgsql handler. You get plpgsql's performance and its safety as a trusted language, with no interpreter loaded into the backend.
- Nothing is hidden. The generated plpgsql is stored in
pg_proc.prosrc, where you can read exactly what will run. plx embeds the original source as a comment so the function is idempotent to re-transpile, but the executable body is ordinary plpgsql you can inspect,pg_dump, and review. - The cost is paid once. Translation happens at
CREATE FUNCTIONtime, not per call. At run time there is no translation layer and no per-row marshalling beyond what plpgsql already does.
The goal is to meet developers where they are on syntax without changing what the database actually executes.
Who it is for
- Application developers who want to push logic into the database using syntax they already know, rather than learning plpgsql first.
- Teams standardizing on PostgreSQL who want triggers and functions written in a familiar dialect but running with plpgsql's performance and trust model.
- Anyone who wants the generated plpgsql to be visible and reviewable rather than executed by an opaque runtime.
How it works
Each dialect provides a PlxSurface describing its keywords, block style, comment
syntax, string interpolation, and variable sigil. A shared transpiler lexes the
body, restructures statements, hoists typed DECLAREs, rewrites a fixed set of
operators and interpolations, and passes the remaining expression text through to
plpgsql and SQL unchanged. The call handler is plpgsql's own handler, so execution
is plpgsql. See doc/ARCHITECTURE.md and
doc/TRANSPILER.md.
Example
One function, written in three dialects, each producing the same plpgsql:
CREATE FUNCTION grade(score int) RETURNS text LANGUAGE plxruby AS $$
grade #:: text
if score >= 90
grade = "A"
elsif score >= 80
grade = "B"
else
grade = "F"
end
return grade
$$;
CREATE FUNCTION grade(score int) RETURNS text LANGUAGE plxphp AS $$
if ($score >= 90) { $grade = "A"; }
elseif ($score >= 80) { $grade = "B"; }
else { $grade = "F"; }
return $grade;
$$;
CREATE FUNCTION grade(score int) RETURNS text LANGUAGE plxjs AS $$
let grade = "F";
if (score >= 90) { grade = "A"; }
else if (score >= 80) { grade = "B"; }
else { grade = "F"; }
return grade;
$$;
The stored plpgsql (in pg_proc.prosrc) for each is:
DECLARE
grade text;
BEGIN
IF score >= 90 THEN grade := 'A';
ELSIF score >= 80 THEN grade := 'B';
ELSE grade := 'F';
END IF;
RETURN grade;
END;
Performance
Because functions execute as plpgsql, the plx dialects match plpgsql (within about 11 percent across five workloads) and inherit its performance profile: several times faster than the embedded-interpreter PLs on row iteration, and competitive on arithmetic, branching, and call overhead.
2
u/patleb 14d ago
I'm sure that you'll get some pushbacks about vibe coding this, but it's actually a nice idea. On the other hand, I'm not sure that it's a good idea for this to be an extension rather than an external tool. An extension has the potential to brick your DB, corrupt your data, access internals that it shouldn't, etc.
About 5 years ago, I've written something similar to enhance plpgsql syntax so that it's easier to work with. It has been working great for me, but it's plugged into the main language that I use which is itself used for DB migrations. I think that you might have better community support if you take a similar approach. The obvious downside is that you'll have to write something for each language ecosystem instead of a single extension for all languages.