CODEWITHSUNDEEP
sqldatabasepostgresqlprepared-statementlibpg
Malkocoglu
Malkocoglu

Reputation: 2611

How to delete a prepared statement in PostgreSQL?

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...

Upvotes: 9

Views: 14733

Answers (3)

Super Kai - Kazuya Ito
Super Kai - Kazuya Ito

Reputation: 1

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.

Upvotes: 5

Mike Kravtsov
Mike Kravtsov

Reputation: 81

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

Upvotes: 6

Daniel V&#233;rit&#233;
Daniel V&#233;rit&#233;

Reputation: 61626

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

Upvotes: 14

Related Questions