In PostgreSQL, syntax errors (except for trivial syntax errors) are reported while running the code in question; unlike Oracle, PostgreSQL can't find errors without running the part of the code. This is a big pain while debugging functions with many conditions in them. The only way I know now is to make testing data for every branch in each function and run the function over them. This should be fine, but just making the data for all the conditions in all the ordinary and trigger functions would consume hours, so I wonder if there is some better solution (free and open source, if possible).

So how to avoid making so much testing data while debugging my PL/pgSQL functions?

  • 1
    Reading that link it is only the SQL statements within a PL/PgSQL function that are not compiled up front (presumably because of the overhead associated with planning them). Is it syntax route in the SQL queries you are concerned about?
    – harmic
    Jun 17, 2014 at 11:10
  • @harmic: I'm concerned mostly about the PL/pgSQL parts and interaction between them and pure SQL, syntax in SQL queries should be already OK. Whether the SELECTs in the functions return what they should return is another, but it's hard to separate from the PL/pgSQL statements.
    – Pavel V.
    Jun 17, 2014 at 12:00

2 Answers 2


Well, there is the Postgres extension plpgsql_check, successor of plpgsql_lint:

plpgsql_check is next generation of plpgsql_lint. It allows to check source code by explicit call plpgsql_check_function.

I'm not currently using either, but the author Pavel Stehule is one of the core developers of PL/pgSQL (and also active around here).

Pavel announced it Dec. 2013 in this blog post.

  • It should be exactly what I need. I have some problems building it, but that's for different question, now I know that such a tool exists and I am sure the problems will be solved somehow. +1 and accepted.
    – Pavel V.
    Jun 19, 2014 at 7:42

You say Oracle reports syntax errors at compile time - true (I didn't know that PostgreSQL didn't!). But even with Oracle (as with any other programming environment), code that compiles perfectly most certainly doesn't always work as intended - it has to be tested with real data (and real volumes for performance).

Testing is a pain, but it has to be done! Set up (this will take time) a generation environment and use a VM image or suchlike - using tools such as Databene (used briefly a considerable time ago - recollection is that it wasn't bad). Take a look at opensourcetesing. A Google of "open source data generation tools" gives a pile of stuff - this one looks interesting (caveat - haven't used).

  • That's what I was afraid of, and hoped there are some tools to make this easier. I tried a search for some data generator; I didn't find anything that would suit my need (behind simple generation function - making some sensible values to feed them with was the pain) yet, but I will give it a second try (thanks for your tips, anyway). I'll wait some time if anyone knows of some other shortcut; otherwise I'll accept your answer in few days.
    – Pavel V.
    Jun 17, 2014 at 11:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.