PostgreSQL

All expressions used in PL/pgSQL statements are processed using the server’s main SQL executor. For example, when you write a PL/pgSQL statement like

PL/pgSQL will evaluate the expression by feeding a query like

to the main SQL engine. While forming the SELECT command, any occurrences of PL/pgSQL variable names are replaced by query parameters, as discussed in detail in Section 43.11.1. This allows the query plan for the SELECT to be prepared just once and then reused for subsequent evaluations with different values of the variables. Thus, what really happens on first use of an expression is essentially a PREPARE command. For example, if we have declared two integer variables x and y, and we write

what happens behind the scenes is equivalent to

and then this prepared statement is EXECUTE`d for each execution of the `IF statement, with the current values of the PL/pgSQL variables supplied as parameter values. Normally these details are not important to a PL/pgSQL user, but they are useful to know when trying to diagnose a problem. More information appears in Section 43.11.2.

Since an `expression is converted to a `SELECT command, it can contain the same clauses that an ordinary SELECT would, except that it cannot include a top-level UNION, INTERSECT, or EXCEPT clause. Thus for example one could test whether a table is non-empty with

since the `expression between `IF and THEN is parsed as though it were SELECT count(*) > 0 FROM my_table. The SELECT must produce a single column, and not more than one row. (If it produces no rows, the result is taken as NULL.)

If you see anything in the documentation that is not correct, does not match your experience with the particular feature or requires further clarification, please use this form to report a documentation issue.

Privacy Policy | Code of Conduct | About PostgreSQL | Contact

Copyright © 1996-2024 The PostgreSQL Global Development Group