DO statement:
can have zero or more queries with
BEGIN ... END
statement. *Be careful,BEGIN ... END
clause is not transaction.can have DECLARE clause. *My post explains how to declare local variables in
DECLARE
clause.can have SELECT INTO statement.
cannot have RETURN NEXT and RETURN QUERY statement.
can have PERFORM statement.
can have EXECUTE. *My post explains how to use
EXECUTE
statement in a funcition.can have RETURN statement without a value which is
RETURN;
.cannot have
RETURN
statement with a value e.g.RETURN 2;
otherwise there is the error.cannot have SELECT statement without
INTO
as a non-substatement otherwise there is the error.is atomic by default running in a single transaction so if there is error, it is rollbacked automatically.
cannot control transaction with
START TRANSACTION
,BEGIN
,ROLLBACK
,COMMIT
, etc otherwise there is error.only supports PL/pgSQL language rather than SQL language so if you set
LANGUAGE SQL
to aDO
statement, there is error.
*The doc explains DO
statement.
*My post explains a PL/pgSQL function.
*My post explains a PL/pgSQL procedure.
*My post explains a SQL function.
*My post explains a SOL procedure.
*You should use PL/pgSQL language because you can do much more things with it compared with SQL language.
For example, you create test
table as shown below:
CREATE TABLE test (
num INT
);
Then, you insert the row whose num
is 0
into test
table as shown below:
INSERT INTO test (num) VALUES (0);
Now, you can create the DO
statement which increments num
by 1
, then raises a message with RAISE statement as shown below. *You can omit the DECLARE
clause, if it is not necessary:
DO LANGUAGE plpgsql $$
DECLARE
value INT;
BEGIN
UPDATE test SET num = num + 1;
SELECT num INTO value FROM test;
RAISE INFO 'value is %.', value;
END
$$;
Or, you can change the position of LANGUAGE plpgsql
as shown below:
DO $$
DECLARE
value INT;
BEGIN
UPDATE test SET num = num + 1;
SELECT num INTO value FROM test;
RAISE INFO 'value is %.', value;
END
$$ LANGUAGE plpgsql; -- Here
Or, you can omit LANGUAGE plpgsql
as shown below:
DO /* LANGUAGE plpgsql */ $$
DECLARE
value INT;
BEGIN
UPDATE test SET num = num + 1;
SELECT num INTO value FROM test;
RAISE INFO 'value is %.', value;
END
$$;
Then, running the DO
statement above gets the output below:
INFO: value is 1.
DO
And, you can use PERFORM
statement as shown below:
DO LANGUAGE plpgsql $$
DECLARE
value INT;
BEGIN
UPDATE test SET num = num + 1;
SELECT num INTO value FROM test;
PERFORM num FROM test; -- Here
RAISE INFO 'value is %.', value;
END
$$;
Then, running the DO
statement above gets the output below:
INFO: value is 2.
DO
And for example, you create person
table as shown below:
CREATE TABLE person (
id INT,
name VARCHAR(20),
age INT
);
Then, you insert 2 rows into person
table as shown below:
INSERT INTO person (id, name, age)
VALUES (1, 'John', 27), (2, 'David', 32);
And, you can use an EXECUTE
statement in a DO
statement as shown below. *My post has the example of a DO
statement with a FOR statement:
DO $$
BEGIN
EXECUTE 'UPDATE person SET age = 13 WHERE id = 2';
END
$$;
Then, running the DO
statement above updates age
of David
to 13
as shown below:
postgres=# SELECT * FROM person;
id | name | age
----+-------+-----
1 | John | 27
2 | David | 13
(2 rows)
And, you can use a USING
clause with an EXECUTE
statement in a DO
statement as shown below:
DO $$
DECLARE
my_age INT := 56;
my_id INT := 2;
BEGIN
EXECUTE 'UPDATE person SET age = 1 WHERE id = 2' USING my_age, my_id;
END
$$;
Or:
DO $$
DECLARE
my_age INT := 56;
my_id INT := 2;
BEGIN
EXECUTE 'UPDATE person SET age = $1 WHERE id = $2' USING my_age, my_id;
END
$$;
*Memos:
You must use
$1
and$2
in''
of theEXECUTE
statement instead ofmy_age
andmy_id
otherwise there is error.You must set
my_age
or$1
andmy_id
or$2
in theUSING
clause to use$1
and$2
respectively in''
of theEXECUTE
statement otherwise there is error.
Then, running the DO
statement above updates age
of David
to 56
as shown below:
postgres=# SELECT * FROM person;
id | name | age
----+-------+-----
1 | John | 27
2 | David | 56
(2 rows)
Top comments (0)