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:

• 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
• DDL statements (e.g., CREATE TABLE, CREATE INDEX) are not allowed within UDFs or stored procedures. #110080

Also refer to the PL/pgSQL known limitations.

See also

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

Was this helpful?