PL/pgSQL

PL/pgSQL is a procedural language that you can use within , , and blocks in CockroachDB.

In contrast to , which are issued one-by-one from the client to the database, PL/pgSQL statements are encapsulated in a block structure and executed on the database side, thus reducing network latency. PL/pgSQL enables more complex functionality than standard SQL, including conditional statements, loops, and exception handling.

This page describes PL/pgSQL structure and syntax, and includes examples of functions and procedures that use PL/pgSQL.

Structure

A function or procedure that uses PL/pgSQL must specify the PLpgSQL language within the or statement:

CREATE [ PROCEDURE | FUNCTION ] ...
  LANGUAGE PLpgSQL
  ...

PL/pgSQL is block-structured. A block contains the following:

• An optional DECLARE section that contains variable declarations for all variables that are used within the block and are not defined as or parameters.
• A or body, consisting of statements enclosed by BEGIN and END.
• An optional EXCEPTION section for catching and handling SQLSTATE errors.

At the highest level, a PL/pgSQL block looks like the following:

[ DECLARE
	declarations ]
  BEGIN
	statements
  END

PL/pgSQL blocks can be nested. An optional label can be placed above each block. Block labels can be targeted by EXIT statements.

[ <<outer_block> ]
  [ DECLARE
    declarations ]
  BEGIN
    statements
    [ <<inner_block> ]
    [ DECLARE
      declarations ]
    BEGIN
      statements
    END;
  END

When you create a function or procedure, you can enclose the entire PL/pgSQL block in dollar quotes ($$). Dollar quotes are not required, but are easier to use than single quotes, which require that you escape other single quotes that are within the function or procedure body.

CREATE PROCEDURE name(parameters)
  LANGUAGE PLpgSQL
  AS $$
  [ DECLARE
	declarations ]
  BEGIN
	statements
  END
  $$;

For complete examples, see Create a user-defined function using PL/pgSQL and Create a stored procedure using PL/pgSQL.

Syntax

Declare a variable

DECLARE specifies all variable definitions that are used in a block.

DECLARE
	variable_name [ CONSTANT ] data_type [ := expression ];

• variable_name is an arbitrary variable name.
• data_type can be a supported , , or the PL/pgSQL REFCURSOR type, when declaring cursor variables.
• CONSTANT specifies that the variable cannot be reassigned, ensuring that its value remains constant within the block.
• expression is an expression that provides an optional default value for the variable. Default values are evaluated every time a block is entered in a function or procedure.

For example:

DECLARE
	a VARCHAR;
	b INT := 0;

Declare cursor variables

A cursor encapsulates a selection query and is used to fetch the query results for a subset of rows.

You can declare forward-only cursors as variables to be used within PL/pgSQL blocks. These must have the PL/pgSQL REFCURSOR data type. For example:

DECLARE
	c REFCURSOR;

You can bind a cursor to a selection query within the declaration. Use the CURSOR FOR syntax and specify the query:

DECLARE
	c CURSOR FOR query;

Note that the preceding cursor still has the REFCURSOR data type.

For information about opening and using cursors, see Open and use cursors.

Assign a result to a variable

Use the PL/pgSQL INTO clause to assign a result of a or mutation (, , ) statement to a specified variable. The optional STRICT clause specifies that the statement must return exactly one row; otherwise, the function or procedure will error. This behavior can be enabled by default using the session setting.

SELECT expression INTO [ STRICT ] target FROM ...;

[ INSERT | UPDATE | DELETE ] ... RETURNING expression INTO [ STRICT ] target;

• expression is an expression that defines the result to be assigned to the variable.
• target is an arbitrary variable name. This can be a list of comma-separated variables, or a single .

For example, given a table t with INT column col:

The following inserts a specified value x into the table, and the INTO clause assigns the to i.

CREATE OR REPLACE PROCEDURE p(x INT) AS $$
	DECLARE
	    i INT;
	BEGIN
	    INSERT INTO t (col) VALUES (x) RETURNING col INTO i;
	    RAISE NOTICE 'New Row: %', i;
	END
$$ LANGUAGE PLpgSQL;

When the procedure is called, it inserts the specified integer into a new row in the table, and prints a NOTICE message that contains the inserted value:

CALL p(2);

NOTICE: New Row: 2
CALL

The following uses the max to find the maximum col value in table t, and assigns the result to i.

CREATE OR REPLACE FUNCTION f() RETURNS INT AS $$
	DECLARE
		i INT;
	BEGIN
		SELECT max(col) INTO i FROM t;
		RETURN i;
	END
$$ LANGUAGE PLpgSQL;

When the function is invoked, it displays the maximum value that was inserted into the table:

SELECT f();

  f
-----
  2

For a more extensive example of variable assignment, see Create a stored procedure using PL/pgSQL.

Write conditional statements

Use IF syntax to execute statements conditionally. PL/pgSQL understands several forms of IF statements.

IF ... THEN executes statements only if a boolean condition is true.

IF condition THEN
		statements;
  END IF;

For an example, see .

IF ... THEN ... ELSE executes statements if a boolean condition is true. If the condition is false, the ELSE statements are executed.

IF condition THEN
	statements;
  ELSE
	else_statements;
  END IF;

IF ... THEN ... ELSIF executes statements if a boolean condition is true. If the condition is false, each ELSIF condition is evaluated until one is true. The corresponding ELSIF statements are executed. If no ELSIF conditions are true, no statements are executed unless an ELSE clause is included, in which case the ELSE statements are executed.

IF condition THEN
	statements;
  ELSIF elsif_condition THEN
	elsif_statements;
  [ ELSIF elsif_condition_n THEN
	elsif_statements_n; ]
  [ ELSE
	else_statements; ]
  END IF;

IF, ELSE, and ELSIF conditions are not required to execute statements. You can exclude any statements or add a placeholder NULL statement.

IF condition THEN
	NULL;
  END IF;

For usage examples of conditional statements, see Examples.

Write loops

Write a loop to repeatedly execute statements.

On its own, LOOP executes statements infinitely.

LOOP
	statements;
  END LOOP;

On its own, WHILE executes statements infinitely if a boolean condition is true. The statements repeat until the condition is false.

WHILE condition LOOP
	statements;
  END LOOP;

For an example, see .

FOR ... IN iterates a loop over a range of integer values. Specify a variable name to iterate over the minimum and maximum values of the integer range. The optional BY clause is used to specify an integer step value, and REVERSE causes the loop to subtract the step value across iterations.

FOR variable_name IN [ REVERSE ] minimum .. maximum [ BY step ]
  LOOP
	statements;
  END LOOP;

In the following example, the FOR loop iterates from 1 to 10 in steps of 2:

CREATE OR REPLACE PROCEDURE p() LANGUAGE PLPGSQL AS $$
  BEGIN
  FOR i IN 1..10 BY 2
  LOOP
  	RAISE NOTICE '%', i;
  END LOOP;
  END $$;

CALL p();

NOTICE: 1
NOTICE: 3
NOTICE: 5
NOTICE: 7
NOTICE: 9
CALL

Control execution flow

EXIT

Add an EXIT statement to end a loop. An EXIT statement can be combined with an optional WHEN boolean condition.

LOOP
	statements;
	EXIT [ WHEN condition ];
  END LOOP;

Add a label to an EXIT statement to target a block that has a matching label. An EXIT statement with a label can target either a loop or a block. An EXIT statement inside a block must have a label.

The following EXIT statement will end the label block before the statements are executed.

BEGIN
	<<label>
	BEGIN
	  EXIT label;
	  statements;
	END;
  END

If more than one PL/pgSQL block has a matching label, the innermost block is chosen.

In the following example, the EXIT statement in the inner block is used to exit the stored procedure.

CREATE PROCEDURE p() AS $$
  <<outer_block>
  BEGIN
  	RAISE NOTICE '%', 'this is printed';
  	<<inner_block>
  	BEGIN
	  	EXIT outer_block;
	  	RAISE NOTICE '%', 'this is not printed';
	  END;
  END
  $$ LANGUAGE PLpgSQL;

RETURN

Add a RETURN statement to a routine with an OUT parameter or VOID return type to exit the routine immediately.

BEGIN
	...
	RETURN;

CONTINUE

Add a CONTINUE statement to end the current iteration of a loop, skipping any statements below CONTINUE and beginning the next iteration of the loop.

A CONTINUE statement can be combined with an optional WHEN boolean condition. In the following example, if a WHEN condition is defined and met, then CONTINUE causes the loop to skip the second group of statements and begin again.

LOOP
	statements;
	CONTINUE [ WHEN condition ];
	statements;
  END LOOP;

Open and use cursors

PL/pgSQL cursors can be used in the following scenarios:

• When declared as variables, cursors can be used within PL/pgSQL blocks.
• When specified as a parameter in a statement, cursors can be accessed externally from the stored procedure.

The cursor must first be opened within a PL/pgSQL block. If the cursor was declared without being bound to a query, you must specify a query using the FOR clause.

BEGIN
	OPEN cursor_name [ FOR query ];

After opening the cursor, you can issue a PL/pgSQL FETCH statement to assign the result to one or more variables.

BEGIN
	...
	FETCH cursor_name INTO target;

In PL/pgSQL, FETCH returns a single row. For example, FETCH 10 returns the 10th row.

You can free up a cursor variable by closing the cursor:

BEGIN
	...
	CLOSE cursor_name;

Cursors that are specified as parameters, rather than declared as variables, can be passed externally to and from PL/pgSQL blocks.

For example, using the loaded by :

CREATE OR REPLACE PROCEDURE get_rides(rides_cursor REFCURSOR) AS $$
  BEGIN
    OPEN rides_cursor FOR SELECT * FROM movr.rides;
  END
  $$ LANGUAGE PLpgSQL;

Within the same transaction that opened the cursor, use the SQL FETCH statement to retrieve query results for a specified number of rows:

FETCH rows FROM cursor_name;

The and FETCH statements have to be issued within the same transaction, or the cursor will not be found:

BEGIN;
  CALL get_rides('rides');
  FETCH 2 FROM rides;
  COMMIT;

                   id                  |   city    | vehicle_city |               rider_id               |              vehicle_id              |         start_address         |         end_address         |     start_time      |      end_time       | revenue
---------------------------------------+-----------+--------------+--------------------------------------+--------------------------------------+-------------------------------+-----------------------------+---------------------+---------------------+----------
  ab020c49-ba5e-4800-8000-00000000014e | amsterdam | amsterdam    | b3333333-3333-4000-8000-000000000023 | bbbbbbbb-bbbb-4800-8000-00000000000b | 58875 Bell Ports              | 50164 William Glens         | 2018-12-16 03:04:05 | 2018-12-17 20:04:05 |   13.00
  ab851eb8-51eb-4800-8000-00000000014f | amsterdam | amsterdam    | ae147ae1-47ae-4800-8000-000000000022 | bbbbbbbb-bbbb-4800-8000-00000000000b | 62025 Welch Alley             | 4092 Timothy Creek Apt. 39  | 2018-12-31 03:04:05 | 2019-01-02 03:04:05 |   32.00

Report messages and handle exceptions

Use the RAISE statement to print messages for status or error reporting.

RAISE level 'message' [, expressions ]
  [ USING option = 'expression' [, ... ] ];

RAISE messages the client directly, and does not currently produce log output.

• level is the message severity. Possible values are DEBUG, LOG, NOTICE, INFO, WARNING, and EXCEPTION. Specify EXCEPTION to raise an error that aborts the current transaction.

• message is a message string to display.

• expressions is an optional, comma-separated list of expressions that provide values to replace any % placed within the message string. The number of expressions must match the number of % placeholders.

• option is a type of additional information to include. Possible values are MESSAGE, DETAIL, HINT, or ERRCODE. To specify MESSAGE, use the following alternate syntax:

  RAISE level USING MESSAGE = 'message';
  

• expression is an expression to display that corresponds to the specified option. If ERRCODE is the specified option, this must be a valid SQLSTATE error code or name.

For example:

CREATE OR REPLACE PROCEDURE raise_time() AS $$
  BEGIN
    RAISE NOTICE 'current timestamp: %', now()
	USING HINT = 'Call this procedure again for a different result';
  END
  $$ LANGUAGE PLpgSQL;

CALL raise_time();

NOTICE: current timestamp: 2024-01-05 23:09:08.0601+00
HINT: Call this procedure again for a different result
CALL

Write exception logic

Use an EXCEPTION statement to catch and handle specified errors.

Any valid SQLSTATE error code or name can be specified, except for Class 40 (transaction rollback) errors. Arbitrary user-defined SQLSTATE codes can also be specified.

If a specified error is caught, the exception handling statements are executed. Any unspecified errors are caught by WHEN OTHERS, except for query_canceled and assert_failure.

EXCEPTION
	WHEN error THEN
		handle_exception;
	[ WHEN error_n THEN
		handle_exception_n; ]
	[ WHEN OTHERS THEN
		handle_other_exceptions; ]

EXCEPTION logic is included after the main body of a PL/pgSQL block. For example:

BEGIN
	...
  EXCEPTION
    WHEN not_null_violation THEN
      RETURN 'not_null_violation';
    WHEN OTHERS THEN
      RETURN others;
  END

WHEN conditions are not required to execute statements. You can exclude any statements or add a placeholder NULL statement.

EXCEPTION
	WHEN error THEN
		NULL;

Control transactions

Use a COMMIT or ROLLBACK statement within a PL/pgSQL to finish the current transaction and automatically start a new one.

• Any updates made within the previous transaction are either committed or rolled back, while PL/pgSQL variables keep their values.
• Execution of the stored procedure resumes in a new transaction with the statements immediately following the COMMIT or ROLLBACK statement.

BEGIN
	statements
	[ COMMIT | ROLLBACK ]

COMMIT and ROLLBACK statements within PL/pgSQL blocks have the following requirements:

• They must be used inside a PL/pgSQL .

• They cannot be inside a block with an EXCEPTION clause.

• The procedure must be called directly in a SQL statement. It cannot be called by another stored procedure.

• The procedure must be called from an ; i.e., the call cannot be enclosed by and SQL statements.

Statements that follow a COMMIT or ROLLBACK automatically start another PL/pgSQL transaction. If a transaction is running when the procedure ends, it is implicitly committed without having to be followed by a COMMIT.

BEGIN
	...
	[ COMMIT | ROLLBACK ]
	statements
  END

Use one or more optional SET TRANSACTION statements to set the of a PL/pgSQL transaction. In PL/pgSQL, SET TRANSACTION statements must directly follow COMMIT or ROLLBACK, or another SET TRANSACTION statement; and must precede the other statements in the transaction.

BEGIN
	...
	[ COMMIT | ROLLBACK ]
	[ SET TRANSACTION mode ]
	statements

For example:

CREATE PROCEDURE p() LANGUAGE PLpgSQL AS
  $$
  BEGIN
    COMMIT;
    SET TRANSACTION PRIORITY HIGH;
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
    RAISE NOTICE '%', current_setting('transaction_isolation');
    RAISE NOTICE '%', current_setting('transaction_priority');
  END
  $$;

CALL p();

NOTICE: read committed
NOTICE: high
CALL

Any PL/pgSQL transaction not preceded by a SET TRANSACTION statement uses the default settings.

Call a procedure

Use a CALL statement to call a procedure from within a PL/pgSQL or .

BEGIN
	CALL procedure(parameters);

A PL/pgSQL routine that calls a procedure should declare a variable that will store the result of each of that procedure’s OUT parameters. For example, given the procedure:

CREATE OR REPLACE PROCEDURE output_one(OUT value INT) AS
  $$
  BEGIN
    value := 1;
  END
  $$ LANGUAGE PLpgSQL;

To call output_one within another procedure:

CREATE OR REPLACE PROCEDURE output() AS
  $$
  DECLARE
    output_value INT;
  BEGIN
    CALL output_one(output_value);
    RAISE NOTICE 'Output value: %', output_value;
  END
  $$ LANGUAGE PLpgSQL;

A procedure with OUT parameters can only be called from a PL/pgSQL routine. For another example, see .

Examples

Create a user-defined function using PL/pgSQL

The following user-defined function returns the nth integer in the Fibonacci sequence.

It uses the PL/pgSQL syntax to iterate through a simple calculation, and to return an error message if the specified n is negative.

CREATE FUNCTION fib(n int) RETURNS INT AS $$
	DECLARE
		tmp INT;
		a INT := 0;
		b INT := 1;
		i INT := 2;
	BEGIN
		IF n < 0 THEN
			RAISE EXCEPTION 'n must be non-negative';
		END IF;
		IF n = 0 THEN RETURN 0; END IF;
		IF n = 1 THEN RETURN 1; END IF;
		LOOP
			IF i > n THEN EXIT; END IF;
			tmp := a + b;
			a := b;
			b := tmp;
			i := i + 1;
		END LOOP;
		RETURN b;
	END
  $$ LANGUAGE PLpgSQL;

SELECT fib(8);

  fib
-------
   21

Create a stored procedure using PL/pgSQL

Setup

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

$ cockroach demo

The following stored procedure removes a specified number of earliest rides in vehicle_location_histories.

It uses the PL/pgSQL 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;

For more details on this example, see the .

Known limitations

• It is not possible to use a variable as a target more than once in the same INTO clause. For example, SELECT 1, 2 INTO x, x;.
• PLpgSQL variable declarations cannot inherit the type of a table row or column using %TYPE or %ROWTYPE syntax.
• PL/pgSQL arguments cannot be referenced with ordinals (e.g., $1, $2).
• The following statements are not supported:
  • FOR cursor loops, FOR query loops, and FOREACH loops.
  • RETURN NEXT and RETURN QUERY.
  • PERFORM, EXECUTE, GET DIAGNOSTICS, and CASE.
• PL/pgSQL exception blocks cannot catch .
• RAISE statements cannot be annotated with names of schema objects related to the error (i.e., using COLUMN, CONSTRAINT, DATATYPE, TABLE, or SCHEMA).
• RAISE statements message the client directly, and do not produce log output.
• ASSERT debugging checks are not supported.
• RECORD parameters and variables are not supported in .
• Variable shadowing (e.g., declaring a variable with the same name in an inner block) is not supported in PL/pgSQL.
• Syntax for accessing members of composite types without parentheses is not supported.
• NOT NULL variable declarations are not supported.
• Cursors opened in PL/pgSQL execute their queries on opening, affecting performance and resource usage.
• Cursors in PL/pgSQL cannot be declared with arguments.
• OPEN FOR EXECUTE is not supported for opening cursors.
• The print_strict_params option is not supported in PL/pgSQL.
• The FOUND local variable, which checks whether a statement affected any rows, is not supported in PL/pgSQL.
• By default, when a PL/pgSQL variable conflicts with a column name, CockroachDB resolves the ambiguity by treating it as a column reference rather than a variable reference. This behavior differs from PostgreSQL, where an ambiguous column error is reported, and it is possible to change the plpgsql.variable_conflict setting in order to prefer either columns or variables.
• It is not possible to define a RECORD-returning PL/pgSQL function that returns different-typed expressions from different RETURN statements. CockroachDB requires a consistent return type for RECORD-returning functions.
• Variables cannot be declared with an associated collation using the COLLATE keyword.
• Variables cannot be accessed using the label.var_name pattern.

See also