Stored Procedures

On this page

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 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.

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.

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:

• COMMIT and ROLLBACK statements are not supported within nested procedures. #122266

• Routines cannot be invoked with named arguments, e.g., SELECT foo(a => 1, b => 2); or SELECT foo(b := 1, a := 2);. #122264

• Routines cannot be created if they reference temporary tables. #121375

• Routines cannot be created with unnamed INOUT parameters. For example, CREATE PROCEDURE p(INOUT INT) AS $$ BEGIN NULL; END; $$ LANGUAGE PLpgSQL;. #121251

• 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); $$;. #121247

• Routines cannot be created with an OUT parameter of type RECORD. #123448

• DDL statements (e.g., CREATE TABLE, CREATE INDEX) are not allowed within UDFs or stored procedures. #110080

• Polymorphic types cannot be cast to other types (e.g., TEXT) within routine parameters. #123536

Also refer to the PL/pgSQL known limitations.

See also

• PL/pgSQL
• CREATE PROCEDURE
• CALL
• ALTER PROCEDURE
• DROP PROCEDURE