Debugging plx functions

A plx function is transpiled to plpgsql at CREATE FUNCTION time and executed by the plpgsql interpreter. Runtime errors are therefore reported by plpgsql, with a line number and CONTEXT that refer to the generated plpgsql stored in pg_proc.prosrc, not to your dialect source. This page explains how to correlate the two.

See the generated plpgsql

The generated body is ordinary, readable plpgsql, one construct per source construct. View it with:

\sf my_func                      -- in psql
-- or
SELECT pg_get_functiondef('my_func(int)'::regprocedure);

The first line is a plx sentinel comment (/*plx:v1:<dialect>:<hash>*/), then the DECLARE block, BEGIN, the body, and a trailing comment that carries your original source (base64-encoded). When an error says line 8 at RAISE, line 8 is counted from that sentinel line.

Recover your original source

plx embeds the original dialect source in the function, so you can always get it back. Define this helper once:

CREATE FUNCTION plx_source(f regprocedure) RETURNS text
LANGUAGE sql STABLE AS $$
  SELECT convert_from(
           decode((regexp_match(prosrc, 'plx-orig:b64[$]([^$]*)[$]plx-orig'))[1],
                  'base64'),
           'UTF8')
  FROM pg_proc WHERE oid = f;
$$;

Then:

SELECT plx_source('my_func(int)');

returns exactly the Ruby / PHP / JavaScript / Python / COBOL body you wrote.

Why the error line differs from your source line

plpgsql requires every local variable to be declared in a DECLARE block before BEGIN. plx therefore hoists your declarations to the top of the generated function. That, plus the one-line sentinel and the BEGIN, shifts the body down relative to your source, so the reported line does not equal your source line.

To locate a failing statement:

1. Read the reported line and construct (line N at RAISE, at assignment, …).
2. Print the generated body numbered and look at line N:

SELECT string_agg(n || ': ' || l, E'\n')
FROM regexp_split_to_table(
       (SELECT prosrc FROM pg_proc WHERE oid = 'my_func(int)'::regprocedure),
       E'\n') WITH ORDINALITY AS x(l, n)
WHERE l NOT LIKE '%plx-orig%';

1. The plpgsql at line N maps one-to-one to the statement you wrote; compare it to plx_source('my_func(int)').

Because the generated plpgsql mirrors your logic statement for statement, the construct name in the CONTEXT (RAISE, assignment, FOR over SELECT, …) plus the surrounding lines is usually enough to pinpoint the source statement. Keeping functions small keeps this trivial.

Compile-time errors

Errors raised at CREATE FUNCTION time (a construct outside the supported subset, a type that cannot be inferred, a missing END-IF, …) come from the plx transpiler and carry your dialect's name and the source line, for example:

ERROR:  plxruby: unsupported operator in statement
DETAIL:  at plxruby source line 3

These already point at your source, so no correlation is needed.