> ## 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.
# 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`.
## DDL and DCL statements
### Supported statements and requirements
Stored procedures support the following DDL and DCL statements:
| Statement category | Supported statements | Requirements |
| ------------------ | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| DDL | `CREATE TABLE` (including temporary tables using `TEMP` or `TEMPORARY`), `DROP TABLE`, `CREATE SCHEMA`, `DROP SCHEMA`, `CREATE ROLE`, `DROP ROLE` | In PL/pgSQL procedures, enable the `sql.procedures.plpgsql.late_binding.enabled` cluster setting. SQL language procedures use early binding and are not affected by this setting. |
| DCL | `GRANT` and `REVOKE` on privileges, including object and system privileges; `ALTER DEFAULT PRIVILEGES` | Late binding is not required for a procedure that contains only DCL statements. `GRANT` and `REVOKE` on roles are not supported. |
The support described here applies only to stored procedures. DDL and DCL statements remain unsupported in user-defined functions and `DO` blocks. Other DDL statements, including `ALTER TABLE`, `CREATE INDEX`, `CREATE VIEW`, and `TRUNCATE`, remain unsupported in stored procedures. Enabling late binding does not enable additional DDL statements.
The value of the `sql.procedures.plpgsql.late_binding.enabled` cluster setting determines how a PL/pgSQL procedure is bound when the procedure is created or replaced. Changing this setting does not change the binding of existing procedures. To enable late binding for an existing procedure, enable the setting and issue `CREATE OR REPLACE PROCEDURE`. Late-bound procedures can create and reference temporary tables, which remain scoped to the session.
### Transaction and security behavior
Procedures that contain DDL or DCL must be invoked by a standalone `CALL`, which runs as an implicit transaction. In other words, they must be run outside an explicit `BEGIN` ... `COMMIT` transaction. This restriction does not apply to a procedure that contains only DML.
Run the standalone `CALL` at `SERIALIZABLE` isolation. If an implicit transaction uses `READ COMMITTED` or `REPEATABLE READ` isolation, CockroachDB attempts to restart the transaction at `SERIALIZABLE` isolation and emits the notice `setting transaction isolation level to SERIALIZABLE due to schema change`.
CockroachDB rejects the `CALL` when it cannot restart the transaction, including in the following cases:
* After another statement in the transaction has executed.
* When the DDL or DCL is reached only through a nested `CALL`.
* When an active portal from the extended query protocol prevents the restart.
DDL and DCL changes in a procedure are transactional. If the `CALL` fails or the transaction is rolled back, the changes are rolled back. A PL/pgSQL block with an `EXCEPTION` clause uses a savepoint: when an exception is caught, changes made inside that block are rolled back, while changes completed before the block are preserved.
DDL and DCL follow the procedure's security mode. In a `SECURITY DEFINER` procedure, privilege checks and `current_user` resolve to the procedure owner, and objects created by the procedure are owned by that user. For details, refer to `CREATE PROCEDURE`'s required privileges.
## 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.
For each supported DDL and DCL operation, CockroachDB exports the `sql.routine.{type}.started.count` metric, which counts statements started within routines, and `sql.routine.{type}.count`, which counts statements successfully executed within routines. Metrics for internal queries append `.internal` to the metric name.
The `{type}` placeholder in the metric name is one of the following:
* `create_table`
* `create_temp_table`
* `drop_table`
* `create_schema`
* `drop_schema`
* `create_role`
* `drop_role`
* `grant`
* `revoke`
* `alter_default_privileges`
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.
### Execute DDL in a PL/pgSQL procedure
The following example creates a self-initializing application event log. Each time the procedure is called, it creates the `audit_events` table if needed and records an event.
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SET CLUSTER SETTING sql.procedures.plpgsql.late_binding.enabled = true;
CREATE PROCEDURE create_audit_events()
LANGUAGE PLpgSQL
AS $$
BEGIN
CREATE TABLE IF NOT EXISTS audit_events (
event_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
event_type STRING NOT NULL,
event_timestamp TIMESTAMPTZ NOT NULL DEFAULT now()
);
INSERT INTO audit_events (event_type) VALUES ('procedure called');
END;
$$;
CALL create_audit_events();
```
In a late-bound PL/pgSQL procedure, each top-level statement is planned immediately before it runs. Therefore, the `INSERT` can reference the table created by the preceding statement. By contrast, SQL language procedures use early binding, so referenced objects must exist when the procedure is created.
### 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);`.
* Temporary tables cannot be created within user-defined functions or `DO` blocks. Within a PL/pgSQL stored procedure body, creating a temporary table requires the `sql.procedures.plpgsql.late_binding.enabled` cluster setting to be enabled.
* 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`.
* Stored procedures do not support arbitrary DDL. For the supported statements and requirements, refer to [DDL and DCL statements](#ddl-and-dcl-statements).
* Within a PL/pgSQL `IF`, loop, nested block, or `EXCEPTION` handler, a statement cannot reference an object created earlier in the same control-flow construct. Move the reference after the construct, or move the DDL and the reference to the top level of the procedure body.
* A stored procedure that contains DDL or DCL statements cannot be called within an explicit transaction.
* 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`