PostgreSQL vs Oracle: "compile-time" checking of PL/pgSQL
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 intoBEGIN
/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.
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.