1

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?

Community
  • 1
Pavel V.
  • 757
  • 3
  • 13
  • 30

2 Answers2

3

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.

Erwin Brandstetter
  • 185,527
  • 28
  • 463
  • 633
2

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).

Vérace
  • 30,923
  • 9
  • 73
  • 85