Prepared Statements on PostgreSQL 8.4/9.0/9.1/9.2

Prepared Statements: 

PREPARE creates a prepared statement. A prepared statement is a server-side object that can be used to optimize performance. When the PREPARE statement is executed, the specified statement is parsed, analyzed, and rewritten. When an EXECUTE command is subsequently issued, the prepared statement is planned and executed. This division of labor avoids repetitive parse analysis work, while allowing the execution plan to depend on the specific parameter values supplied.



Prepared Statements usage examples on select/Update/Delete/Insert :  


Insert Prepared statement:

postgres=# PREPARE ins(int) as INSERT INTO test  values($1);
PREPARE
postgres=# select * from pg_prepared_statements ;
 name |                     statement                     |         prepare_time         | parameter_types | from_sql 
------+---------------------------------------------------+------------------------------+-----------------+----------
 ins  | PREPARE ins(int) as INSERT INTO test  values($1); | 2013-05-07 09:42:27.64714-07 | {integer}       | t
(1 row)

postgres=# select * from test;
 id 
----
(0 rows)

postgres=# EXECUTE ins(1);
INSERT 0 1
postgres=# EXECUTE ins(10);
INSERT 0 1
postgres=# select * from test;
 id 
----
  1
 10
(2 rows)

Update Prepared statement:

postgres=# PREPARE up(int,int) as UPDATE test set id=$1 where id=$2;
PREPARE
postgres=# EXECUTE up(100,10);
UPDATE 1
postgres=# select * from test;
 id  
-----
   1
 100
(2 rows)

Delete Prepared statement:
postgres=# PREPARE del(int) as DELETE FROM test where id=$1;
PREPARE
postgres=# EXECUTE del(1);
DELETE 1
postgres=# select * from test;
 id  
-----
 100
(1 row)

Select Prepared statement:

postgres=# 
postgres=# PREPARE sel(int) as select * from test where id=$1;
PREPARE
postgres=# EXECUTE sel(100);
 id  
-----
 100
(1 row)

View the existing prepared statements:

postgres=# select * from pg_prepared_statements ;
 name |                         statement                         |         prepare_time          |  parameter_types  | from_sql 
------+-----------------------------------------------------------+-------------------------------+-------------------+----------
 up   | PREPARE up(int,int) as UPDATE test set id=$1 where id=$2; | 2013-05-07 09:44:20.745465-07 | {integer,integer} | t
 del  | PREPARE del(int) as DELETE FROM test where id=$1;         | 2013-05-07 09:45:39.987117-07 | {integer}         | t
 ins  | PREPARE ins(int) as INSERT INTO test  values($1);         | 2013-05-07 09:42:27.64714-07  | {integer}         | t
 sel  | PREPARE sel(int) as select * from test where id=$1;       | 2013-05-07 09:47:08.865568-07 | {integer}         | t
(4 rows)

Remove/Deallocate the existing prepared statements:


postgres=# DEALLOCATE prepare sel;
DEALLOCATE
postgres=# select * from pg_prepared_statements ;
 name |                         statement                         |         prepare_time          |  parameter_types  | from_sql 
------+-----------------------------------------------------------+-------------------------------+-------------------+----------
 up   | PREPARE up(int,int) as UPDATE test set id=$1 where id=$2; | 2013-05-07 09:44:20.745465-07 | {integer,integer} | t
 del  | PREPARE del(int) as DELETE FROM test where id=$1;         | 2013-05-07 09:45:39.987117-07 | {integer}         | t
 ins  | PREPARE ins(int) as INSERT INTO test  values($1);         | 2013-05-07 09:42:27.64714-07  | {integer}         | t
(3 rows)

Remove/Deallocate All the existing prepared statements:

postgres=# DEALLOCATE prepare all;
DEALLOCATE ALL
postgres=# select * from pg_prepared_statements ;
 name | statement | prepare_time | parameter_types | from_sql 
------+-----------+--------------+-----------------+----------
(0 rows)

postgres=# 

======Thank You======