Yes. PostgreSQL supports IF statements inside functions written in PL/pgSQL, its procedural language. Put the condition and branches between BEGIN and END, and close the statement with END IF;. A function written in plain SQL does not use procedural IF; use a SQL CASE expression instead.
What an IF statement does in PL/pgSQL
An IF statement chooses which block of code to run based on a Boolean condition. It is useful when a function must apply different rules, return different values, or perform different actions depending on its arguments or database state. PL/pgSQL supports the familiar forms IF ... THEN, IF ... THEN ... ELSE, and IF ... THEN ... ELSIF ... ELSE.
The distinction between the language and the database matters. PostgreSQL functions can be written in different languages. PL/pgSQL is designed for procedural logic, including variables, loops, and conditional statements. SQL-language functions express a query or sequence of SQL statements, so conditional output is generally expressed with SQL constructs such as CASE.
PL/pgSQL is available in standard PostgreSQL installations and is commonly enabled in databases by default. If a database reports that the language is unavailable, an administrator can check the installation and language setup; do not assume the problem is the syntax of the IF itself.
A complete function with IF, ELSIF, and ELSE
This example classifies a numeric price. It also handles a missing value explicitly, which is important because a null price is not the same as a price of zero.
CREATE OR REPLACE FUNCTION price_band(p_price numeric)
RETURNS text
LANGUAGE plpgsql
AS $$
BEGIN
IF p_price IS NULL THEN
RETURN 'unknown';
ELSIF p_price < 10 THEN
RETURN 'low';
ELSIF p_price < 100 THEN
RETURN 'medium';
ELSE
RETURN 'high';
END IF;
END;
$$;
For example, SELECT price_band(7.50); returns low, while SELECT price_band(45); returns medium. The conditions are checked in order: once one is true, its branch runs and the later branches are skipped. The final ELSE handles values that did not match an earlier condition.
CREATE OR REPLACE FUNCTION creates the function or replaces a same-named function with a compatible argument signature. The dollar-quoted body, delimited here by $$, keeps the function body readable without escaping its internal single-quoted text values.
IF syntax and Boolean conditions
The general shape is IF condition THEN statements; ELSIF condition THEN statements; ELSE statements; END IF;. Use the exact keyword ELSIF, not ELSEIF. The trailing semicolon belongs after END IF, and each SQL or PL/pgSQL statement within a branch also needs its own semicolon.
Conditions can compare values, combine tests with AND and OR, or call expressions that return Boolean results. Parentheses are useful when a condition mixes operators, both for readability and to make the intended grouping unambiguous. For example:
IF p_status = 'active' AND p_balance > 0 THEN
-- statements for an active account with a positive balance
END IF;
PostgreSQL uses three-valued logic: a comparison involving NULL usually evaluates to unknown, not true or false. In an IF, a condition that is false or null does not enter the THEN branch. Test missing values with IS NULL or IS NOT NULL, rather than comparing with = NULL.
Returning values and running statements
In a function that returns a value, a branch can use RETURN expression; to finish the function and supply its result. If a function has several branches, ensure every possible execution path returns a value of the declared return type. Reaching the end of a non-void PL/pgSQL function without returning a result causes a runtime error.
A branch can also assign a value to a variable and return it later. Declare local variables in an optional DECLARE section before BEGIN, then use PL/pgSQL assignment syntax such as v_result := 'low';. This approach is handy if several branches share a final step, but direct returns are often clearer for short decision functions.
A function declared RETURNS void does not return a data value. Its branches can instead execute actions such as an INSERT, UPDATE, or call to another routine. Whether changing data is appropriate depends on how the function will be called and the application’s transaction design. A function cannot issue transaction-ending commands such as COMMIT or ROLLBACK as ordinary function-body operations.
Branching on data in the database
An IF condition can use a query expression, not just a comparison between function parameters. For example, a function may behave differently if an account exists. PL/pgSQL accepts an EXISTS expression as a Boolean condition:
IF EXISTS (SELECT 1 FROM accounts WHERE account_id = p_account_id) THEN
RETURN 'found';
ELSE
RETURN 'missing';
END IF;
Here, p_account_id must be a parameter or declared variable in the surrounding function. EXISTS checks whether the query would return at least one row; it does not need to retrieve every matching row. For more involved logic, a function can fetch data into a variable with SELECT ... INTO and then test that value, or inspect the PL/pgSQL FOUND status after a statement that sets it.
Choose the simplest form that communicates the rule. A direct EXISTS condition is concise for an existence test. Fetching a row is more suitable when later branches need its columns. Remember that database contents may change between separate statements, so keep related checks and actions within an appropriate transaction and design for concurrent updates where necessary.
IF in PL/pgSQL compared with CASE in SQL
Use PL/pgSQL IF when a function needs procedural control: multiple statements in a branch, local variables, loops, or decisions that determine which database operations run. Use SQL CASE when the decision simply determines a value in a query. For a value-only version of the price example, a SQL-language function can be written like this:
CREATE OR REPLACE FUNCTION price_band_sql(p_price numeric)
RETURNS text
LANGUAGE sql
IMMUTABLE
AS $$
SELECT CASE
WHEN p_price IS NULL THEN 'unknown'
WHEN p_price < 10 THEN 'low'
WHEN p_price < 100 THEN 'medium'
ELSE 'high'
END;
$$;
This SQL function returns the same labels without a procedural block. The IMMUTABLE declaration is appropriate here because the result depends only on the input and does not read changing table data or depend on session state. Do not copy that volatility label automatically: functions that read tables or depend on changing settings generally need a different volatility classification. The declaration informs PostgreSQL about behavior; it does not make a function immutable.
A SQL CASE is an expression that returns a value, while PL/pgSQL IF is a control-flow statement. They solve related but not identical problems. SQL also has conditional constructs such as COALESCE and NULLIF for specific null-handling cases, but they are not universal replacements for multi-branch procedural logic.
Nested conditions and choosing a clear structure
Conditions can be nested: a branch may contain another IF. Nesting can make sense when the second decision is relevant only after the first condition passes. However, several levels of indentation make code harder to follow. Consider combining related Boolean tests or using a single ELSIF chain when the choices are mutually exclusive.
- Use
ELSIFfor a sequence of alternative tests where the first matching branch should win. - Use nested
IFstatements when an inner decision depends on entering an outer branch. - Use
CASEfor a compact value-selection expression, especially inside a query. - Use separate helper functions only when they clarify reusable rules; splitting every small condition can obscure the overall behavior.
Order matters in an ELSIF chain. Put more specific tests before broader ones if the broader test would otherwise capture the same values. In the price example, testing “less than 100” before “less than 10” would incorrectly classify prices below 10 as medium, because the first true branch stops the chain.
Common errors and practical checks
Many errors come from mixing SQL syntax with PL/pgSQL syntax or omitting a required terminator. The function definition itself ends with a semicolon after the dollar-quoted body, and the procedural block has its own END;. These are separate boundaries: the block ends inside the body, while the final semicolon ends the CREATE FUNCTION command.
- Using IF in a SQL-language function: change the function to
LANGUAGE plpgsqlfor procedural logic, or express the result with SQLCASE. - Writing ELSEIF: PL/pgSQL spells the middle branch keyword
ELSIF. - Missing END IF: close every conditional block with
END IF;, including nested blocks. - Comparing to NULL with equals: use
IS NULL; ordinary equality does not test for null correctly. - Returning the wrong type: make each return expression compatible with the function’s declared
RETURNStype. - Forgetting a return path: verify what happens when no condition matches, especially if there is no
ELSE.
When PostgreSQL reports a syntax error, read the reported location alongside the complete function body. A missing semicolon earlier in the block can make the error appear to be at ELSIF or END IF even though the actual omission is above it. Test boundary values, null inputs, and values that should reach the ELSE branch.
Testing and maintaining conditional functions
Call a function with representative inputs using SELECT, and check both the returned value and any database changes it is meant to make. For a numeric classifier, useful cases include a value below the first threshold, values exactly at each threshold, a value above the highest threshold, and NULL. Testing the exact boundary catches mistakes caused by using < where the intended rule requires <=, or vice versa.
Keep the function’s behavior explicit. Name parameters clearly, handle nulls deliberately, and document unusual business rules near the relevant branch. If a function reads or modifies tables, also consider permissions, concurrent changes, and the transaction in which callers use it. Conditional logic does not itself make an operation safe or atomic; those properties depend on the SQL statements and transaction design.
For security, avoid building SQL commands by concatenating untrusted input. Static SQL with parameters is usually simpler. If dynamic SQL is genuinely needed in PL/pgSQL, use the supported EXECUTE form with parameter binding for values, and quote identifiers appropriately rather than treating values and identifiers as interchangeable. A branch may control whether dynamic SQL runs, but that does not remove injection risks from unsafe construction.
Frequently Asked Questions
Can I use IF in a PostgreSQL function?
Yes. Write the function in PL/pgSQL with LANGUAGE plpgsql, and put the conditional block inside its BEGIN and END block. For a SQL-language function, use SQL expressions such as CASE instead.
What is the correct keyword: ELSEIF or ELSIF?
PL/pgSQL uses ELSIF. The complete conditional must end with END IF;.
Can an IF condition check whether a row exists?
Yes. Use a Boolean expression such as IF EXISTS (SELECT 1 FROM ...). You can also fetch data first and test the resulting variable when the function needs the row’s values.
What happens when an IF condition evaluates to NULL?
It does not count as true, so its THEN branch is not taken. Test for null explicitly with IS NULL or IS NOT NULL when that distinction matters.
Can a PostgreSQL procedure use IF too?
Yes. Procedures written in PL/pgSQL can use the same conditional syntax. The main difference is how routines are invoked and what they return: functions are commonly called in expressions such as SELECT, while procedures are invoked with CALL.
Related
- Split in SQL: Practical Methods for Common Databases
- T-SQL STRING_SPLIT: Syntax, Order, and Examples
- JavaScript String Split: Syntax, Examples, and Tips
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel INDEX Array Formula: How It Works and When to Use ItIn Excel for Microsoft 365, INDEX can return a value from a range or return an array of…
- HLOOKUP in Excel: How Horizontal Lookups WorkHLOOKUP in Excel searches across the first row of a range for a value, then returns a value…
- Excel IFS Function: Syntax, Examples, and Common ErrorsExcel’s IFS function contains pairs of logical tests and the values to return when those tests are true.…
- Excel Shortcut to Drag a Formula DownIn Excel for Microsoft 365 on Windows, select the cell with the formula and the cells below it,…
- Snowflake SQL String Functions: Examples and Usage GuideSnowflake SQL string functions let you combine, inspect, search, split, extract, and clean text values in queries. Common…
- Excel SUMIF Formula Examples for Everyday TasksExcel’s SUMIF function adds values that meet one condition. With regions in A2:A7 and sales amounts in B2:B7,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.