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
CALLstatement. Functions can be invoked in nearly any context, such asSELECT,FROM, andWHEREclauses,DEFAULTexpressions, 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
REFCURSORtype, when declaring PL/pgSQL cursor variables. LANGUAGEspecifies the language of the function body. CockroachDB supports the languagesSQLandPLpgSQL.- 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
OUTandINOUTargument 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.
