By John Doe December 26, 2024
Summary: In this article, we’ll learn how PL/pgSQL conditional expressions are parsed and evaluated in PostgreSQL.
Table of Contents
Introduction
Recently, in the PostgreSQL community, a user posted a PL/pgSQL block (shown below) and was surprised that it did not generate a syntax error:
DO $$
DECLARE i INT;
BEGIN
i = 0;
IF i = 0 AND THEN
RAISE NOTICE 'i = 0';
END IF;
END; $$;
At first glance, this code block seems incomplete. Notice the IF condition: it appears to be missing an additional condition after the AND operator. Logically, this should cause an exception due to the incomplete condition following AND.
IF i = 0 AND THEN
However, during PL/pgSQL execution, the condition is evaluated without any syntax errors or warnings. This raises a critical question:
- How does PostgreSQL internally process this condition?
- What allows this seemingly incomplete expression to work?
- How the condition “i = 0 AND” is processed?
While reviewing such PL/pgSQL it looks incomplete and assuming that it should fail will be surprising.
In this article, we’ll dive into the internals of PL/pgSQL to understand how this conditional statement is processed and why PostgreSQL does not flag it as erroneous.
Uncover PL/pgSQL Internal Code
One of the greatest advantages of open-source software is the ability to examine the code base directly. This gives us a foundation for understanding how things work or, in some cases, why they don’t break as expected.
Our investigation begins with the PLPGSQL_STMT_IF structure, tracing through the call stack in the pl_exec.c file.
By exploring the code, we find that IF statements and their conditions are evaluated using exec_run_select, which effectively executes a SELECT statement that returns a boolean result.
PL/pgSQL conditions as SELECT clause
Looking back at the original example, the condition i = 0 AND is processed within a SELECT clause. Here, AND essentially acts as a placeholder, allowing the PL/pgSQL engine to evaluate the condition without triggering syntax errors.
-- the condition is evaluated as SQL,
-- "and" is treated as column alias
SELECT 0 = 0 AND;
and
-----
t
(1 row)
This insight — that conditional expressions are evaluated as SELECT statements — opens up new possibilities. It means we can leverage various functions within conditions in PL/pgSQL, as demonstrated in the examples below.
The following snippets illustrate different ways conditional expressions can be evaluated in PL/pgSQL:
| Conditions | SQL | PL/pgSQL |
|---|---|---|
| Count evaluation | select count(1) = 1; | do $$ begin if count(1) = 1 and then raise notice ‘%’,‘Matched If Clause’; else raise notice ‘Not Match If Clase’; end if; end;$$; |
| Case-insensitive match | select (‘a’ ilike ‘A’); | do $$ begin if (‘a’ ilike ‘A’) and then raise notice ‘%’,‘Matched If Clause’; else raise notice ‘Not Match If Clase’; end if; end;$$; |
| Complex unnest – Array based Condition. | select COUNT(col1) filter(where col1 = ‘A’) = 2 from (select unnest(ARRAY[‘A’,‘B’,‘A’]) col1) | do $$ begin if COUNT(col1) filter(where col1 = ‘A’) = 2 from (select unnest(ARRAY[‘A’,‘B’,‘A’]) col1) then raise notice ‘%’, ‘Matched If Clause’; else raise notice ‘Not Match If Clase’; end if; end;$$; |
| Row Existence check | select exists (select 1 from generate_series(1,10000)); | do $$ begin if exists (select 1 from generate_series(1, 10000)) then raise notice ‘%’, ‘Matched If Clause’; else raise notice ‘Not Match If Clase’; end if; end;$$; |
Conclusion
PL/pgSQL processes conditional expressions using SELECT statements, allowing incomplete conditions like IF i = 0 AND to execute without errors.
Instead of failing, the AND is treated as part of a SELECT expression, letting PostgreSQL evaluate it flexibly. This approach lets developers incorporate various SELECT expressions directly into conditional evaluations, offering additional ways to build and test complex logic seamlessly in PL/pgSQL.