Add a new system view, pg_cursors, that displays the currently available

cursors. Patch from Joachim Wieland, review and ediorialization by Neil
Conway. The view lists cursors defined by DECLARE CURSOR, using SPI, or
via the Bind message of the frontend/backend protocol. This means the
view does not list the unnamed portal or the portal created to implement
EXECUTE. Because we do list SPI portals, there might be more rows in
this view than you might expect if you are using SPI implicitly (e.g.
via a procedural language).

Per recent discussion on -hackers, the query string included in the
view for cursors defined by DECLARE CURSOR is based on
debug_query_string. That means it is not accurate if multiple queries
separated by semicolons are submitted as one query string. However,
there doesn't seem a trivial fix for that: debug_query_string
is better than nothing. I also changed SPI_cursor_open() to include
the source text for the portal it creates: AFAICS there is no reason
not to do this.

Update the documentation and regression tests, bump the catversion.

4 files changed, 14 insertions, 6 deletions

diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h
index b844196c4ff..2742b700275 100644
--- a/src/include/catalog/catversion.h
+++ b/src/include/catalog/catversion.h
@@ -37,7 +37,7 @@
  * Portions Copyright (c) 1996-2005, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.311 2006/01/16 18:15:30 neilc Exp $
+ * $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.312 2006/01/18 06:49:27 neilc Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -53,6 +53,6 @@
  */
 
 /*							yyyymmddN */
-#define CATALOG_VERSION_NO	200601161
+#define CATALOG_VERSION_NO	200601181
 
 #endif
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index 6f5ec17c451..5c54d831cdb 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -7,7 +7,7 @@
  * Portions Copyright (c) 1996-2005, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.391 2006/01/11 20:12:39 tgl Exp $
+ * $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.392 2006/01/18 06:49:28 neilc Exp $
  *
  * NOTES
  *	  The script catalog/genbki.sh reads this file and generates .bki
@@ -3621,6 +3621,8 @@ DATA(insert OID = 2509 (  pg_get_expr		   PGNSP PGUID 12 f f t f s 3 25 "25 26 1
 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");
+DATA(insert OID = 2511 (  pg_cursor PGNSP PGUID 12 f f t t s 0 2249 "" _null_ _null_ _null_ pg_cursor - _null_ ));
+DESCR("get the open cursors 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_ ));
diff --git a/src/include/utils/builtins.h b/src/include/utils/builtins.h
index 8ca212852c2..e8ad4bd0e21 100644
--- a/src/include/utils/builtins.h
+++ b/src/include/utils/builtins.h
@@ -7,7 +7,7 @@
  * Portions Copyright (c) 1996-2005, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $PostgreSQL: pgsql/src/include/utils/builtins.h,v 1.270 2006/01/11 20:12:42 tgl Exp $
+ * $PostgreSQL: pgsql/src/include/utils/builtins.h,v 1.271 2006/01/18 06:49:29 neilc Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -865,4 +865,7 @@ extern Datum pg_convert_using(PG_FUNCTION_ARGS);
 /* commands/prepare.c */
 extern Datum pg_prepared_statement(PG_FUNCTION_ARGS);
 
+/* utils/mmgr/portalmem.c */
+extern Datum pg_cursor(PG_FUNCTION_ARGS);
+
 #endif   /* BUILTINS_H */
diff --git a/src/include/utils/portal.h b/src/include/utils/portal.h
index 758592525ff..dca2ce8af3e 100644
--- a/src/include/utils/portal.h
+++ b/src/include/utils/portal.h
@@ -39,7 +39,7 @@
  * Portions Copyright (c) 1996-2005, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $PostgreSQL: pgsql/src/include/utils/portal.h,v 1.57 2005/10/15 02:49:46 momjian Exp $
+ * $PostgreSQL: pgsql/src/include/utils/portal.h,v 1.58 2006/01/18 06:49:29 neilc Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -72,7 +72,6 @@
  * PORTAL_MULTI_QUERY: all other cases.  Here, we do not support partial
  * execution: the portal's queries will be run to completion on first call.
  */
-
 typedef enum PortalStrategy
 {
 	PORTAL_ONE_SELECT,
@@ -166,6 +165,10 @@ typedef struct PortalData
 	bool		atEnd;
 	bool		posOverflow;
 	long		portalPos;
+
+	/* Presentation data, primarily used by the pg_cursors system view */
+	TimestampTz	creation_time;	/* time at which this portal was defined */
+	bool		visible;		/* include this portal in pg_cursors? */
 } PortalData;
 
 /*