Stored Procedures

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

Also refer to the PL/pgSQL known limitations.

Statements

Syntax

Data Types

Spatial Data

Constraints

Structure

Examples

Setup

Create a stored procedure using PL/pgSQL

Example details

Alter a stored procedure

Known limitations

See also

​Structure

​Examples

​Setup

​Create a stored procedure using PL/pgSQL

​Example details

​Alter a stored procedure

​Known limitations

​See also

Structure

Examples

Setup

Create a stored procedure using PL/pgSQL

Example details

Alter a stored procedure

Known limitations

See also