Joachim Wieland wrote:
I propose the attached patch for the TODO item:
* %Allow pooled connections to list all prepared queries
Attached is a revised version of this patch, based on some improvements
sent to me offlist by Joachim, as well as some code review and fixes by
myself. Changes:
- the query string in the view is produced by deparsing the parsetree
after the parse-analysis phase using adt/ruleutils (but before the
rewriter or planner have been invoked).
- two new columns: "parameter_types" is an array of oid that contains
the OIDs of the prepared statement's parameters, and "from_sql" is a
boolean field that is true if the prepared statement was prepared via
SQL, and false if it was prepared via the FE/BE protocol.
The docs need some improvement, but I'm not aware of any major remaining
issues with the patch. Comments are welcome -- barring any major
problems, I'll apply the patch tomorrow.
-Neil
Index: doc/src/sgml/catalogs.sgml
===================================================================
RCS file: /Users/neilc/postgres/cvs_root/pgsql/doc/src/sgml/catalogs.sgml,v
retrieving revision 2.115
diff -c -r2.115 catalogs.sgml
*** doc/src/sgml/catalogs.sgml 4 Nov 2005 23:13:59 -0000 2.115
--- doc/src/sgml/catalogs.sgml 1 Jan 2006 06:22:50 -0000
***************
*** 4373,4378 ****
--- 4373,4383 ----
</row>
<row>
+ <entry><link
linkend="view-pg-prepared-statements"><structname>pg_prepared_statements</structname></link></entry>
+ <entry>available prepared statements for the current session</entry>
+ </row>
+
+ <row>
<entry><link
linkend="view-pg-prepared-xacts"><structname>pg_prepared_xacts</structname></link></entry>
<entry>currently prepared transactions</entry>
</row>
***************
*** 4778,4783 ****
--- 4783,4883 ----
</sect1>
+ <sect1 id="view-pg-prepared-statements">
+ <title><structname>pg_prepared_statements</structname></title>
+
+ <indexterm zone="view-pg-prepared-statements">
+ <primary>pg_prepared_statements</primary>
+ </indexterm>
+
+ <para>
+ The view <structname>pg_prepared_statements</structname> displays all
+ available prepared statements for the current session. See <xref
+ linkend="sql-prepare" endterm="sql-prepare-title"> for more
+ information about prepared statements.
+ </para>
+
+ <para>
+ <structname>pg_prepared_statements</structname> contains one row per
+ prepared statement. New entries get added when preparing new statements,
+ an entry gets deleted from the view when the associated prepared
+ statement is released by means of the <xref linkend="sql-deallocate"
+ endterm="sql-deallocate-title"> command.
+ </para>
+
+ <table>
+ <title><structname>pg_prepared_statements</> Columns</title>
+
+ <tgroup cols=4>
+ <thead>
+ <row>
+ <entry>Name</entry>
+ <entry>Type</entry>
+ <entry>References</entry>
+ <entry>Description</entry>
+ </row>
+ </thead>
+ <tbody>
+ <row>
+ <entry><structfield>name</structfield></entry>
+ <entry><type>text</type></entry>
+ <entry></entry>
+ <entry>
+ The identifier of the prepared statement.
+ </entry>
+ </row>
+ <row>
+ <entry><structfield>statement</structfield></entry>
+ <entry><type>text</type></entry>
+ <entry></entry>
+ <entry>
+ The reverse compiled SQL command used for preparing this statement.
+ </entry>
+ </row>
+ <row>
+ <entry><structfield>prepare_time</structfield></entry>
+ <entry><type>timestamptz</type></entry>
+ <entry></entry>
+ <entry>
+ The time when the prepared statement was created.
+ </entry>
+ </row>
+ <row>
+ <entry><structfield>parameter_types</structfield></entry>
+ <entry><type>oid[]</type></entry>
+ <entry></entry>
+ <entry>
+ The expected parameter types for the prepared statement in the form of
+ an array of type OIDs.
+ </entry>
+ </row>
+ <row>
+ <entry><structfield>from_sql</structfield></entry>
+ <entry><type>boolean</type></entry>
+ <entry></entry>
+ <entry>
+ <literal>true</literal> is the prepared statement was created
+ via the <command>PREPARE</command> SQL statement;
+ <literal>false</literal> if the statement was prepared via the
+ frontend/backend protocol.
+ </entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ <para>
+ The <structname>pg_prepared_statements</structname> view is read only.
+ </para>
+
+ <para>
+ The <structfield>prepare_time</structfield> contains the time at
+ which the statement was prepared (and not the transaction start
+ time of the preparing transaction).
+ </para>
+
+ </sect1>
+
<sect1 id="view-pg-prepared-xacts">
<title><structname>pg_prepared_xacts</structname></title>
Index: doc/src/sgml/ref/prepare.sgml
===================================================================
RCS file: /Users/neilc/postgres/cvs_root/pgsql/doc/src/sgml/ref/prepare.sgml,v
retrieving revision 1.16
diff -c -r1.16 prepare.sgml
*** doc/src/sgml/ref/prepare.sgml 15 Oct 2005 01:47:12 -0000 1.16
--- doc/src/sgml/ref/prepare.sgml 1 Jan 2006 01:09:36 -0000
***************
*** 145,150 ****
--- 145,155 ----
the <xref linkend="sql-analyze" endterm="sql-analyze-title">
documentation.
</para>
+
+ <para>
+ You can see all available prepared statements of a session by querying the
+ <structname>pg_prepared_statements</> system view.
+ </para>
</refsect1>
<refsect1 id="sql-prepare-examples">
Index: src/backend/catalog/system_views.sql
===================================================================
RCS file:
/Users/neilc/postgres/cvs_root/pgsql/src/backend/catalog/system_views.sql,v
retrieving revision 1.22
diff -c -r1.22 system_views.sql
*** src/backend/catalog/system_views.sql 6 Oct 2005 02:29:15 -0000
1.22
--- src/backend/catalog/system_views.sql 1 Jan 2006 06:04:04 -0000
***************
*** 156,161 ****
--- 156,167 ----
LEFT JOIN pg_authid U ON P.ownerid = U.oid
LEFT JOIN pg_database D ON P.dbid = D.oid;
+ CREATE VIEW pg_prepared_statements AS
+ SELECT P.name, P.statement, P.prepare_time, P.parameter_types, P.from_sql
+ FROM pg_prepared_statement() AS P
+ (name text, statement text, prepare_time timestamptz,
+ parameter_types oid[], from_sql boolean);
+
CREATE VIEW pg_settings AS
SELECT *
FROM pg_show_all_settings() AS A
Index: src/backend/commands/prepare.c
===================================================================
RCS file: /Users/neilc/postgres/cvs_root/pgsql/src/backend/commands/prepare.c,v
retrieving revision 1.44
diff -c -r1.44 prepare.c
*** src/backend/commands/prepare.c 14 Dec 2005 17:06:27 -0000 1.44
--- src/backend/commands/prepare.c 2 Jan 2006 01:05:29 -0000
***************
*** 16,30 ****
*/
#include "postgres.h"
#include "commands/explain.h"
#include "commands/prepare.h"
#include "executor/executor.h"
! #include "utils/guc.h"
#include "optimizer/planner.h"
#include "rewrite/rewriteHandler.h"
#include "tcop/pquery.h"
#include "tcop/tcopprot.h"
#include "tcop/utility.h"
#include "utils/hsearch.h"
#include "utils/memutils.h"
--- 16,35 ----
*/
#include "postgres.h"
+ #include "access/heapam.h"
+ #include "catalog/pg_type.h"
#include "commands/explain.h"
#include "commands/prepare.h"
#include "executor/executor.h"
! #include "funcapi.h"
! #include "parser/parsetree.h"
#include "optimizer/planner.h"
#include "rewrite/rewriteHandler.h"
#include "tcop/pquery.h"
#include "tcop/tcopprot.h"
#include "tcop/utility.h"
+ #include "utils/builtins.h"
+ #include "utils/guc.h"
#include "utils/hsearch.h"
#include "utils/memutils.h"
***************
*** 40,45 ****
--- 45,51 ----
static void InitQueryHashTable(void);
static ParamListInfo EvaluateParams(EState *estate,
List *params, List *argtypes);
+ static Datum build_oid_array(List *oid_list);
/*
* Implements the 'PREPARE' utility statement.
***************
*** 51,56 ****
--- 57,63 ----
Query *query;
List *query_list,
*plan_list;
+ char *query_string_deparse;
/*
* Disallow empty-string statement name (conflicts with protocol-level
***************
*** 98,103 ****
--- 105,117 ----
*/
query = copyObject(stmt->query);
+ /*
+ * Produce an SQL string corresponding to the prepared statement
+ * by deparsing the analyzed parsetree. Note that this is done
+ * before invoking the rewriter or planner.
+ */
+ query_string_deparse = deparse_query_list(list_make1(query));
+
/* Rewrite the query. The result could be 0, 1, or many queries. */
AcquireRewriteLocks(query);
query_list = QueryRewrite(query);
***************
*** 111,120 ****
*/
StorePreparedStatement(stmt->name,
debug_query_string,
commandTag,
query_list,
plan_list,
! stmt->argtype_oids);
}
/*
--- 125,136 ----
*/
StorePreparedStatement(stmt->name,
debug_query_string,
+ query_string_deparse,
commandTag,
query_list,
plan_list,
! stmt->argtype_oids,
! true);
}
/*
***************
*** 295,309 ****
void
StorePreparedStatement(const char *stmt_name,
const char *query_string,
const char *commandTag,
List *query_list,
List *plan_list,
! List *argtype_list)
{
PreparedStatement *entry;
MemoryContext oldcxt,
entrycxt;
char *qstring;
char key[NAMEDATALEN];
bool found;
--- 311,328 ----
void
StorePreparedStatement(const char *stmt_name,
const char *query_string,
+ const char *query_string_deparse,
const char *commandTag,
List *query_list,
List *plan_list,
! List *argtype_list,
! bool from_sql)
{
PreparedStatement *entry;
MemoryContext oldcxt,
entrycxt;
char *qstring;
+ char *qstring_deparse;
char key[NAMEDATALEN];
bool found;
***************
*** 339,344 ****
--- 358,364 ----
* incomplete (ie corrupt) hashtable entry.
*/
qstring = query_string ? pstrdup(query_string) : NULL;
+ qstring_deparse = pstrdup(query_string_deparse);
query_list = (List *) copyObject(query_list);
plan_list = (List *) copyObject(plan_list);
argtype_list = list_copy(argtype_list);
***************
*** 356,366 ****
--- 376,389 ----
/* Fill in the hash table entry with copied data */
entry->query_string = qstring;
+ entry->query_string_pretty = qstring_deparse;
entry->commandTag = commandTag;
entry->query_list = query_list;
entry->plan_list = plan_list;
entry->argtype_list = argtype_list;
entry->context = entrycxt;
+ entry->prepare_time = GetCurrentTimestamp();
+ entry->from_sql = from_sql;
MemoryContextSwitchTo(oldcxt);
}
***************
*** 383,389 ****
{
/*
* We can't just use the statement name as supplied by the
user: the
! * hash package is picky enough that it needs to be NULL-padded
out to
* the appropriate length to work correctly.
*/
StrNCpy(key, stmt_name, sizeof(key));
--- 406,412 ----
{
/*
* We can't just use the statement name as supplied by the
user: the
! * hash package is picky enough that it needs to be NUL-padded
out to
* the appropriate length to work correctly.
*/
StrNCpy(key, stmt_name, sizeof(key));
***************
*** 661,663 ****
--- 684,801 ----
if (estate)
FreeExecutorState(estate);
}
+
+ /*
+ * This set returning function reads all the prepared statements and
+ * returns a set of (name, statement, prepare_time, param_types).
+ */
+ Datum
+ pg_prepared_statement(PG_FUNCTION_ARGS)
+ {
+ FuncCallContext *funcctx;
+ HASH_SEQ_STATUS *hash_seq;
+ PreparedStatement *prep_stmt;
+
+ /* stuff done only on the first call of the function */
+ if (SRF_IS_FIRSTCALL())
+ {
+ TupleDesc tupdesc;
+ MemoryContext oldcontext;
+
+ /* create a function context for cross-call persistence */
+ funcctx = SRF_FIRSTCALL_INIT();
+
+ /*
+ * switch to memory context appropriate for multiple function
+ * calls
+ */
+ oldcontext =
MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
+
+ /* allocate memory for user context */
+ if (prepared_queries)
+ {
+ hash_seq = (HASH_SEQ_STATUS *)
palloc(sizeof(HASH_SEQ_STATUS));
+ hash_seq_init(hash_seq, prepared_queries);
+ funcctx->user_fctx = (void *) hash_seq;
+ }
+ else
+ funcctx->user_fctx = NULL;
+
+ /*
+ * build tupdesc for result tuples. This must match the
+ * definition of the pg_prepared_statements view in
+ * system_views.sql
+ */
+ tupdesc = CreateTemplateTupleDesc(5, false);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 1, "name",
+ TEXTOID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 2, "statement",
+ TEXTOID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 3, "prepare_time",
+ TIMESTAMPTZOID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 4, "parameter_types",
+ OIDARRAYOID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 5, "from_sql",
+ BOOLOID, -1, 0);
+
+ funcctx->tuple_desc = BlessTupleDesc(tupdesc);
+ MemoryContextSwitchTo(oldcontext);
+ }
+
+ /* stuff done on every call of the function */
+ funcctx = SRF_PERCALL_SETUP();
+ hash_seq = (HASH_SEQ_STATUS *) funcctx->user_fctx;
+
+ /* if the hash table is uninitialized, we're done */
+ if (hash_seq == NULL)
+ SRF_RETURN_DONE(funcctx);
+
+ prep_stmt = hash_seq_search(hash_seq);
+ if (prep_stmt)
+ {
+ Datum result;
+ HeapTuple tuple;
+ Datum values[5];
+ bool nulls[5];
+
+ MemSet(nulls, 0, sizeof(nulls));
+ values[0] = DirectFunctionCall1(textin,
+
CStringGetDatum(prep_stmt->stmt_name));
+ values[1] = DirectFunctionCall1(textin,
+
CStringGetDatum(prep_stmt->query_string_pretty));
+ values[2] = TimestampTzGetDatum(prep_stmt->prepare_time);
+ values[3] = build_oid_array(prep_stmt->argtype_list);
+ values[4] = BoolGetDatum(prep_stmt->from_sql);
+
+ tuple = heap_form_tuple(funcctx->tuple_desc, values, nulls);
+ result = HeapTupleGetDatum(tuple);
+ SRF_RETURN_NEXT(funcctx, result);
+ }
+
+ SRF_RETURN_DONE(funcctx);
+ }
+
+ /*
+ * This utility function takes a List of Oids, and returns a Datum
+ * pointing to a Postgres array containing those OIDs.
+ */
+ static Datum
+ build_oid_array(List *oid_list)
+ {
+ ListCell *lc;
+ int len;
+ int i;
+ Datum *tmp_ary;
+ ArrayType *ary;
+
+ len = list_length(oid_list);
+ tmp_ary = (Datum *) palloc(len * sizeof(Datum));
+
+ i = 0;
+ foreach(lc, oid_list)
+ tmp_ary[i++] = ObjectIdGetDatum(lfirst_oid(lc));
+
+ /* XXX: this hardcodes assumptions about the OID type... */
+ ary = construct_array(tmp_ary, len, OIDOID, sizeof(Oid), true, 'i');
+ return PointerGetDatum(ary);
+ }
Index: src/backend/tcop/postgres.c
===================================================================
RCS file: /Users/neilc/postgres/cvs_root/pgsql/src/backend/tcop/postgres.c,v
retrieving revision 1.474
diff -c -r1.474 postgres.c
*** src/backend/tcop/postgres.c 31 Dec 2005 16:50:44 -0000 1.474
--- src/backend/tcop/postgres.c 2 Jan 2006 02:12:32 -0000
***************
*** 55,60 ****
--- 55,61 ----
#include "tcop/pquery.h"
#include "tcop/tcopprot.h"
#include "tcop/utility.h"
+ #include "utils/builtins.h"
#include "utils/flatfiles.h"
#include "utils/guc.h"
#include "utils/lsyscache.h"
***************
*** 1129,1134 ****
--- 1130,1136 ----
List *querytree_list,
*plantree_list,
*param_list;
+ char *query_string_deparse = NULL;
bool is_named;
bool save_log_statement_stats = log_statement_stats;
***************
*** 1271,1276 ****
--- 1273,1286 ----
if (log_parser_stats)
ShowUsage("PARSE ANALYSIS STATISTICS");
+ /*
+ * If this is a named statement, produce another version of
+ * query_string by deparsing the list of parsetrees. This is
+ * done before invoking the rewriter or planner.
+ */
+ if (is_named)
+ query_string_deparse =
deparse_query_list(querytree_list);
+
querytree_list = pg_rewrite_queries(querytree_list);
/*
***************
*** 1301,1310 ****
{
StorePreparedStatement(stmt_name,
query_string,
commandTag,
querytree_list,
plantree_list,
! param_list);
}
else
{
--- 1311,1322 ----
{
StorePreparedStatement(stmt_name,
query_string,
+ query_string_deparse,
commandTag,
querytree_list,
plantree_list,
! param_list,
! false);
}
else
{
***************
*** 1313,1323 ****
--- 1325,1337 ----
pstmt = (PreparedStatement *)
palloc0(sizeof(PreparedStatement));
/* query_string needs to be copied into unnamed_stmt_context */
pstmt->query_string = pstrdup(query_string);
+ pstmt->query_string_pretty = NULL; /* not needed */
/* the rest is there already */
pstmt->commandTag = commandTag;
pstmt->query_list = querytree_list;
pstmt->plan_list = plantree_list;
pstmt->argtype_list = param_list;
+ pstmt->from_sql = false;
pstmt->context = unnamed_stmt_context;
/* Now the unnamed statement is complete and valid */
unnamed_stmt_pstmt = pstmt;
Index: src/backend/utils/adt/ruleutils.c
===================================================================
RCS file:
/Users/neilc/postgres/cvs_root/pgsql/src/backend/utils/adt/ruleutils.c,v
retrieving revision 1.212
diff -c -r1.212 ruleutils.c
*** src/backend/utils/adt/ruleutils.c 30 Dec 2005 18:34:22 -0000 1.212
--- src/backend/utils/adt/ruleutils.c 2 Jan 2006 01:01:47 -0000
***************
*** 1351,1356 ****
--- 1351,1377 ----
showimplicit, 0, 0);
}
+ char *
+ deparse_query_list(List *query_list)
+ {
+ ListCell *q;
+ StringInfoData buf;
+
+ initStringInfo(&buf);
+ foreach(q, query_list)
+ {
+ Query *query = lfirst(q);
+
+ /* separate queries with semi-colons */
+ if (buf.data[0] != '\0')
+ appendStringInfo(&buf, "; ");
+
+ get_query_def(query, &buf, NIL, NULL, PRETTYFLAG_PAREN, 0);
+ }
+
+ return buf.data;
+ }
+
/* ----------
* deparse_expression_pretty - General utility for deparsing expressions
*
Index: src/include/catalog/pg_proc.h
===================================================================
RCS file: /Users/neilc/postgres/cvs_root/pgsql/src/include/catalog/pg_proc.h,v
retrieving revision 1.389
diff -c -r1.389 pg_proc.h
*** src/include/catalog/pg_proc.h 17 Nov 2005 22:14:54 -0000 1.389
--- src/include/catalog/pg_proc.h 1 Jan 2006 01:09:36 -0000
***************
*** 3617,3622 ****
--- 3617,3624 ----
DESCR("constraint description with pretty-print option");
DATA(insert OID = 2509 ( pg_get_expr PGNSP PGUID 12 f f t f s 3
25 "25 26 16" _null_ _null_ _null_ pg_get_expr_ext - _null_ ));
DESCR("deparse an encoded expression with pretty-print option");
+ DATA(insert OID = 2510 ( pg_prepared_statement PGNSP PGUID 12 f f t t s 0
2249 "" _null_ _null_ _null_ pg_prepared_statement - _null_ ));
+ DESCR("get the prepared statements for this session");
/* non-persistent series generator */
DATA(insert OID = 1066 ( generate_series PGNSP PGUID 12 f f t t v 3 23 "23
23 23" _null_ _null_ _null_ generate_series_step_int4 - _null_ ));
Index: src/include/catalog/pg_type.h
===================================================================
RCS file: /Users/neilc/postgres/cvs_root/pgsql/src/include/catalog/pg_type.h,v
retrieving revision 1.167
diff -c -r1.167 pg_type.h
*** src/include/catalog/pg_type.h 22 Nov 2005 18:17:30 -0000 1.167
--- src/include/catalog/pg_type.h 1 Jan 2006 03:12:52 -0000
***************
*** 406,411 ****
--- 406,412 ----
DATA(insert OID = 1008 ( _regproc PGNSP PGUID -1 f b t \054 0 24
array_in array_out array_recv array_send - i x f 0 -1 0 _null_ _null_ ));
DATA(insert OID = 1009 ( _text PGNSP PGUID -1 f b t \054 0
25 array_in array_out array_recv array_send - i x f 0 -1 0 _null_ _null_ ));
DATA(insert OID = 1028 ( _oid PGNSP PGUID -1 f b t \054 0
26 array_in array_out array_recv array_send - i x f 0 -1 0 _null_ _null_ ));
+ #define OIDARRAYOID 1028
DATA(insert OID = 1010 ( _tid PGNSP PGUID -1 f b t \054 0
27 array_in array_out array_recv array_send - i x f 0 -1 0 _null_ _null_ ));
DATA(insert OID = 1011 ( _xid PGNSP PGUID -1 f b t \054 0
28 array_in array_out array_recv array_send - i x f 0 -1 0 _null_ _null_ ));
DATA(insert OID = 1012 ( _cid PGNSP PGUID -1 f b t \054 0
29 array_in array_out array_recv array_send - i x f 0 -1 0 _null_ _null_ ));
Index: src/include/commands/prepare.h
===================================================================
RCS file: /Users/neilc/postgres/cvs_root/pgsql/src/include/commands/prepare.h,v
retrieving revision 1.16
diff -c -r1.16 prepare.h
*** src/include/commands/prepare.h 14 Dec 2005 17:06:28 -0000 1.16
--- src/include/commands/prepare.h 2 Jan 2006 00:44:06 -0000
***************
*** 30,42 ****
typedef struct
{
/* dynahash.c requires key to be first field */
! char stmt_name[NAMEDATALEN];
! char *query_string; /* text of query, or NULL */
! const char *commandTag; /* command tag (a constant!), or NULL */
! List *query_list; /* list of queries */
! List *plan_list; /* list of plans */
! List *argtype_list; /* list of parameter type OIDs */
! MemoryContext context; /* context containing this query */
} PreparedStatement;
--- 30,47 ----
typedef struct
{
/* dynahash.c requires key to be first field */
! char stmt_name[NAMEDATALEN];
! char *query_string; /* text of query, or NULL */
! char *query_string_pretty; /* text of queries, produced
!
* via deparsing the Query list */
! const char *commandTag; /* command tag (a constant!),
or NULL */
! List *query_list; /* list of queries, rewritten */
! List *plan_list; /* list of plans */
! List *argtype_list; /* list of parameter type OIDs
*/
! TimestampTz prepare_time; /* the time when the stmt was
prepared */
! bool from_sql; /* stmt prepared via
SQL, not
! *
FE/BE protocol? */
! MemoryContext context; /* context containing this
query */
} PreparedStatement;
***************
*** 51,60 ****
/* Low-level access to stored prepared statements */
extern void StorePreparedStatement(const char *stmt_name,
const char *query_string,
const char *commandTag,
List *query_list,
List *plan_list,
! List *argtype_list);
extern PreparedStatement *FetchPreparedStatement(const char *stmt_name,
bool throwError);
extern void DropPreparedStatement(const char *stmt_name, bool showError);
--- 56,67 ----
/* Low-level access to stored prepared statements */
extern void StorePreparedStatement(const char *stmt_name,
const char *query_string,
+ const char *query_string_deparse,
const char *commandTag,
List *query_list,
List *plan_list,
! List *argtype_list,
! bool from_sql);
extern PreparedStatement *FetchPreparedStatement(const char *stmt_name,
bool throwError);
extern void DropPreparedStatement(const char *stmt_name, bool showError);
Index: src/include/utils/builtins.h
===================================================================
RCS file: /Users/neilc/postgres/cvs_root/pgsql/src/include/utils/builtins.h,v
retrieving revision 1.268
diff -c -r1.268 builtins.h
*** src/include/utils/builtins.h 22 Nov 2005 18:17:32 -0000 1.268
--- src/include/utils/builtins.h 1 Jan 2006 03:01:25 -0000
***************
*** 513,518 ****
--- 513,519 ----
extern Datum pg_get_serial_sequence(PG_FUNCTION_ARGS);
extern char *deparse_expression(Node *expr, List *dpcontext,
bool forceprefix, bool showimplicit);
+ extern char *deparse_query_list(List *query_list);
extern List *deparse_context_for(const char *aliasname, Oid relid);
extern List *deparse_context_for_plan(int outer_varno, Node *outercontext,
int inner_varno, Node
*innercontext,
***************
*** 861,864 ****
--- 862,868 ----
/* catalog/pg_conversion.c */
extern Datum pg_convert_using(PG_FUNCTION_ARGS);
+ /* commands/prepare.c */
+ extern Datum pg_prepared_statement(PG_FUNCTION_ARGS);
+
#endif /* BUILTINS_H */
Index: src/test/regress/expected/prepare.out
===================================================================
RCS file:
/Users/neilc/postgres/cvs_root/pgsql/src/test/regress/expected/prepare.out,v
retrieving revision 1.5
diff -c -r1.5 prepare.out
*** src/test/regress/expected/prepare.out 20 Jul 2003 21:56:35 -0000
1.5
--- src/test/regress/expected/prepare.out 2 Jan 2006 01:41:25 -0000
***************
*** 1,9 ****
! -- Regression tests for prepareable statements
! PREPARE q1 AS SELECT 1;
EXECUTE q1;
! ?column?
! ----------
! 1
(1 row)
-- should fail
--- 1,22 ----
! -- 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;
! a
! ---
! 1
! (1 row)
!
! SELECT name, statement, parameter_types FROM pg_prepared_statements;
! name | statement | parameter_types
! ------+---------------+-----------------
! q1 | SELECT 1 AS a | {}
(1 row)
-- should fail
***************
*** 18,29 ****
--- 31,69 ----
2
(1 row)
+ PREPARE q2 AS SELECT 2 AS b;
+ SELECT name, statement, parameter_types FROM pg_prepared_statements;
+ name | statement | parameter_types
+ ------+---------------+-----------------
+ q1 | SELECT 2 | {}
+ q2 | 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 | 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 | SELECT datname, datistemplate, datallowconn FROM pg_database WHERE
datname::text = $1 | {25}
+ (1 row)
+
EXECUTE q2('regression');
datname | datistemplate | datallowconn
------------+---------------+--------------
***************
*** 33,38 ****
--- 73,85 ----
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 | SELECT datname, datistemplate, datallowconn FROM pg_database WHERE
datname::text = $1
| {25}
+ q3 | SELECT unique1, unique2, two, four, ten, twenty, hundred, thousand,
twothousand, fivethous, tenthous, odd, even, stringu1, stringu2, string4 FROM
tenk1 WHERE string4::text = $1 AND (four = $2 OR ten = $3::bigint OR true = $4
OR oid = $5 OR odd = $6::integer) | {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
---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------
Index: src/test/regress/expected/rules.out
===================================================================
RCS file:
/Users/neilc/postgres/cvs_root/pgsql/src/test/regress/expected/rules.out,v
retrieving revision 1.110
diff -c -r1.110 rules.out
*** src/test/regress/expected/rules.out 28 Nov 2005 04:35:32 -0000 1.110
--- src/test/regress/expected/rules.out 2 Jan 2006 01:41:36 -0000
***************
*** 1280,1285 ****
--- 1280,1286 ----
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,1326 ****
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)
SELECT tablename, rulename, definition FROM pg_rules
ORDER BY tablename, rulename;
--- 1321,1327 ----
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;
! (45 rows)
SELECT tablename, rulename, definition FROM pg_rules
ORDER BY tablename, rulename;
Index: src/test/regress/sql/prepare.sql
===================================================================
RCS file:
/Users/neilc/postgres/cvs_root/pgsql/src/test/regress/sql/prepare.sql,v
retrieving revision 1.2
diff -c -r1.2 prepare.sql
*** src/test/regress/sql/prepare.sql 1 Jul 2003 00:04:31 -0000 1.2
--- src/test/regress/sql/prepare.sql 1 Jan 2006 01:31:20 -0000
***************
*** 1,8 ****
! -- Regression tests for prepareable statements
! PREPARE q1 AS SELECT 1;
EXECUTE q1;
-- should fail
PREPARE q1 AS SELECT 2;
--- 1,14 ----
! -- 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;
!
! 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,29 ****
--- 17,49 ----
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
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
