Stored Procedures

A stored procedure is a database object consisting of or statements that can be issued with a single statement. This allows complex logic to be executed repeatedly within the database, which can improve performance and mitigate security risks.

Both stored procedures and 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 statement. Functions can be invoked in nearly any context, such as SELECT, FROM, and WHERE clauses, expressions, and expressions.
• Functions have 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 , , or the PL/pgSQL REFCURSOR type, when .
• CockroachDB supports the IN (default), OUT, and INOUT modes for parameters. For an example, see .
• LANGUAGE specifies the language of the function body. CockroachDB supports the languages and .
• 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 if written in PL/pgSQL.

For details, see .

Examples

Setup

To follow along, run to start a temporary, in-memory cluster with the sample dataset preloaded:

$ cockroach demo

For more examples of stored procedure creation, see .

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

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:

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 as the language for the stored procedure.

LANGUAGE PLpgSQL

DECLARE specifies the that are used in the procedure body.

DECLARE
    counter INT := 0;
    deleted_timestamp TIMESTAMP;
    deleted_ride_id UUID;
    latest_timestamp TIMESTAMP;

BEGIN and END in the procedure body.

BEGIN
  ...
  END

The following statement 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 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 .

Finally, the 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 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 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 .

See also