> ## 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) is a procedural language that you can use within <InternalLink path="user-defined-functions">user-defined functions</InternalLink> and <InternalLink path="stored-procedures">stored procedures</InternalLink> 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 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) 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) 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);
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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();
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  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>.

### 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, `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 or `VOID` return type to exit the routine immediately.

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

#### `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;
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
                   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) 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).

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;
```

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

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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) 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();
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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> returns the `n`th integer in the Fibonacci sequence.

It uses the PL/pgSQL <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);
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  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 <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#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');
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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;
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
    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.

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` loops, including `FOR` cursor loops, `FOR` query loops, and `FOREACH` loops.
  * `RETURN NEXT` and `RETURN QUERY`.
  * `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>