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. - CockroachDB supports the
IN(default),OUT, andINOUTmodes for parameters. For an example, see Create a procedure that usesOUTandINOUTparameters. 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 have the following limitations:
COMMITandROLLBACKstatements are not supported within nested procedures. #122266Routines cannot be invoked with named arguments, e.g.,
SELECT foo(a => 1, b => 2);orSELECT foo(b := 1, a := 2);. #122264Routines cannot be created if they reference temporary tables. #121375
Routines cannot be created with unnamed
INOUTparameters. For example,CREATE PROCEDURE p(INOUT INT) AS $$ BEGIN NULL; END; $$ LANGUAGE PLpgSQL;. #121251Routines 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); $$;. #121247Routines cannot be created with an
OUTparameter of typeRECORD. #123448DDL statements (e.g.,
CREATE TABLE,CREATE INDEX) are not allowed within UDFs or stored procedures. #110080Polymorphic types cannot be cast to other types (e.g.,
TEXT) within routine parameters. #123536Routine parameters and return types cannot be declared using the
ANYENUMpolymorphic type, which is able to match anyENUMtype. 123048
Also refer to the PL/pgSQL known limitations.
