PREPARE and EXECUTE statements in PostgreSQL

Buy Me a Coffee☕

A PREPARE statement:

• can create a named query called a prepared statement to run it later with an EXECUTE statement. *The prepared statement is deleted after logout.

• can only have a SELECT, INSERT, UPDATE or DELETE 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 explains PREPARE statement.

*My answer explains how to show all the prepared statements in the current session.

*My answer explains how to delete the prepared statements in the current session.

An EXECUTE statement:

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

• can dynamically run a SELECT, INSERT, UPDATE, DELETE, EXECUTE, PREPARE, CREATE TABLE 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 explains an EXECUTE statement.

*The doc explains the EXECUTE statement for PL/pgSQL language(LANGUAGE plpgsql).

*My answer explains when error occurs with EXECUTE statement.

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

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:

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:

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:

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:

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 explains a PL/pgSQL function.

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

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 to update age with id in person table as shown below:

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

*Memos:

• My post has the examples of a EXECUTE statement with a FOR statement.

• My post has the examples of a EXECUTE statement with a FOR and RETURN NEXT statement.

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

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:

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:

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:

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 as I explains it in my answer.

• 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 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 statement, quote_ident(), quote_literal(), 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:

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:

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)