DO statement in PostgreSQL
Tags: postgres, dostatement, do, plpgsql
Published: 2024-01-25 17:53:44

### [DO statement](https://www.postgresql.org/docs/current/sql-do.html):

- can have zero or more queries with `BEGIN ... END` statement. *Be careful, `BEGIN ... END` clause is not [transaction](https://www.postgresql.org/docs/current/tutorial-transactions.html).

- can have [DECLARE clause](https://www.postgresql.org/docs/current/plpgsql-declarations.html). *[My post](https://dev.to/hyperkai/declarations-in-postgresql-357o) explains how to declare local variables in `DECLARE` clause.

- can have [SELECT INTO statement](https://www.postgresql.org/docs/current/sql-selectinto.html).

- cannot have [RETURN NEXT](https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING-RETURN-NEXT) and [RETURN QUERY statement](https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING-RETURN-NEXT).

- can have [PERFORM statement](https://www.postgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-GENERAL-SQL).

- can have [EXECUTE](https://www.postgresql.org/docs/current/sql-execute.html). *[My post](https://dev.to/hyperkai/prepare-and-execute-statements-in-postgresql-3bhc) explains how to use `EXECUTE` statement in a funcition.

- can have [RETURN statement](https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING-RETURN) without a value which is `RETURN;`.

- cannot have `RETURN` statement with a value e.g. `RETURN 2;` otherwise there is [the error](https://stackoverflow.com/questions/17698503/plpgsql-error-return-cannot-have-a-parameter-in-function-returning-void#77609986).

- cannot have [SELECT statement](https://www.postgresql.org/docs/current/sql-select.html) without `INTO` as a non-substatement otherwise there is [the error](https://stackoverflow.com/questions/23946735/postgresql-query-has-no-destination-for-result-data#77642993).

- 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](https://www.postgresql.org/docs/current/sql-do.html) explains `DO` statement.
*[My post](https://dev.to/hyperkai/a-plpgsql-function-in-postgresql-3j9f) explains a PL/pgSQL function.
*[My post](https://dev.to/hyperkai/a-plpgsql-procedure-31nd) explains a PL/pgSQL procedure.
*[My post](https://dev.to/hyperkai/a-sql-function-in-postgresql-54oh) explains a SQL function.
*[My post](https://dev.to/hyperkai/a-sql-procedure-in-postgresql-41e0) 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:

```sql
CREATE TABLE test (
  num INT
);
```

Then, you insert the row whose `num` is `0` into `test` table as shown below:

```sql
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](https://www.postgresql.org/docs/current/plpgsql-errors-and-messages.html) as shown below. *You can omit the `DECLARE` clause, if it is not necessary:

```sql
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:

```sql
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:

```sql
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:

```sql
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:

```sql
CREATE TABLE person (
  id INT,
  name VARCHAR(20),
  age INT
);
```

Then, you insert 2 rows into `person` table as shown below:

```sql
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](https://dev.to/hyperkai/for-statement-in-postgresql-5d19) has the example of a `DO` statement with a [FOR](https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING) statement:

```sql
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:

```sql
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:

```sql
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:

```sql
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:

```sql
postgres=# SELECT * FROM person;
 id | name  | age
----+-------+-----
  1 | John  |  27
  2 | David |  56
(2 rows)
```