This is an enterprise-only feature. Request a 30-day trial license to try it out.
New in v23.2: PL/pgSQL is a procedural language that you can use within user-defined functions and stored procedures in CockroachDB.
In contrast to SQL statements, 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 CREATE FUNCTION
or CREATE PROCEDURE
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 asCREATE FUNCTION
orCREATE PROCEDURE
parameters. - A function or procedure body, consisting of statements enclosed by
BEGIN
andEND
. - An optional
EXCEPTION
section for catching and handlingSQLSTATE
errors.
At the highest level, a PL/pgSQL block looks like the following:
[ DECLARE
declarations ]
BEGIN
statements
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 the function or procedure body.
DECLARE
variable_name [ CONSTANT ] data_type [ := expression ];
variable_name
is an arbitrary variable name.data_type
can be a supported SQL data type, user-defined type, or the PL/pgSQLREFCURSOR
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.
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 SELECT
or mutation (INSERT
, UPDATE
, DELETE
) statement to a specified variable:
SELECT expression INTO target FROM ...;
[ INSERT | UPDATE | DELETE ] ... RETURNING expression INTO 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 composite variable.
For example, given a table t
with INT
column col
:
The following stored procedure inserts a specified value x
into the table, and the INTO
clause assigns the returned value 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 user-defined function uses the max()
built-in function 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 Create a stored procedure that uses conditional logic.
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;
For usage examples of conditional statements, see Examples.
Write loops
Use looping syntax 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 Create a stored procedure that uses a WHILE
loop.
Add an EXIT
statement to end a LOOP
or WHILE
statement block. This should be combined with a conditional statement.
LOOP
statements;
IF condition THEN
EXIT;
END IF;
END LOOP;
Add a CONTINUE
statement to end a LOOP
or WHILE
statement block, skipping any statements below CONTINUE
, and begin the next iteration of the loop. This should be combined with a conditional statement. In the following example, if the IF
condition is met, then CONTINUE
causes the loop to skip the second block of statements and begin again.
LOOP
statements;
IF condition THEN
CONTINUE;
END IF;
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
CREATE PROCEDURE
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 movr
dataset loaded by cockroach demo
:
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 CALL
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 areDEBUG
,LOG
,NOTICE
,INFO
,WARNING
, andEXCEPTION
. SpecifyEXCEPTION
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 areMESSAGE
,DETAIL
,HINT
, orERRCODE
. To specifyMESSAGE
, use the following alternate syntax:RAISE level USING MESSAGE = 'message';
expression
is an expression to display that corresponds to the specifiedoption
. IfERRCODE
is the specified option, this must be a validSQLSTATE
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
Examples
Create a user-defined function using PL/pgSQL
The following user-defined function returns the n
th integer in the Fibonacci sequence.
It uses the PL/pgSQL LOOP
syntax to iterate through a simple calculation, and RAISE EXCEPTION
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 cockroach demo
to start a temporary, in-memory cluster with the movr
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 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.
For more details on this example, see the Stored Procedures documentation.
Known limitations
- PL/pgSQL blocks cannot be nested.
- Cursors used in PL/pgSQL execute their queries on opening. This can affect performance and resource usage.
- Cursors cannot be declared with parameters.
RECORD
andROW
-type variables cannot be declared in PL/pgSQL.NOT NULL
variables cannot be declared in PL/pgSQL.- PL/pgSQL arguments cannot be referenced with ordinals (e.g.,
$1
,$2
). - PL/pgSQL
EXCEPTION
blocks cannot catch transaction retry errors. FOR
loops (includingFOR
cursor loops andFOR
query loops) andFOREACH
loops are not supported.RETURN NEXT
andRETURN QUERY
statements are not supported.CASE
statements are not supported.EXIT
andCONTINUE
labels and conditions are not supported.- Variable shadowing (e.g., declaring a variable with the same name in an inner block) is not supported in PL/pgSQL.
- When using the
RAISE
statement, schema objects related to the error cannot be named usingCOLUMN
,CONSTRAINT
,DATATYPE
,TABLE
, andSCHEMA
. - The
INTO
statement in PL/pgSQL does not support theSTRICT
option. PERFORM
,EXECUTE
,GET DIAGNOSTICS
, andNULL
statements are not supported for PL/pgSQL.