Add a new system view, pg_prepared_statements, that can be used to

access information about the prepared statements that are available
in the current session. Original patch from Joachim Wieland, various
improvements by Neil Conway.

The "statement" column of the view contains the literal query string
sent by the client, without any rewriting or pretty printing. This
means that prepared statements created via SQL will be prefixed with
"PREPARE ... AS ", whereas those prepared via the FE/BE protocol will
not. That is unfortunate, but discussion on -patches did not yield an
efficient way to improve this, and there is some merit in returning
exactly what the client sent to the backend.

Catalog version bumped, regression tests updated.

3 files changed, 82 insertions, 8 deletions

diff --git a/src/test/regress/expected/prepare.out b/src/test/regress/expected/prepare.out
index 43fd8ecf56c..54616199b60 100644
--- a/src/test/regress/expected/prepare.out
+++ b/src/test/regress/expected/prepare.out
@@ -1,9 +1,22 @@
--- Regression tests for prepareable statements
-PREPARE q1 AS SELECT 1;
+-- Regression tests for prepareable statements. We query the content
+-- of the pg_prepared_statements view as prepared statements are
+-- created and removed.
+SELECT name, statement, parameter_types FROM pg_prepared_statements;
+ name | statement | parameter_types 
+------+-----------+-----------------
+(0 rows)
+
+PREPARE q1 AS SELECT 1 AS a;
 EXECUTE q1;
- ?column? 
-----------
-        1
+ a 
+---
+ 1
+(1 row)
+
+SELECT name, statement, parameter_types FROM pg_prepared_statements;
+ name |          statement           | parameter_types 
+------+------------------------------+-----------------
+ q1   | PREPARE q1 AS SELECT 1 AS a; | {}
 (1 row)
 
 -- should fail
@@ -18,12 +31,41 @@ EXECUTE q1;
         2
 (1 row)
 
+PREPARE q2 AS SELECT 2 AS b;
+SELECT name, statement, parameter_types FROM pg_prepared_statements;
+ name |          statement           | parameter_types 
+------+------------------------------+-----------------
+ q1   | PREPARE q1 AS SELECT 2;      | {}
+ q2   | PREPARE q2 AS SELECT 2 AS b; | {}
+(2 rows)
+
 -- sql92 syntax
 DEALLOCATE PREPARE q1;
+SELECT name, statement, parameter_types FROM pg_prepared_statements;
+ name |          statement           | parameter_types 
+------+------------------------------+-----------------
+ q2   | PREPARE q2 AS SELECT 2 AS b; | {}
+(1 row)
+
+DEALLOCATE PREPARE q2;
+-- the view should return the empty set again
+SELECT name, statement, parameter_types FROM pg_prepared_statements;
+ name | statement | parameter_types 
+------+-----------+-----------------
+(0 rows)
+
 -- parameterized queries
 PREPARE q2(text) AS
 	SELECT datname, datistemplate, datallowconn
 	FROM pg_database WHERE datname = $1;
+SELECT name, statement, parameter_types FROM pg_prepared_statements;
+ name |                                               statement                                                | parameter_types 
+------+--------------------------------------------------------------------------------------------------------+-----------------
+ q2   | PREPARE q2(text) AS
+	SELECT datname, datistemplate, datallowconn
+	FROM pg_database WHERE datname = $1; | {25}
+(1 row)
+
 EXECUTE q2('regression');
   datname   | datistemplate | datallowconn 
 ------------+---------------+--------------
@@ -33,6 +75,17 @@ EXECUTE q2('regression');
 PREPARE q3(text, int, float, boolean, oid, smallint) AS
 	SELECT * FROM tenk1 WHERE string4 = $1 AND (four = $2 OR
 	ten = $3::bigint OR true = $4 OR oid = $5 OR odd = $6::int);
+SELECT name, statement, parameter_types FROM pg_prepared_statements;
+ name |                                                                                    statement                                                                                    |   parameter_types    
+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------
+ q2   | PREPARE q2(text) AS
+	SELECT datname, datistemplate, datallowconn
+	FROM pg_database WHERE datname = $1;                                                                          | {25}
+ q3   | PREPARE q3(text, int, float, boolean, oid, smallint) AS
+	SELECT * FROM tenk1 WHERE string4 = $1 AND (four = $2 OR
+	ten = $3::bigint OR true = $4 OR oid = $5 OR odd = $6::int); | {25,23,701,16,26,21}
+(2 rows)
+
 EXECUTE q3('AAAAxx', 5::smallint, 10.5::float, false, 500::oid, 4::bigint);
  unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 
 ---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 2c8070d1195..67616b73335 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1280,6 +1280,7 @@ SELECT viewname, definition FROM pg_views WHERE schemaname <> 'information_schem
  pg_group                 | SELECT pg_authid.rolname AS groname, pg_authid.oid AS grosysid, ARRAY(SELECT pg_auth_members.member FROM pg_auth_members WHERE (pg_auth_members.roleid = pg_authid.oid)) AS grolist FROM pg_authid WHERE (NOT pg_authid.rolcanlogin);
  pg_indexes               | SELECT n.nspname AS schemaname, c.relname AS tablename, i.relname AS indexname, t.spcname AS "tablespace", pg_get_indexdef(i.oid) AS indexdef FROM ((((pg_index x JOIN pg_class c ON ((c.oid = x.indrelid))) JOIN pg_class i ON ((i.oid = x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) LEFT JOIN pg_tablespace t ON ((t.oid = i.reltablespace))) WHERE ((c.relkind = 'r'::"char") AND (i.relkind = 'i'::"char"));
  pg_locks                 | SELECT l.locktype, l."database", l.relation, l.page, l.tuple, l.transactionid, l.classid, l.objid, l.objsubid, l."transaction", l.pid, l."mode", l."granted" FROM pg_lock_status() l(locktype text, "database" oid, relation oid, page integer, tuple smallint, transactionid xid, classid oid, objid oid, objsubid smallint, "transaction" xid, pid integer, "mode" text, "granted" boolean);
+ pg_prepared_statements   | SELECT p.name, p."statement", p.prepare_time, p.parameter_types, p.from_sql FROM pg_prepared_statement() p(name text, "statement" text, prepare_time timestamp with time zone, parameter_types oid[], from_sql boolean);
  pg_prepared_xacts        | SELECT p."transaction", p.gid, p."prepared", u.rolname AS "owner", d.datname AS "database" FROM ((pg_prepared_xact() p("transaction" xid, gid text, "prepared" timestamp with time zone, ownerid oid, dbid oid) LEFT JOIN pg_authid u ON ((p.ownerid = u.oid))) LEFT JOIN pg_database d ON ((p.dbid = d.oid)));
  pg_roles                 | SELECT pg_authid.rolname, pg_authid.rolsuper, pg_authid.rolinherit, pg_authid.rolcreaterole, pg_authid.rolcreatedb, pg_authid.rolcatupdate, pg_authid.rolcanlogin, pg_authid.rolconnlimit, '********'::text AS rolpassword, pg_authid.rolvaliduntil, pg_authid.rolconfig, pg_authid.oid FROM pg_authid;
  pg_rules                 | SELECT n.nspname AS schemaname, c.relname AS tablename, r.rulename, pg_get_ruledef(r.oid) AS definition FROM ((pg_rewrite r JOIN pg_class c ON ((c.oid = r.ev_class))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (r.rulename <> '_RETURN'::name);
@@ -1320,7 +1321,7 @@ SELECT viewname, definition FROM pg_views WHERE schemaname <> 'information_schem
  shoelace_obsolete        | SELECT shoelace.sl_name, shoelace.sl_avail, shoelace.sl_color, shoelace.sl_len, shoelace.sl_unit, shoelace.sl_len_cm FROM shoelace WHERE (NOT (EXISTS (SELECT shoe.shoename FROM shoe WHERE (shoe.slcolor = shoelace.sl_color))));
  street                   | SELECT r.name, r.thepath, c.cname FROM ONLY road r, real_city c WHERE (c.outline ## r.thepath);
  toyemp                   | SELECT emp.name, emp.age, emp."location", (12 * emp.salary) AS annualsal FROM emp;
-(44 rows)
+(45 rows)
 
 SELECT tablename, rulename, definition FROM pg_rules 
 	ORDER BY tablename, rulename;
diff --git a/src/test/regress/sql/prepare.sql b/src/test/regress/sql/prepare.sql
index fc6924307da..95db2a0910f 100644
--- a/src/test/regress/sql/prepare.sql
+++ b/src/test/regress/sql/prepare.sql
@@ -1,8 +1,14 @@
--- Regression tests for prepareable statements
+-- Regression tests for prepareable statements. We query the content
+-- of the pg_prepared_statements view as prepared statements are
+-- created and removed.
 
-PREPARE q1 AS SELECT 1;
+SELECT name, statement, parameter_types FROM pg_prepared_statements;
+
+PREPARE q1 AS SELECT 1 AS a;
 EXECUTE q1;
 
+SELECT name, statement, parameter_types FROM pg_prepared_statements;
+
 -- should fail
 PREPARE q1 AS SELECT 2;
 
@@ -11,19 +17,33 @@ DEALLOCATE q1;
 PREPARE q1 AS SELECT 2;
 EXECUTE q1;
 
+PREPARE q2 AS SELECT 2 AS b;
+SELECT name, statement, parameter_types FROM pg_prepared_statements;
+
 -- sql92 syntax
 DEALLOCATE PREPARE q1;
 
+SELECT name, statement, parameter_types FROM pg_prepared_statements;
+
+DEALLOCATE PREPARE q2;
+-- the view should return the empty set again
+SELECT name, statement, parameter_types FROM pg_prepared_statements;
+
 -- parameterized queries
 PREPARE q2(text) AS
 	SELECT datname, datistemplate, datallowconn
 	FROM pg_database WHERE datname = $1;
+
+SELECT name, statement, parameter_types FROM pg_prepared_statements;
+
 EXECUTE q2('regression');
 
 PREPARE q3(text, int, float, boolean, oid, smallint) AS
 	SELECT * FROM tenk1 WHERE string4 = $1 AND (four = $2 OR
 	ten = $3::bigint OR true = $4 OR oid = $5 OR odd = $6::int);
 
+SELECT name, statement, parameter_types FROM pg_prepared_statements;
+
 EXECUTE q3('AAAAxx', 5::smallint, 10.5::float, false, 500::oid, 4::bigint);
 
 -- too few params