> ## Documentation Index
> Fetch the complete documentation index at: https://www.cockroachlabs.com/llms.txt
> Use this file to discover all available pages before exploring further.
# PL/pgSQL
export const InternalLink = ({version, path = "", children, ...props}) => {
let detectedVersion = version || "stable";
if (typeof window !== 'undefined' && !version) {
const match = window.location.pathname.match(/\/docs\/([^/]+)/);
if (match) {
detectedVersion = match[1];
}
}
const normalizedPath = path.startsWith("/") ? path.slice(1) : path;
return
{children}
;
};
[PL/pgSQL](https://www.postgresql.org/docs/16/plpgsql-overview.html) is a procedural language that you can use within user-defined functions, stored procedures, and `DO` blocks in CockroachDB.
In contrast to SQL statements, which are issued one-by-one from the client to the database, PL/pgSQL statements are encapsulated in a [block structure](#structure) and executed on the database side, thus reducing network latency. PL/pgSQL enables more complex functionality than standard SQL, including [conditional statements](#write-conditional-statements), [loops](#write-loops), and [exception handling](#report-messages-and-handle-exceptions).
This page describes PL/pgSQL [structure](#structure) and [syntax](#syntax), and includes [examples](#examples) of functions and procedures that use PL/pgSQL.
## Structure
A function or procedure that uses PL/pgSQL must specify the `PLpgSQL` language within the `CREATE FUNCTION` or `CREATE PROCEDURE` statement:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CREATE [ PROCEDURE | FUNCTION ] ...
LANGUAGE PLpgSQL
...
```
PL/pgSQL is block-structured. A block contains the following:
* An optional `DECLARE` section that contains [variable declarations](#declare-a-variable) for all variables that are used within the block and are not defined as `CREATE FUNCTION` or `CREATE PROCEDURE` parameters.
* A function or procedure body, consisting of statements enclosed by `BEGIN` and `END`.
* An optional `EXCEPTION` section for [catching and handling `SQLSTATE` errors](#write-exception-logic).
At the highest level, a PL/pgSQL block looks like the following:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
[ DECLARE
declarations ]
BEGIN
statements
END
```
PL/pgSQL blocks can be nested. An optional label can be placed above each block. Block labels can be targeted by [`EXIT` statements](#exit).
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
[ < ]
[ DECLARE
declarations ]
BEGIN
statements
[ < ]
[ DECLARE
declarations ]
BEGIN
statements
END;
END
```
When you create a function or procedure, you can enclose the entire PL/pgSQL block in dollar quotes (`$$`). Dollar quotes are not required, but are easier to use than single quotes, which require that you escape other single quotes that are within the function or procedure body.
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CREATE PROCEDURE name(parameters)
LANGUAGE PLpgSQL
AS $$
[ DECLARE
declarations ]
BEGIN
statements
END
$$;
```
For complete examples, see [Create a user-defined function using PL/pgSQL](#create-a-user-defined-function-using-pl/pgsql) and [Create a stored procedure using PL/pgSQL](#create-a-stored-procedure-using-pl/pgsql).
## Syntax
### Declare a variable
`DECLARE` specifies all variable definitions that are used in a block.
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
DECLARE
variable_name [ CONSTANT ] data_type [ := expression ];
```
* `variable_name` is an arbitrary variable name.
* `data_type` can be a supported SQL data type, user-defined type, or the PL/pgSQL `REFCURSOR` type, when declaring [cursor](#declare-cursor-variables) variables.
* `CONSTANT` specifies that the variable cannot be [reassigned](#assign-a-result-to-a-variable), ensuring that its value remains constant within the block.
* `expression` is an [expression](https://www.postgresql.org/docs/16/plpgsql-expressions.html) that provides an optional default value for the variable. Default values are evaluated every time a block is entered in a function or procedure.
For example:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
DECLARE
a VARCHAR;
b INT := 0;
```
#### Declare cursor variables
A *cursor* encapsulates a selection query and is used to fetch the query results for a subset of rows.
You can declare *forward-only* cursors as variables to be used within [PL/pgSQL blocks](#structure). These must have the PL/pgSQL `REFCURSOR` data type. For example:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
DECLARE
c REFCURSOR;
```
You can bind a cursor to a selection query within the declaration. Use the `CURSOR FOR` syntax and specify the query:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
DECLARE
c CURSOR FOR query;
```
Note that the preceding cursor still has the `REFCURSOR` data type.
For information about opening and using cursors, see [Open and use cursors](#open-and-use-cursors).
### Assign a result to a variable
Use the PL/pgSQL `INTO` clause to assign a result of a `SELECT` or mutation (`INSERT`, `UPDATE`, `DELETE`) statement to a specified variable. The optional `STRICT` clause specifies that the statement must return exactly one row; otherwise, the function or procedure will error. This behavior can be enabled by default using the `plpgsql_use_strict_into` session setting.
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SELECT expression INTO [ STRICT ] target FROM ...;
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
[ INSERT | UPDATE | DELETE ] ... RETURNING expression INTO [ STRICT ] target;
```
* `expression` is an [expression](https://www.postgresql.org/docs/16/plpgsql-expressions.html) that defines the result to be assigned to the variable.
* `target` is an arbitrary variable name. This can be a list of comma-separated variables, or a single composite variable.
For example, given a table `t` with `INT` column `col`:
The following stored procedure inserts a specified value `x` into the table, and the `INTO` clause assigns the returned value to `i`.
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CREATE OR REPLACE PROCEDURE p(x INT) AS $$
DECLARE
i INT;
BEGIN
INSERT INTO t (col) VALUES (x) RETURNING col INTO i;
RAISE NOTICE 'New Row: %', i;
END
$$ LANGUAGE PLpgSQL;
```
When the procedure is called, it inserts the specified integer into a new row in the table, and prints a [`NOTICE`](#report-messages-and-handle-exceptions) message that contains the inserted value:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CALL p(2);
```
```
NOTICE: New Row: 2
CALL
```
The following user-defined function uses the `max` built-in function to find the maximum `col` value in table `t`, and assigns the result to `i`.
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CREATE OR REPLACE FUNCTION f() RETURNS INT AS $$
DECLARE
i INT;
BEGIN
SELECT max(col) INTO i FROM t;
RETURN i;
END
$$ LANGUAGE PLpgSQL;
```
When the function is invoked, it displays the maximum value that was inserted into the table:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SELECT f();
```
```
f
-----
2
```
For a more extensive example of variable assignment, see [Create a stored procedure using PL/pgSQL](#create-a-stored-procedure-using-pl/pgsql).
### Write conditional statements
Use `IF` syntax to execute statements conditionally. PL/pgSQL understands several forms of `IF` statements.
`IF ... THEN` executes statements only if a boolean condition is true.
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
IF condition THEN
statements;
END IF;
```
For an example, see Create a stored procedure that uses conditional logic.
`IF ... THEN ... ELSE` executes statements if a boolean condition is true. If the condition is false, the `ELSE` statements are executed.
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
IF condition THEN
statements;
ELSE
else_statements;
END IF;
```
`IF ... THEN ... ELSIF` executes statements if a boolean condition is true. If the condition is false, each `ELSIF` condition is evaluated until one is true. The corresponding `ELSIF` statements are executed. If no `ELSIF` conditions are true, no statements are executed unless an `ELSE` clause is included, in which case the `ELSE` statements are executed.
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
IF condition THEN
statements;
ELSIF elsif_condition THEN
elsif_statements;
[ ELSIF elsif_condition_n THEN
elsif_statements_n; ]
[ ELSE
else_statements; ]
END IF;
```
`IF`, `ELSE`, and `ELSIF` conditions are not required to execute statements. You can exclude any statements or add a placeholder `NULL` statement.
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
IF condition THEN
NULL;
END IF;
```
For usage examples of conditional statements, see [Examples](#examples).
### Write loops
Write a loop to repeatedly execute statements.
On its own, `LOOP` executes statements infinitely.
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
LOOP
statements;
END LOOP;
```
On its own, `WHILE` executes statements infinitely if a boolean condition is true. The statements repeat until the condition is false.
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
WHILE condition LOOP
statements;
END LOOP;
```
For an example, see Create a stored procedure that uses a `WHILE` loop.
`FOR ... IN` iterates a loop over a range of integer values. Specify a variable name to iterate over the minimum and maximum values of the integer range. The optional `BY` clause is used to specify an integer step value, and `REVERSE` causes the loop to subtract the step value across iterations.
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
FOR variable_name IN [ REVERSE ] minimum .. maximum [ BY step ]
LOOP
statements;
END LOOP;
```
In the following example, the `FOR` loop iterates from `1` to `10` in steps of `2`:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CREATE OR REPLACE PROCEDURE p() LANGUAGE PLPGSQL AS $$
BEGIN
FOR i IN 1..10 BY 2
LOOP
RAISE NOTICE '%', i;
END LOOP;
END $$;
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CALL p();
```
```
NOTICE: 1
NOTICE: 3
NOTICE: 5
NOTICE: 7
NOTICE: 9
CALL
```
### Control execution flow
#### `EXIT`
Add an `EXIT` statement to end a [loop](#write-loops). An `EXIT` statement can be combined with an optional `WHEN` boolean condition.
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
LOOP
statements;
EXIT [ WHEN condition ];
END LOOP;
```
Add a label to an `EXIT` statement to target a block that has a matching label. An `EXIT` statement with a label can target either a loop or a [block](#structure). An `EXIT` statement inside a block must have a label.
The following `EXIT` statement will end the `label` block before the statements are executed.
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
BEGIN
<