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

# Stored Procedures

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

A *stored procedure* is a database object consisting of <InternalLink path="plpgsql">PL/pgSQL</InternalLink> or <InternalLink path="sql-statements">SQL</InternalLink> statements that can be issued with a single <InternalLink path="call">`CALL`</InternalLink> statement. This allows complex logic to be executed repeatedly within the database, which can improve performance and mitigate security risks.

Both stored procedures and <InternalLink path="user-defined-functions">user-defined functions</InternalLink> are types of *routines*. However, they differ in the following ways:

* Functions return a value, and procedures do not return a value.
* Procedures must be invoked using a <InternalLink path="call">`CALL`</InternalLink> statement. Functions can be invoked in nearly any context, such as `SELECT`, `FROM`, and `WHERE` clauses, <InternalLink path="default-value">`DEFAULT`</InternalLink> expressions, and <InternalLink path="computed-columns">computed column</InternalLink> expressions.
* Functions have <InternalLink path="functions-and-operators#function-volatility">volatility</InternalLink> settings, and procedures do not.

## Structure

A stored procedure consists of a name, optional parameters, language, and procedure body.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CREATE PROCEDURE procedure_name(parameters)
  LANGUAGE procedure_language
  AS procedure_body
```

* Each parameter 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 <InternalLink path="plpgsql#declare-cursor-variables">declaring PL/pgSQL cursor variables</InternalLink>.
* CockroachDB supports the `IN` (default), `OUT`, and `INOUT` modes for parameters. For an example, see <InternalLink path="create-procedure#create-a-stored-procedure-that-uses-out-and-inout-parameters">Create a procedure that uses `OUT` and `INOUT` parameters</InternalLink>.
* `LANGUAGE` specifies the language of the function body. CockroachDB supports the languages <InternalLink path="sql-statements">`SQL`</InternalLink> and <InternalLink path="plpgsql">`PLpgSQL`</InternalLink>.
* The procedure body:
  * Can be enclosed in single or dollar ( `$$` ) quotes. Dollar quotes are easier to use than single quotes, which require that you escape other single quotes that are within the procedure body.
  * Must conform to a <InternalLink path="plpgsql#structure">block structure</InternalLink> if written in PL/pgSQL.

For details, see <InternalLink path="create-procedure">`CREATE PROCEDURE`</InternalLink>.

## Examples

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

For more examples of stored procedure creation, see <InternalLink path="create-procedure#examples">`CREATE PROCEDURE`</InternalLink>.

### Create a stored procedure using PL/pgSQL

The following stored procedure removes a specified number of earliest rides in `vehicle_location_histories`.

It uses the <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');
```

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

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

### Alter a stored procedure

The following statement renames the <InternalLink path="stored-procedures">`delete_earliest_histories` example procedure</InternalLink> to `delete_histories`:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
ALTER PROCEDURE delete_earliest_histories RENAME TO delete_histories;
```

## Known limitations

Stored procedures have the following limitations:

* `COMMIT` and `ROLLBACK` statements are not supported within nested procedures.
* Routines cannot be invoked with named arguments, e.g., `SELECT foo(a => 1, b => 2);` or `SELECT foo(b:= 1, a:= 2);`.
* Routines cannot be created if they reference temporary tables.
* Routines cannot be created with unnamed `INOUT` parameters. For example, `CREATE PROCEDURE p(INOUT INT) AS $$ BEGIN NULL; END; $$ LANGUAGE PLpgSQL;`.
* Routines cannot be created if they return fewer columns than declared. For example, `CREATE FUNCTION f(OUT sum INT, INOUT a INT, INOUT b INT) LANGUAGE SQL AS $$ SELECT (a + b, b); $$;`.
* Routines cannot be created with an `OUT` parameter of type `RECORD`.
* DDL statements (e.g., `CREATE TABLE`, `CREATE INDEX`) are not allowed within UDFs or stored procedures.
* Polymorphic types cannot be cast to other types (e.g., `TEXT`) within routine parameters.

Also refer to the <InternalLink path="plpgsql#known-limitations">PL/pgSQL known limitations</InternalLink>.

## See also

* <InternalLink path="plpgsql">PL/pgSQL</InternalLink>
* <InternalLink path="create-procedure">`CREATE PROCEDURE`</InternalLink>
* <InternalLink path="call">`CALL`</InternalLink>
* <InternalLink path="alter-procedure">`ALTER PROCEDURE`</InternalLink>
* <InternalLink path="drop-procedure">`DROP PROCEDURE`</InternalLink>