New in v23.2:
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 procedures and user-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 asSELECT
,FROM
, andWHERE
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.
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. LANGUAGE
specifies the language of the function body. CockroachDB supports the languagesSQL
andPLpgSQL
.- 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.
- Can be enclosed in single or dollar (
For details, see CREATE PROCEDURE
.
Examples
Setup
To follow along, run cockroach demo
to start a temporary, in-memory cluster with the movr
sample dataset preloaded:
$ cockroach demo
For more examples of stored procedure creation, see CREATE PROCEDURE
.
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 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.
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:
BEGIN;
Call the stored procedure, specifying 5 rows to delete and a rides_left
cursor name:
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
:
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.
CREATE OR REPLACE PROCEDURE delete_earliest_histories (
num_deletions INT, remaining_histories REFCURSOR
)
LANGUAGE
specifies PL/pgSQL as the language for the stored procedure.
LANGUAGE PLpgSQL
DECLARE
specifies the PL/pgSQL variable definitions that are used in the procedure body.
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.
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.
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.
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.
OPEN remaining_histories FOR SELECT * FROM vehicle_location_histories ORDER BY timestamp;
Alter a stored procedure
The following statement renames the delete_earliest_histories
example procedure to delete_histories
:
ALTER PROCEDURE delete_earliest_histories RENAME TO delete_histories;
Known limitations
- Stored procedures cannot call other stored procedures or user-defined functions.
- Stored procedures do not support
OUT
andINOUT
argument modes. - DDL statements (e.g.,
CREATE TABLE
,CREATE INDEX
) cannot be used within a stored procedure body. - Transactions cannot be run within stored procedures.
Also refer to the PL/pgSQL known limitations.