> ## 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
{children}
;
};
A *stored procedure* is a database object consisting of PL/pgSQL or SQL statements that can be issued with a single `CALL` statement. This allows complex logic to be executed repeatedly within the database, which can improve performance and mitigate security risks.
Both stored proceduresstored procedures and user-defined functionsuser-defined functions 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 `CALL` statement. Functions can be invoked in nearly any context, such as `SELECT`, `FROM`, and `WHERE` clauses, `DEFAULT` expressions, and computed column expressions.
* Functions have volatility 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 SQL data type, user-defined type, or the PL/pgSQL `REFCURSOR` type, when declaring PL/pgSQL cursor variables.
* CockroachDB supports the `IN` (default), `OUT`, and `INOUT` modes for parameters. For an example, see Create a procedure that uses `OUT` and `INOUT` parameters.
* `LANGUAGE` specifies the language of the function body. CockroachDB supports the languages `SQL` and `PLpgSQL`.
* 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 block structure if written in PL/pgSQL.
For details, see `CREATE PROCEDURE`.
## Statement statistics
SQL statements executed within stored procedures are tracked in the SQL statistics subsystem and will appear in the **SQL Activity** > **Statements** page and the **Insights** page in the DB Console. This allows you to monitor the performance and execution statistics of individual statements within your procedures.
When the stored procedure is invoked as part of a transaction, the statements executed within the procedure will also appear in the **Transaction details** in the **Statement Fingerprints** table.
Statement diagnostics cannot be collected for statements executed inside stored procedures. You can request statement diagnostics for the top-level invocation of the procedure, and the resulting trace includes spans for each statement executed. However, there is no way to target statements executed inside the procedure with a statement diagnostics request. For details, refer to [Known limitations](#known-limitations).
## Examples
#### Setup
To follow along, run `cockroach demo` to start a temporary, in-memory cluster with the `movr` sample dataset preloaded:
```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
$ cockroach demo
```
For more examples of stored procedure creation, see `CREATE PROCEDURE`.
### Create a stored procedure using PL/pgSQL
The following stored procedurestored procedure removes a specified number of earliest rides in `vehicle_location_histories`.
It uses the PL/pgSQLPL/pgSQL `WHILE` 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 transaction:
```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.
#### Example details
The example works as follows:
`CREATE PROCEDURE` 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 PL/pgSQL as the language for the stored procedure.
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
LANGUAGE PLpgSQL
```
`DECLARE` specifies the PL/pgSQL variable definitions 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` group the PL/pgSQL statements in the procedure body.
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
BEGIN
...
END
```
The following `IF ... THEN` statement raises an exception 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 `WHILE` 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 Assign a result to a variable.
Finally, the `RAISE NOTICE` 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 opens a cursor 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 Stored Procedures documentation.
### Alter a stored procedure
The following statement renames the `delete_earliest_histories` example procedure 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:
* Pausable portals are not supported with `CALL` statements for stored procedures.
* `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.
* Routine parameters and return types cannot be declared using the `ANYENUM` polymorphic type, which is able to match any `ENUM` type.
* Statement diagnostics cannot be collected for statements executed inside UDFs or stored procedures. You can request statement diagnostics for the top-level invocation of the function or procedure, and the resulting trace includes spans for each statement executed. However, there is no way to target statements executed inside the function or procedure with a statement diagnostics request.
* Statements within routines do not currently respect hint injections. The workaround is to modify the inline hints directly in the body by replacing the routine.
Also refer to the PL/pgSQL known limitations.
## See also
* PL/pgSQL
* `CREATE PROCEDURE`
* `CALL`
* `ALTER PROCEDURE`
* `DROP PROCEDURE`