Why Use PLpgSQL?

Why Use PL/pgSQL?

PL/pgSQL is a powerful extension for SQL. Not all common problems can be solved with plain SQL code. PL/pgSQL was designed to be a programming language that can be used within the database, and for this reason PL/pgSQL is always a good choice to build enhancements and new features for a PostgreSQL database.

One advantage of PL/pgSQL is platform independence, which means that there is no reason for the programmer to care whether the function runs on an x86-CPU, an Alpha machine, or any other platform supported by PostgreSQL. All machine matters are solely a problem of PL/pgSQL's internals (who wants to care about byte order and so on?), and that is why PL/pgSQL functions do not need to be ported to other platforms. PL/pgSQL is a part of the database, and this is possibly the most significant advantage over any other way of building functions in PostgreSQL environments.

However, debugging PL/pgSQL code can be real pain. Inserting the function into the database does not guarantee that the function can be called.

Up to now, there is no debugger available, and PostgreSQL often does not offer precise error messages that help the user find an error quickly. Functions containing many lines of source code can hardly be debugged . We strongly recommend that you not write a number of functions at once; it is often better to start with something like "Hello World" and improve the function step by step. This guarantees ”in a way ”that the programmer can locate the lines where a syntax error might have occurred.