On this page 
The DO statement defines a code block that executes PL/pgSQL syntax.
Required privileges
- To define a
DOblock with a user-defined type, a user must haveUSAGEprivilege on the user-defined type.
Synopsis
Parameters
| Parameter | Description |
|---|---|
routine_body_str |
The body of the code block. |
Examples
Declare a variable in a DO block
The following example uses the PL/pgSQL DECLARE syntax to declare variables to use in the code block.
DO $$
DECLARE
x INT := 10;
y INT := 5;
result INT;
BEGIN
result := x + y;
RAISE NOTICE 'The sum of % and % is %', x, y, result;
END $$;
NOTICE: The sum of 10 and 5 is 15
DO
Use a loop in a DO block
The following example uses the PL/pgSQL WHILE syntax to loop through several statements.
DO $$
DECLARE
counter INT := 1;
BEGIN
WHILE counter <= 5 LOOP
RAISE NOTICE 'Counter: %', counter;
counter := counter + 1;
END LOOP;
END $$;
NOTICE: Counter: 1
NOTICE: Counter: 2
NOTICE: Counter: 3
NOTICE: Counter: 4
NOTICE: Counter: 5
DO
Use a common table expression in a DO block
The following example uses a common table expression in the body of the code block.
DO $$
DECLARE
sum_result INT;
BEGIN
WITH numbers AS (
SELECT generate_series(1, 5) AS num
)
SELECT sum(num) INTO sum_result
FROM numbers;
RAISE NOTICE 'Sum of numbers 1-5: %', sum_result;
END $$;
NOTICE: Sum of numbers 1-5: 15
DO
See also
Was this helpful?
