DO statement in PostgreSQL

Buy Me a Coffee☕

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 a DO 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 the EXECUTE statement instead of my_age and my_id otherwise there is error.

• You must set my_age or $1 and my_id or $2 in the USING clause to use $1 and $2 respectively in '' of the EXECUTE 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)