> ## Documentation Index
> Fetch the complete documentation index at: https://cockroachlabs.mintlify.site/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 <a href={`/docs/${detectedVersion}/${normalizedPath}`} {...props}>
      {children}
    </a>;
};

[PL/pgSQL](https://www.postgresql.org/docs/16/plpgsql-overview.html) is a procedural language that you can use within <InternalLink path="user-defined-functions">user-defined functions</InternalLink>, <InternalLink path="stored-procedures">stored procedures</InternalLink>, and <InternalLink path="do">`DO`</InternalLink> blocks in CockroachDB.

In contrast to <InternalLink path="sql-statements">SQL statements</InternalLink>, 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 <InternalLink path="create-function">`CREATE FUNCTION`</InternalLink> or <InternalLink path="create-procedure">`CREATE PROCEDURE`</InternalLink> 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 <InternalLink path="create-function">`CREATE FUNCTION`</InternalLink> or <InternalLink path="create-procedure">`CREATE PROCEDURE`</InternalLink> parameters.
* A <InternalLink path="user-defined-functions">function</InternalLink> or <InternalLink path="stored-procedures">procedure</InternalLink> 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"}}
[ <<outer_block> ]
  [ DECLARE
    declarations ]
  BEGIN
    statements
    [ <<inner_block> ]
    [ 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 <InternalLink path="data-types">SQL data type</InternalLink>, <InternalLink path="create-type">user-defined type</InternalLink>, 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 <InternalLink path="select-clause">`SELECT`</InternalLink> or mutation (<InternalLink path="insert">`INSERT`</InternalLink>, <InternalLink path="update">`UPDATE`</InternalLink>, <InternalLink path="delete">`DELETE`</InternalLink>) 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 <InternalLink path="session-variables">`plpgsql_use_strict_into`</InternalLink> 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 <InternalLink path="create-type#create-a-composite-data-type">composite variable</InternalLink>.

For example, given a table `t` with `INT` column `col`:

The following <InternalLink path="stored-procedures">stored procedure</InternalLink> inserts a specified value `x` into the table, and the `INTO` clause assigns the <InternalLink path="insert#insert-and-return-values">returned value</InternalLink> 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 <InternalLink path="user-defined-functions">user-defined function</InternalLink> uses the `max` <InternalLink path="functions-and-operators#aggregate-functions">built-in function</InternalLink> 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 <InternalLink path="create-procedure#create-a-stored-procedure-that-uses-conditional-logic">Create a stored procedure that uses conditional logic</InternalLink>.

`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 <InternalLink path="create-procedure#create-a-stored-procedure-that-uses-a-while-loop">Create a stored procedure that uses a `WHILE` loop</InternalLink>.

`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
	<<label>
	BEGIN
	  EXIT label;
	  statements;
	END;
  END
```

<Note>
  If more than one PL/pgSQL block has a matching label, the innermost block is chosen.
</Note>

In the following example, the `EXIT` statement in the inner block is used to exit the stored procedure.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CREATE PROCEDURE p() AS $$
  <<outer_block>
  BEGIN
  	RAISE NOTICE '%', 'this is printed';
  	<<inner_block>
  	BEGIN
	  	EXIT outer_block;
	  	RAISE NOTICE '%', 'this is not printed';
	  END;
  END
  $$ LANGUAGE PLpgSQL;
```

#### `RETURN`

Add a `RETURN` statement to a routine with an `OUT` parameter, `RETURNS VOID` clause, or `RETURNS SETOF` clause to exit the routine immediately.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
BEGIN
	...
	RETURN;
```

Add a `RETURN` statement in a scalar-returning function to return the result of an expression.

The following example uses `RETURN` to return the square of the input argument.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CREATE FUNCTION square(x INT) RETURNS INT AS $$
BEGIN
	RETURN x * x;
END;
$$ LANGUAGE PLpgSQL;
```

#### `RETURN NEXT` and `RETURN QUERY`

Add `RETURN NEXT` or `RETURN QUERY` statements to a <InternalLink path="create-function#create-a-function-that-returns-a-set-of-results">set-returning function</InternalLink> to append rows to the result set. You can combine `RETURN NEXT` and `RETURN QUERY` statements in a single function to build the result set.

Use `RETURN NEXT` within a set-returning function to append a row to the result set.

In the following example, `RETURN NEXT` returns a new row during each loop iteration.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CREATE FUNCTION get_numbers() RETURNS TABLE (n INT) AS $$
DECLARE
	i INT := 1;
BEGIN
	WHILE i <= 5 LOOP
		n := i;
		RETURN NEXT;
		i := i + 1;
	END LOOP;
END;
$$ LANGUAGE PLpgSQL;
```

Use `RETURN QUERY` within a set-returning function to append the results of a SQL query to the result set.

In the following example, `RETURN QUERY` returns all qualifying rows from the `SELECT` query.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CREATE FUNCTION get_even_numbers() RETURNS SETOF INT AS $$
BEGIN
	RETURN QUERY
		SELECT i FROM generate_series(1, 10) AS i WHERE i % 2 = 0;
END
$$ LANGUAGE PLpgSQL;
```

#### `CONTINUE`

Add a `CONTINUE` statement to end the current iteration of a [loop](#write-loops), skipping any statements below `CONTINUE` and beginning the next iteration of the loop.

A `CONTINUE` statement can be combined with an optional `WHEN` boolean condition. In the following example, if a `WHEN` condition is defined and met, then `CONTINUE` causes the loop to skip the second group of statements and begin again.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
LOOP
	statements;
	CONTINUE [ WHEN condition ];
	statements;
  END LOOP;
```

### Open and use cursors

PL/pgSQL cursors can be used in the following scenarios:

* When [declared as variables](#declare-cursor-variables), cursors can be used within [PL/pgSQL blocks](#structure).
* When specified as a parameter in a <InternalLink path="create-procedure">`CREATE PROCEDURE`</InternalLink> statement, cursors can be accessed externally from the stored procedure.

The cursor must first be opened within a PL/pgSQL block. If the cursor was declared without being bound to a query, you must specify a query using the `FOR` clause.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
BEGIN
	OPEN cursor_name [ FOR query ];
```

After opening the cursor, you can issue a PL/pgSQL `FETCH` statement to assign the result to one or more variables.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
BEGIN
	...
	FETCH cursor_name INTO target;
```

<Note>
  In PL/pgSQL, `FETCH` returns a single row. For example, `FETCH 10` returns the 10th row.
</Note>

You can free up a cursor variable by closing the cursor:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
BEGIN
	...
	CLOSE cursor_name;
```

Cursors that are specified as parameters, rather than declared as variables, can be passed externally to and from PL/pgSQL blocks.

For example, using the <InternalLink path="movr">`movr` dataset</InternalLink> loaded by <InternalLink path="cockroach-demo">`cockroach demo`</InternalLink>:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CREATE OR REPLACE PROCEDURE get_rides(rides_cursor REFCURSOR) AS $$
  BEGIN
    OPEN rides_cursor FOR SELECT * FROM movr.rides;
  END
  $$ LANGUAGE PLpgSQL;
```

Within the same transaction that opened the cursor, use the SQL `FETCH` statement to retrieve query results for a specified number of rows:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
FETCH rows FROM cursor_name;
```

The <InternalLink path="call">`CALL`</InternalLink> and `FETCH` statements have to be issued within the same transaction, or the cursor will not be found:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
BEGIN;
  CALL get_rides('rides');
  FETCH 2 FROM rides;
  COMMIT;
```

```
                   id                  |   city    | vehicle_city |               rider_id               |              vehicle_id              |         start_address         |         end_address         |     start_time      |      end_time       | revenue
---------------------------------------+-----------+--------------+--------------------------------------+--------------------------------------+-------------------------------+-----------------------------+---------------------+---------------------+----------
  ab020c49-ba5e-4800-8000-00000000014e | amsterdam | amsterdam    | b3333333-3333-4000-8000-000000000023 | bbbbbbbb-bbbb-4800-8000-00000000000b | 58875 Bell Ports              | 50164 William Glens         | 2018-12-16 03:04:05 | 2018-12-17 20:04:05 |   13.00
  ab851eb8-51eb-4800-8000-00000000014f | amsterdam | amsterdam    | ae147ae1-47ae-4800-8000-000000000022 | bbbbbbbb-bbbb-4800-8000-00000000000b | 62025 Welch Alley             | 4092 Timothy Creek Apt. 39  | 2018-12-31 03:04:05 | 2019-01-02 03:04:05 |   32.00
```

### Report messages and handle exceptions

Use the `RAISE` statement to print messages for status or error reporting.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
RAISE level 'message' [, expressions ]
  [ USING option = 'expression' [, ... ] ];
```

<Note>
  `RAISE` messages the client directly, and does not currently produce log output.
</Note>

* `level` is the message severity. Possible values are `DEBUG`, `LOG`, `NOTICE`, `INFO`, `WARNING`, and `EXCEPTION`. Specify `EXCEPTION` to raise an error that aborts the current transaction.

* `message` is a message string to display.

* `expressions` is an optional, comma-separated list of [expressions](https://www.postgresql.org/docs/16/plpgsql-expressions.html) that provide values to replace any `%` placed within the message string. The number of expressions must match the number of `%` placeholders.

* `option` is a type of additional information to include. Possible values are `MESSAGE`, `DETAIL`, `HINT`, or `ERRCODE`. To specify `MESSAGE`, use the following alternate syntax:

  ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  RAISE level USING MESSAGE = 'message';
  ```

* `expression` is an expression to display that corresponds to the specified `option`. If `ERRCODE` is the specified option, this must be a valid [`SQLSTATE` error code or name](https://www.postgresql.org/docs/16/errcodes-appendix.html).

For example:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CREATE OR REPLACE PROCEDURE raise_time() AS $$
  BEGIN
    RAISE NOTICE 'current timestamp: %', now()
	USING HINT = 'Call this procedure again for a different result';
  END
  $$ LANGUAGE PLpgSQL;
```

```
CALL raise_time();
```

```
NOTICE: current timestamp: 2024-01-05 23:09:08.0601+00
HINT: Call this procedure again for a different result
CALL
```

#### Write exception logic

Use an `EXCEPTION` statement to catch and handle specified errors.

Any valid [`SQLSTATE` error code or name](https://www.postgresql.org/docs/16/errcodes-appendix.html) can be specified, except for Class 40 (transaction rollback) errors. Arbitrary user-defined `SQLSTATE` codes can also be specified.

If a specified error is caught, the exception handling statements are executed. Any unspecified errors are caught by `WHEN OTHERS`, except for `query_canceled` and `assert_failure`.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
EXCEPTION
	WHEN error THEN
		handle_exception;
	[ WHEN error_n THEN
		handle_exception_n; ]
	[ WHEN OTHERS THEN
		handle_other_exceptions; ]
```

`EXCEPTION` logic is included after the main body of a PL/pgSQL block. For example:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
BEGIN
	...
  EXCEPTION
    WHEN not_null_violation THEN
      RETURN 'not_null_violation';
    WHEN OTHERS THEN
      RETURN others;
  END
```

`WHEN` 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"}}
EXCEPTION
	WHEN error THEN
		NULL;
```

### Control transactions

Use a `COMMIT` or `ROLLBACK` statement within a PL/pgSQL <InternalLink path="stored-procedures">stored procedure</InternalLink> to finish the current transaction and automatically start a new one.

* Any updates made within the previous transaction are either committed or rolled back, while [PL/pgSQL variables](#declare-a-variable) keep their values.
* Execution of the stored procedure resumes in a new transaction with the statements immediately following the `COMMIT` or `ROLLBACK` statement.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
BEGIN
	statements
	[ COMMIT | ROLLBACK ]
```

`COMMIT` and `ROLLBACK` statements within PL/pgSQL blocks have the following requirements:

* They must be used inside a PL/pgSQL <InternalLink path="stored-procedures">stored procedure</InternalLink>.

* They cannot be inside a [block](#structure) with an [`EXCEPTION`](#write-exception-logic) clause.

* The procedure must be called directly in a <InternalLink path="call">`CALL`</InternalLink> SQL statement. It cannot be [called by another stored procedure](#call-a-procedure).

* The procedure must be called from an <InternalLink path="transactions#individual-statements">implicit transaction</InternalLink>; i.e., the call cannot be enclosed by <InternalLink path="begin-transaction">`BEGIN`</InternalLink> and <InternalLink path="commit-transaction">`COMMIT`</InternalLink> SQL statements.

Statements that follow a `COMMIT` or `ROLLBACK` automatically start another PL/pgSQL transaction. If a transaction is running when the procedure ends, it is implicitly committed without having to be followed by a `COMMIT`.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
BEGIN
	...
	[ COMMIT | ROLLBACK ]
	statements
  END
```

Use one or more optional `SET TRANSACTION` statements to set the <InternalLink path="set-transaction#parameters">priority, isolation level, timestamp, or read-only status</InternalLink> of a PL/pgSQL transaction. In PL/pgSQL, `SET TRANSACTION` statements **must** directly follow `COMMIT` or `ROLLBACK`, or another `SET TRANSACTION` statement; and must precede the other statements in the transaction.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
BEGIN
	...
	[ COMMIT | ROLLBACK ]
	[ SET TRANSACTION mode ]
	statements
```

For example:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CREATE PROCEDURE p() LANGUAGE PLpgSQL AS
  $$
  BEGIN
    COMMIT;
    SET TRANSACTION PRIORITY HIGH;
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
    RAISE NOTICE '%', current_setting('transaction_isolation');
    RAISE NOTICE '%', current_setting('transaction_priority');
  END
  $$;
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CALL p();
```

```
NOTICE: read committed
NOTICE: high
CALL
```

Any PL/pgSQL transaction not preceded by a `SET TRANSACTION` statement uses the default settings.

### Call a procedure

Use a `CALL` statement to call a procedure from within a PL/pgSQL <InternalLink path="user-defined-functions">function</InternalLink> or <InternalLink path="stored-procedures">procedure</InternalLink>.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
BEGIN
	CALL procedure(parameters);
```

A PL/pgSQL routine that calls a procedure should [declare a variable](#declare-a-variable) that will store the result of each of that procedure's `OUT` parameters. For example, given the procedure:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CREATE OR REPLACE PROCEDURE output_one(OUT value INT) AS
  $$
  BEGIN
    value := 1;
  END
  $$ LANGUAGE PLpgSQL;
```

To call `output_one` within another procedure:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CREATE OR REPLACE PROCEDURE output() AS
  $$
  DECLARE
    output_value INT;
  BEGIN
    CALL output_one(output_value);
    RAISE NOTICE 'Output value: %', output_value;
  END
  $$ LANGUAGE PLpgSQL;
```

A procedure with `OUT` parameters can only be called from a PL/pgSQL routine. For another example, see <InternalLink path="create-procedure#create-a-stored-procedure-that-calls-a-procedure">Create a stored procedure that calls a procedure</InternalLink>.

## Examples

### Create a user-defined function using PL/pgSQL

The following <InternalLink path="user-defined-functions">user-defined function</InternalLink>user-defined function returns the `n`th integer in the Fibonacci sequence.

It uses the PL/pgSQL<InternalLink path="plpgsql">PL/pgSQL</InternalLink> <InternalLink path="plpgsql#write-loops">`LOOP`</InternalLink> syntax to iterate through a simple calculation, and <InternalLink path="plpgsql#report-messages-and-handle-exceptions">`RAISE EXCEPTION`</InternalLink> to return an error message if the specified `n` is negative.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CREATE FUNCTION fib(n int) RETURNS INT AS $$
	DECLARE
		tmp INT;
		a INT := 0;
		b INT := 1;
		i INT := 2;
	BEGIN
		IF n < 0 THEN
			RAISE EXCEPTION 'n must be non-negative';
		END IF;
		IF n = 0 THEN RETURN 0; END IF;
		IF n = 1 THEN RETURN 1; END IF;
		LOOP
			IF i > n THEN EXIT; END IF;
			tmp := a + b;
			a := b;
			b := tmp;
			i := i + 1;
		END LOOP;
		RETURN b;
	END
  $$ LANGUAGE PLpgSQL;
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SELECT fib(8);
```

```
  fib
-------
   21
```

### Create a stored procedure using PL/pgSQL

#### Setup

To follow along, run <InternalLink path="cockroach-demo">`cockroach demo`</InternalLink> to start a temporary, in-memory cluster with the <InternalLink path="movr">`movr`</InternalLink> sample dataset preloaded:

```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
$ cockroach demo
```

The following stored procedure<InternalLink path="user-defined-functions">stored procedure</InternalLink> removes a specified number of earliest rides in `vehicle_location_histories`.

It uses the PL/pgSQL<InternalLink path="plpgsql">PL/pgSQL</InternalLink> <InternalLink path="plpgsql#write-loops">`WHILE`</InternalLink> syntax to iterate through the rows, \[`RAISE`] to return notice and error messages, and `REFCURSOR` to define a cursor that fetches the next rows to be affected by the procedure.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CREATE OR REPLACE PROCEDURE delete_earliest_histories (
	num_deletions INT, remaining_histories REFCURSOR
)
LANGUAGE PLpgSQL
AS $$
DECLARE
	counter INT := 0;
	deleted_timestamp TIMESTAMP;
	deleted_ride_id UUID;
	latest_timestamp TIMESTAMP;
BEGIN
	-- Raise an exception if the table has fewer rows than the number to delete
	IF (SELECT COUNT(*) FROM vehicle_location_histories) < num_deletions THEN
	    RAISE EXCEPTION 'Only % row(s) in vehicle_location_histories',
	    (SELECT count(*) FROM vehicle_location_histories)::STRING;
	END IF;

	-- Delete 1 row with each loop iteration, and report its timestamp and ride ID
	WHILE counter < num_deletions LOOP
		DELETE FROM vehicle_location_histories
		WHERE timestamp IN (
			SELECT timestamp FROM vehicle_location_histories
			ORDER BY timestamp
			LIMIT 1
		)
		RETURNING ride_id, timestamp INTO deleted_ride_id, deleted_timestamp;

		-- Report each row deleted
		RAISE NOTICE 'Deleted ride % with timestamp %', deleted_ride_id, deleted_timestamp;

		counter := counter + 1;
	END LOOP;

	-- Open a cursor for the remaining rows in the table
	OPEN remaining_histories FOR SELECT * FROM vehicle_location_histories ORDER BY timestamp;
END;
$$;
```

Open a <InternalLink path="transactions">transaction</InternalLink>:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
BEGIN;
```

Call the stored procedure, specifying 5 rows to delete and a `rides_left` cursor name:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CALL delete_earliest_histories (5, 'rides_left');
```

```
NOTICE: Deleted ride 0a3d70a3-d70a-4d80-8000-000000000014 with timestamp 2019-01-02 03:04:05
NOTICE: Deleted ride 0b439581-0624-4d00-8000-000000000016 with timestamp 2019-01-02 03:04:05.001
NOTICE: Deleted ride 09ba5e35-3f7c-4d80-8000-000000000013 with timestamp 2019-01-02 03:04:05.002
NOTICE: Deleted ride 0fdf3b64-5a1c-4c00-8000-00000000001f with timestamp 2019-01-02 03:04:05.003
NOTICE: Deleted ride 049ba5e3-53f7-4ec0-8000-000000000009 with timestamp 2019-01-02 03:04:05.004
CALL
```

Use the cursor to fetch the 3 earliest remaining rows in `vehicle_location_histories`:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
FETCH 3 from rides_left;
```

```
    city   |               ride_id                |        timestamp        | lat | long
-----------+--------------------------------------+-------------------------+-----+-------
  new york | 0c49ba5e-353f-4d00-8000-000000000018 | 2019-01-02 03:04:05.005 |  -88 |  -83
  new york | 0083126e-978d-4fe0-8000-000000000001 | 2019-01-02 03:04:05.006 |  170 |  -16
  new york | 049ba5e3-53f7-4ec0-8000-000000000009 | 2019-01-02 03:04:05.007 | -149 |   63
```

If the procedure is called again, these rows will be the first 3 to be deleted.

<a id="locality-example" />

#### Example details

The example works as follows:

<InternalLink path="create-procedure">`CREATE PROCEDURE`</InternalLink> defines a stored procedure called `delete_earliest_histories` with an `INT` and a `REFCURSOR` parameter.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CREATE OR REPLACE PROCEDURE delete_earliest_histories (
	num_deletions INT, remaining_histories REFCURSOR
  )
```

`LANGUAGE` specifies <InternalLink path="plpgsql">PL/pgSQL</InternalLink> as the language for the stored procedure.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
LANGUAGE PLpgSQL
```

`DECLARE` specifies the <InternalLink path="plpgsql#declare-a-variable">PL/pgSQL variable definitions</InternalLink> that are used in the procedure body.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
DECLARE
	counter INT := 0;
	deleted_timestamp TIMESTAMP;
	deleted_ride_id UUID;
	latest_timestamp TIMESTAMP;
```

`BEGIN` and `END` <InternalLink path="plpgsql#structure">group the PL/pgSQL statements</InternalLink> in the procedure body.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
BEGIN
  ...
  END
```

The following <InternalLink path="plpgsql#write-conditional-statements">`IF ... THEN`</InternalLink> statement <InternalLink path="plpgsql#report-messages-and-handle-exceptions">raises an exception</InternalLink> if `vehicle_location_histories` has fewer rows than the number specified with `num_deletions`. If the exception is raised within an open transaction, the transaction will abort.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
IF (SELECT COUNT(*) FROM vehicle_location_histories) < num_deletions THEN
	RAISE EXCEPTION 'Only % row(s) in vehicle_location_histories', (SELECT count(*) FROM vehicle_location_histories)::STRING;
  END IF;
```

The following <InternalLink path="plpgsql#write-loops">`WHILE`</InternalLink> loop deletes rows iteratively from `vehicle_location_histories`, stopping when the number of loops reaches the `num_deletions` value.

The `DELETE ... RETURNING ... INTO` statement assigns column values from each deleted row into separate variables. For more information about assigning variables, see <InternalLink path="plpgsql#assign-a-result-to-a-variable">Assign a result to a variable</InternalLink>.

Finally, the <InternalLink path="plpgsql#report-messages-and-handle-exceptions">`RAISE NOTICE`</InternalLink> statement reports these values for each deleted row.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
WHILE counter < num_deletions LOOP
	DELETE FROM vehicle_location_histories
	WHERE timestamp IN (
  	SELECT timestamp FROM vehicle_location_histories
  	ORDER BY timestamp
  	LIMIT 1
	)
	RETURNING ride_id, timestamp INTO deleted_ride_id, deleted_timestamp;
	RAISE NOTICE 'Deleted ride % with timestamp %', deleted_ride_id, deleted_timestamp;
	counter := counter + 1;
  END LOOP;
```

The `OPEN` statement <InternalLink path="plpgsql#open-and-use-cursors">opens a cursor</InternalLink> for all remaining rows in `vehicle_location_histories`, sorted by timestamp. After calling the procedure in an open transaction, the cursor can be used to fetch rows from the table.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
OPEN remaining_histories FOR SELECT * FROM vehicle_location_histories ORDER BY timestamp;
```

For more details on this example, see the <InternalLink path="stored-procedures#example-details">Stored Procedures documentation</InternalLink>.

## Known limitations

* It is not possible to use a variable as a target more than once in the same `INTO` clause. For example, `SELECT 1, 2 INTO x, x;`.
* PLpgSQL variable declarations cannot inherit the type of a table row or column using `%TYPE` or `%ROWTYPE` syntax.
* PL/pgSQL arguments cannot be referenced with ordinals (e.g., `$1`, `$2`).
* The following statements are not supported:
  * `FOR` cursor loops, `FOR` query loops, and `FOREACH` loops.
  * `PERFORM`, `EXECUTE`, `GET DIAGNOSTICS`, and `CASE`.
* PL/pgSQL exception blocks cannot catch <InternalLink path="transaction-retry-error-reference">transaction retry errors</InternalLink>.
* `RAISE` statements cannot be annotated with names of schema objects related to the error (i.e., using `COLUMN`, `CONSTRAINT`, `DATATYPE`, `TABLE`, or `SCHEMA`).
* `RAISE` statements message the client directly, and do not produce log output.
* `ASSERT` debugging checks are not supported.
* `RECORD` parameters and variables are not supported in <InternalLink path="user-defined-functions">user-defined functions</InternalLink>.
* Variable shadowing (e.g., declaring a variable with the same name in an inner block) is not supported in PL/pgSQL.
* Syntax for accessing members of composite types without parentheses is not supported.
* `NOT NULL` variable declarations are not supported.
* Cursors opened in PL/pgSQL execute their queries on opening, affecting performance and resource usage.
* Cursors in PL/pgSQL cannot be declared with arguments.
* `OPEN FOR EXECUTE` is not supported for opening cursors.
* The `print_strict_params` option is not supported in PL/pgSQL.
* The `FOUND` local variable, which checks whether a statement affected any rows, is not supported in PL/pgSQL.
* By default, when a PL/pgSQL variable conflicts with a column name, CockroachDB resolves the ambiguity by treating it as a column reference rather than a variable reference. This behavior differs from PostgreSQL, where an ambiguous column error is reported, and it is possible to change the `plpgsql.variable_conflict` setting in order to prefer either columns or variables.
* It is not possible to define a `RECORD`-returning PL/pgSQL function that returns different-typed expressions from different `RETURN` statements. CockroachDB requires a consistent return type for `RECORD`-returning functions.
* Variables cannot be declared with an associated collation using the `COLLATE` keyword.
* Variables cannot be accessed using the `label.var_name` pattern.

## See also

* <InternalLink path="stored-procedures">Stored procedures</InternalLink>
* <InternalLink path="user-defined-functions">User-defined functions</InternalLink>
* <InternalLink path="create-procedure">`CREATE PROCEDURE`</InternalLink>