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.

Answer 1

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:

1. wrap your function calling SQL queries into BEGIN / COMMIT statements in order to have better control over errors;
2. add EXCEPTION blocks to your code to catch and track errors. Note, though, that this will affect function performance;
3. 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);
4. 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.

Answer 2

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.