How to delete a prepared statement in PostgreSQL?

Question

I already create & use prepared statements by utilizing the libpq (of PostgreSQL). I am wondering if there is a way to delete a prepared statement without disconnecting the database? Or the best way to achieve this is to reconnect & re-prepare?

I am using the libpq of PostgreSQL version 8.4. I searched the 9.2 documentation but could not find anything related to this...

Answer 1

According to the documentation, DEALLOCATE is the only way to delete a prepared statement, emphasis added:

Prepared statements for use with PQexecPrepared can also be created by executing SQL PREPARE statements. Also, although there is no libpq function for deleting a prepared statement, the SQL DEALLOCATE statement can be used for that purpose.

Presumably they did not bother to expose a C function for this because this would be as simple as:

  char query[NAMEDATALEN+12];
  snprintf(query, sizeof(query), "DEALLOCATE %s", stmtName);
  return PQexec(conn, query);

Answer 2

As @Amit mentioned, sometimes Postgress throws "Prepared statement doesn't exists."

Unfortunately, there is no DEALLOCATE IF EXISTS, but a simple workaround is: DEALLOCATE ALL;.

Answer 3

You can delete one prepared statement in the current session:

DEALLOCATE PREPARE <name>;

And, you can delete all the prepared statements in the current session:

DEALLOCATE PREPARE ALL;

*Memos:

• You can omit PREPARE which is optional.

• Prepared statements are deleted after logout.

• The doc explains DEALLOCATE statement in detail.