> ## 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 InlineImage = ({src, alt = "", height = "1.6em"}) => {
  return <img noZoom src={src} alt={alt} style={{
    display: "inline",
    verticalAlign: "start",
    height: height,
    margin: "0"
  }} />;
};

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

<InlineImage alt="Megaphone" src="/images/common/icon-megaphone.png" /> New in v23.2: [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
```

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 the function or procedure body.

```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) that provides an optional default value for the variable.

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:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SELECT expression INTO target FROM ...;
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
[ INSERT | UPDATE | DELETE ] ... RETURNING expression INTO 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;
```

For usage examples of conditional statements, see [Examples](#examples).

### Write loops

Use looping syntax 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>.

Add an `EXIT` statement to end a `LOOP` or `WHILE` statement block. This should be combined with a [conditional statement](#write-conditional-statements).

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
LOOP
    statements;
    IF condition THEN
        EXIT;
    END IF;
  END LOOP;
```

Add a `CONTINUE` statement to end a `LOOP` or `WHILE` statement block, skipping any statements below `CONTINUE`, and begin the next iteration of the loop. This should be combined with a [conditional statement](#write-conditional-statements). In the following example, if the `IF` condition is met, then `CONTINUE` causes the loop to skip the second block of statements and begin again.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
LOOP
    statements;
    IF condition THEN
        CONTINUE;
    END IF;
    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
```

## 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`](#report-messages-and-handle-exceptions) 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

* PL/pgSQL blocks cannot be nested.
* Cursors used in PL/pgSQL execute their queries on opening. This can affect performance and resource usage.
* Cursors cannot be declared with parameters.
* `RECORD` and `ROW` -type variables cannot be declared in PL/pgSQL.
* `NOT NULL` variables cannot be declared in PL/pgSQL.
* PL/pgSQL arguments cannot be referenced with ordinals (e.g., `$1`, `$2` ).
* PL/pgSQL `EXCEPTION` blocks cannot catch <InternalLink path="transaction-retry-error-reference">transaction retry errors</InternalLink>.
* `FOR` loops (including `FOR` cursor loops and `FOR` query loops) and `FOREACH` loops are not supported.
* `RETURN NEXT` and `RETURN QUERY` statements are not supported.
* `CASE` statements are not supported.
* `EXIT` and `CONTINUE` labels and conditions are not supported.
* Variable shadowing (e.g., declaring a variable with the same name in an inner block) is not supported in PL/pgSQL.
* When using the `RAISE` statement, schema objects related to the error cannot be named using `COLUMN`, `CONSTRAINT`, `DATATYPE`, `TABLE`, and `SCHEMA`.
* The `INTO` statement in PL/pgSQL does not support the `STRICT` option.
* `PERFORM`, `EXECUTE`, `GET DIAGNOSTICS`, and `NULL` statements are not supported for PL/pgSQL.

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