> ## Documentation Index > Fetch the complete documentation index at: https://docs.cockroachlabs.com/llms.txt > Use this file to discover all available pages before exploring further. # Stored Procedures A *stored procedure* is a database object consisting of [PL/pgSQL](https://docs.cockroachlabs.com/docs/stable/plpgsql.md) or [SQL](https://docs.cockroachlabs.com/docs/stable/sql-statements.md) statements that can be issued with a single [`CALL`](https://docs.cockroachlabs.com/docs/stable/call) statement. This allows complex logic to be executed repeatedly within the database, which can improve performance and mitigate security risks. Both stored procedures and [user-defined functions](https://docs.cockroachlabs.com/docs/stable/user-defined-functions.md) 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`](https://docs.cockroachlabs.com/docs/stable/call) statement. Functions can be invoked in nearly any context, such as `SELECT`, `FROM`, and `WHERE` clauses, [`DEFAULT`](https://docs.cockroachlabs.com/docs/stable/default-value) expressions, and [computed column](https://docs.cockroachlabs.com/docs/stable/computed-columns.md) expressions. * Functions have [volatility](https://docs.cockroachlabs.com/docs/stable/functions-and-operators.md#function-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](https://docs.cockroachlabs.com/docs/stable/data-types.md), [user-defined type](https://docs.cockroachlabs.com/docs/stable/create-type.md), or the PL/pgSQL `REFCURSOR` type, when [declaring PL/pgSQL cursor variables](https://docs.cockroachlabs.com/docs/stable/plpgsql.md#declare-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](https://docs.cockroachlabs.com/docs/stable/create-procedure#create-a-stored-procedure-that-uses-out-and-inout-parameters). * `LANGUAGE` specifies the language of the function body. CockroachDB supports the languages [`SQL`](https://docs.cockroachlabs.com/docs/stable/sql-statements) and [`PLpgSQL`](https://docs.cockroachlabs.com/docs/stable/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](https://docs.cockroachlabs.com/docs/stable/plpgsql.md#structure) if written in PL/pgSQL. For details, see [`CREATE PROCEDURE`](https://docs.cockroachlabs.com/docs/stable/create-procedure). ## Examples #### Setup To follow along, run [`cockroach demo`](https://docs.cockroachlabs.com/docs/stable/cockroach-demo) to start a temporary, in-memory cluster with the [`movr`](https://docs.cockroachlabs.com/docs/stable/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`](https://docs.cockroachlabs.com/docs/stable/create-procedure#examples). ### 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 [PL/pgSQL](https://docs.cockroachlabs.com/docs/stable/plpgsql.md) [`WHILE`](https://docs.cockroachlabs.com/docs/stable/plpgsql#write-loops) syntax to iterate through the rows, [`RAISE`](https://docs.cockroachlabs.com/docs/stable/plpgsql#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 [transaction](https://docs.cockroachlabs.com/docs/stable/transactions.md): ```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`](https://docs.cockroachlabs.com/docs/stable/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](https://docs.cockroachlabs.com/docs/stable/plpgsql.md) 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](https://docs.cockroachlabs.com/docs/stable/plpgsql.md#declare-a-variable) 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](https://docs.cockroachlabs.com/docs/stable/plpgsql.md#structure) in the procedure body. ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} BEGIN ... END ``` The following [`IF ... THEN`](https://docs.cockroachlabs.com/docs/stable/plpgsql#write-conditional-statements) statement [raises an exception](https://docs.cockroachlabs.com/docs/stable/plpgsql.md#report-messages-and-handle-exceptions) 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`](https://docs.cockroachlabs.com/docs/stable/plpgsql#write-loops) 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](https://docs.cockroachlabs.com/docs/stable/plpgsql.md#assign-a-result-to-a-variable). Finally, the [`RAISE NOTICE`](https://docs.cockroachlabs.com/docs/stable/plpgsql#report-messages-and-handle-exceptions) 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](https://docs.cockroachlabs.com/docs/stable/plpgsql.md#open-and-use-cursors) 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](https://docs.cockroachlabs.com/docs/stable/stored-procedures.md#example-details). ### Alter a stored procedure The following statement renames the [`delete_earliest_histories` example procedure](https://docs.cockroachlabs.com/docs/stable/stored-procedures) 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. * Routine parameters and return types cannot be declared using the `ANYENUM` polymorphic type, which is able to match any [`ENUM`](https://docs.cockroachlabs.com/docs/stable/enum) type. 123048 Also refer to the [PL/pgSQL known limitations](https://docs.cockroachlabs.com/docs/stable/plpgsql.md#known-limitations). ## See also * [PL/pgSQL](https://docs.cockroachlabs.com/docs/stable/plpgsql.md) * [`CREATE PROCEDURE`](https://docs.cockroachlabs.com/docs/stable/create-procedure) * [`CALL`](https://docs.cockroachlabs.com/docs/stable/call) * [`ALTER PROCEDURE`](https://docs.cockroachlabs.com/docs/stable/alter-procedure) * [`DROP PROCEDURE`](https://docs.cockroachlabs.com/docs/stable/drop-procedure)