PostgreSQL vs. Oracle: checking PL / pgSQL compilation time

Question

Summary: PostgreSQL is amazing, but we face many problems at work because it defers many PL / pgSQL code checks to runtime. Is there a way to make it more like Oracle PL / SQL in this regard?

For instance...

Try this in any Oracle DB:

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

Oracle immediately (i.e. at compile time!) Responds:

 [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 this - unfortunately! - execute a fine ... Error not reported.

But when you 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 get PostgreSQL to parse and check at function definition time - not at run time? We have a lot of obsolete PL / SQL codes in the workplace that we port to PostgreSQL, but the lack of checks during compilation is very painful, forcing us to do manual work - that is, write code to check all the code paths in all functions / procedures that otherwise were automated by Oracle.

Answer 1

Yes, this is a known issue.

PL / pgSQL (like any function other than SQL ) is a black box for PostgreSQL, so it’s actually impossible to detect errors other than runtime.

You can do a few things:

• terminates your function by calling SQL queries in BEGIN / COMMIT expressions to have better error control;
• add EXCEPTION blocks your code to catch and track errors. Please note that this will affect the performance of the function;
• use the plpgsql_check extension developed by Pavel Stěhule, which is one of the main contributors to the PL / pgSQL development. I believe that ultimately this extension will turn it into the foundation of PostgreSQL, but it will take some time (now we are in state 9.4beta3);
• You can also examine this related question: postgresql syntax without query execution

Indeed, it seems that you really need a single testing platform.

Answer 2

Plpgsql is designed without compilation semantics checking. I'm not sure how this function was the intention or side effect of the old plpgsql implementation, but over time we found some advantages (but taking into account the mentioned drawback).

and

• fewer dependency problems between functions and other database objects. This is a simple solution to cyclic dependency. Deploying plpgsql functions is easier because you do not need to respect dependency.
• Some templates with temporary tables are possible due to lazy dependency. This is necessary because Postgres does not support global temporary tables.

Example:

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

Minus:

• It is not possible to conduct a deep compile-time check (identifier check), although sometimes this is possible.

For some large projects it is necessary to use a combination of solutions:

• regression and unit tests are the base because some situation cannot be checked statically - for example, dynamic SQL.
• plpgsql_check is an external but supported project used by several large companies and larger plpgsql users. It can provide static validation of SQL identifiers. You can perform this check with DDL triggers.