PostgreSQL vs Oracle: "compile-time" checking of PL/pgSQL

Question

Executive summary: PostgreSQL is amazing, but we are facing many issues at work due to the fact that it postpones many checks on PL/pgSQL code until runtime. Is there a way to make it more like Oracle's PL/SQL in this respect?

For example...

Try executing this in any Oracle DB:

create function foo return number as
begin
  select a from dual;
  return a;
end;

Oracle will immediately (i.e. at compile-time!) respond with:

[Error] ORA-00904: invalid identifier

Now try the semantically equivalent thing in PostgreSQL:

CREATE OR REPLACE FUNCTION public.foo ()
    RETURNS integer AS
$body$
    BEGIN
        select a;
        return a;
    END;
$body$
LANGUAGE plpgsql;

You will see it - unfortunately! - execute fine ... No error is reported.

But when you then try to call this function (i.e. at runtime) you will get:

ERROR:  column "a" does not exist
LINE 1: select a

Is there a way to force PostgreSQL to perform syntax analysis and checking at function definition time - not at run-time? We have tons of legacy PL/SQL code at work, which we are porting to PostgreSQL - but the lack of compile-time checks is very painful, forcing us to do manual work - i.e. writing code to test all code paths in all functions/procedures - that was otherwise automated in Oracle.

score 8 · Accepted Answer · edited May 23 '17 at 11:45

Yes, this is a known issue.

PL/pgSQL (like any other function, except on SQL) is a “black box” for the PostgreSQL, therefore it is not really possible to detect errors except in runtime.

You can do several things:

wrap your function calling SQL queries into BEGIN / COMMIT statements in order to have better control over errors;
add EXCEPTION blocks to your code to catch and track errors. Note, though, that this will affect function performance;
use plpgsql_check extension, developed by the Pavel Stěhule, who is one of the main contributors to PL/pgSQL development. I suppose eventually this extension will make it into the core of the PostgreSQL, but it'll take some time (now we're in 9.4beta3 state);
You might also look into this related question: postgresql syntax check without running the query

And it really looks like you're in a huge need of a unit testing framework.

On the subject of testing, I would suggest the original poster heads immediately to http://pgtap.org/ and make his life *much* easier. — Richard Huxton, Oct 13 '14 at 17:32

score 5 · Answer 2 · edited Feb 21 '20 at 11:30

Plpgsql language is designed without semantics checking at compile-time. I am not sure if this feature was an intention or a side effect of old plpgsql implementation, but over time we found some advantages to it (but also disadvantages as you mentioned).

Plus :

there are less issues with dependency between functions and other database objects. It's a simple solution to cyclic dependency problem. Deployment of plpgsql functions is easier, because you don't need to respect dependency.
Some patterns with temporary tables are possible using lazy dependency checking. It's necessary, because Postgres doesn't support global temporary tables.

Example:

BEGIN
  CREATE TEMP TABLE xx(a int);
  INSERT INTO xx VALUES(10); -- isn't possible with compile-time dependency checks
END;

Minus:

Compile-time deep checking is not possible (identifiers checking), although it's sometimes possible.

For some bigger projects a mix of solutions should be used:

regress and unit tests - it is fundamental, because some situations cannot be checked statically - dynamic SQL for example.
plpgsql_check - it is an external but supported project used by some bigger companies and bigger plpgsql users. It can enforce a static check of SQL identifiers validity. You can enforce this check by DDL triggers.

PostgreSQL vs Oracle: "compile-time" checking of PL/pgSQL

2 Answers2

Linked