PREPARE and EXECUTE statements in PostgreSQL
Tags: postgres, prepare, execute, sql
Published: 2024-01-28 23:20:01

### A [PREPARE statement](https://www.postgresql.org/docs/current/sql-prepare.html):

- can create a named query called **a prepared statement** to run it later with an [EXECUTE statement](https://www.postgresql.org/docs/current/sql-execute.html). *The prepared statement is deleted after logout.

- can only have a [SELECT](https://www.postgresql.org/docs/current/sql-select.html), [INSERT](https://www.postgresql.org/docs/current/sql-insert.html), [UPDATE](https://www.postgresql.org/docs/current/sql-update.html) or [DELETE](https://www.postgresql.org/docs/current/sql-delete.html) statement.

- can have zero or more type-only parameters.

- can have the aliases of type-only parameters e.g. `$1`, `$2`, etc.

- cannot have multiple queries otherwise there is error.

- cannot have named parameters e.g. `num1 INT`, `num2 INT`, etc otherwise there is error.

- cannot have `()` just after the name if there are no parameters otherwise there is error so use `()` only if there are one or more parameters.

*[The doc](https://www.postgresql.org/docs/current/sql-prepare.html) explains `PREPARE` statement.

*[My answer](https://dba.stackexchange.com/questions/122236/is-it-possible-to-obtain-number-of-prepared-statements-a-connection-holds#335256) explains how to show all the prepared statements in the current session.

*[My answer](https://stackoverflow.com/questions/16965495/how-to-delete-a-prepared-statement-in-postgresql#77893504) explains how to delete the prepared statements in the current session.

### An [EXECUTE statement](https://www.postgresql.org/docs/current/sql-execute.html):

- can run a prepared statement without a function, procedure or `DO` statement.

- can dynamically run a [SELECT](https://www.postgresql.org/docs/current/sql-select.html), [INSERT](https://www.postgresql.org/docs/current/sql-insert.html), [UPDATE](https://www.postgresql.org/docs/current/sql-update.html), [DELETE](https://www.postgresql.org/docs/current/sql-delete.html), `EXECUTE`, `PREPARE`, [CREATE TABLE](https://www.postgresql.org/docs/current/sql-createtable.html) statement, etc with a function, procedure or `DO` statement.

- can be used in PL/pgSQL language(`LANGUAGE plpgsql`) for a function, procedure or `DO` statement.

- cannot be used in SQL language(`LANGUAGE SQL`) otherwise there is error.

*[The doc](https://www.postgresql.org/docs/current/sql-execute.html) explains an `EXECUTE` statement.

*[The doc](https://www.postgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN) explains the `EXECUTE` statement for PL/pgSQL language(`LANGUAGE plpgsql`).

*[My answer](https://stackoverflow.com/questions/12707417/using-a-prepared-statement-in-a-stored-function-in-postgresql/77907990#77907990) explains when error occurs with `EXECUTE` statement.

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);
```

Now, you can create the prepared statement `my_pre` with a `PREPARE` statement which can update `age` with `id` in `person` table as shown below:

```sql
PREPARE my_pre(INT, INT) AS
  UPDATE person SET age = $1 WHERE id = $2;
```

*Memos:

- `my_pre` has 2 parameters as above but if you create `my_pre` with no parameters, you have to omit `()` otherwise there is error.

And now, you can run `my_pre` with an `EXECUTE` statement as shown below:

```sql
EXECUTE my_pre(45, 2);
```

*Memos:

- If `my_pre` has no parameters, you must run it without `()` as `EXECUTE my_pre;` instead of `EXECUTE my_pre();` otherwise there is error.

Then, `age` of `David` is updated to `45` as shown below:

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

And, you can use the `EXECUTE` statement with the `EXECUTE` statement to run `my_pre` in the PL/pgSQL function `my_func()` as shown below:

```sql
CREATE FUNCTION my_func() RETURNS VOID
AS $$
BEGIN
  EXECUTE 'EXECUTE my_pre(18, 2)';
END;
$$ LANGUAGE plpgsql;
```

*Memos:

- `EXECUTE my_pre(18, 2);` gets error so you must use `EXECUTE 'EXECUTE my_pre(18, 2)';`.

- My question and the answers(12) explain how to use the `EXECUTE` statement with the `EXECUTE` statement to dynamically run `my_pre` in `my_func()`.

- [My post](https://dev.to/hyperkai/a-plpgsql-function-in-postgresql-3j9f) explains a PL/pgSQL function.

Then, calling `my_func()` updates `age` of `David` to `18` as shown below:

```sql
postgres=# SELECT my_func();
 my_func
---------

(1 row)

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

And, you can use the `EXECUTE` statement with the [UPDATE statement](https://www.postgresql.org/docs/current/sql-update.html) to update `age` with `id` in `person` table as shown below:

```sql
CREATE FUNCTION my_func() RETURNS VOID
AS $$
BEGIN
  EXECUTE 'UPDATE person SET age = 33 WHERE id = 2';
END;
$$ LANGUAGE plpgsql;
```

*Memos:

- [My post](https://dev.to/hyperkai/for-statement-in-postgresql-5d19) has the examples of a `EXECUTE` statement with a [FOR statement](https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING).

- [My post](https://dev.to/hyperkai/return-next-statement-in-postgresql-1lmi) has the examples of a `EXECUTE` statement with a `FOR` and [RETURN NEXT](https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING-RETURN-NEXT) statement.

Or, you can use `||` as shown below. *`||` can do concatenation with or without indentation:

```sql
CREATE FUNCTION my_func() RETURNS VOID
AS $$
BEGIN
  EXECUTE 'UPDATE person SET age = ' || 33 || ' WHERE id = ' || 2;
END;
$$ LANGUAGE plpgsql;
```

Then, calling `my_func()` updates `age` of `David` to `33` as shown below:

```sql
postgres=# SELECT my_func();
 my_func
---------

(1 row)

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

And, you can use a `USING` clause with an `EXECUTE` statement to dynamically update `age` with `id` in `person` table as shown below:

```sql
CREATE FUNCTION my_func(age INT, id INT) RETURNS VOID
AS $$
BEGIN
  EXECUTE 'UPDATE person SET age = $1 WHERE id = $2' USING age, id;
END;
$$ LANGUAGE plpgsql;
```

Or:

```sql
CREATE FUNCTION my_func(age INT, id INT) RETURNS VOID
AS $$
BEGIN
  EXECUTE 'UPDATE person SET age = $1 WHERE id = $2' USING $1, $2;
END;
$$ LANGUAGE plpgsql;
```

*Memos:

- You must use `$1` and `$2` instead of `age` and `id` in `''` of the `EXECUTE` statement otherwise there is error.

- You cannot directly use `age` or `$1` and `id` or `$2` in `''` of the `EXECUTE` statement without a `USING` clause otherwise there is [the error](https://stackoverflow.com/questions/9821121/sql-column-reference-id-is-ambiguous#77811511) as I explains it in [my answer](https://stackoverflow.com/questions/78004889/execute-statement-vs-no-execute-statement-in-a-function-in-postgresql#78004890).

- You can set `age` or `$1` and `id` or `$2` in the `USING` clause to use `$1` and `$2` in `''` of the `EXECUTE` statement.

- [My question and the answers](https://stackoverflow.com/questions/78004889/execute-statement-vs-no-execute-statement-in-a-function-in-postgresql) explains the difference between the function with and without an `EXECUTE` statement.

- My answer(4) explains how to create a dynamic function with an `EXECUTE` and [SELECT INTO](https://www.postgresql.org/docs/current/sql-selectinto.html) statement, [quote_ident()](https://www.postgresql.org/docs/current/functions-string.html), [quote_literal()](https://www.postgresql.org/docs/current/functions-string.html), [format()](https://www.postgresql.org/docs/current/functions-string.html#FUNCTIONS-STRING-FORMAT).

- My answer(5) explains how to create a dynamic function with an `EXECUTE` and `INSERT` statement, `format()` and `USING` clause:

Or, you can use `||` as shown below:

```sql
CREATE FUNCTION my_func(age INT, id INT) RETURNS VOID
AS $$
BEGIN
  EXECUTE 'UPDATE person SET age = ' || age || ' WHERE id = ' || id;
END;
$$ LANGUAGE plpgsql;
```

Then, calling `my_func()` updates `age` of `David` to `56` as shown below:

```sql
postgres=# SELECT my_func(56, 2);
 my_func
---------

(1 row)

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