aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--doc/src/sgml/catalogs.sgml132
-rw-r--r--doc/src/sgml/protocol.sgml4
-rw-r--r--doc/src/sgml/ref/close.sgml7
-rw-r--r--doc/src/sgml/ref/declare.sgml7
-rw-r--r--src/backend/catalog/system_views.sql9
-rw-r--r--src/backend/commands/portalcmds.c9
-rw-r--r--src/backend/commands/prepare.c10
-rw-r--r--src/backend/executor/spi.c6
-rw-r--r--src/backend/tcop/postgres.c4
-rw-r--r--src/backend/utils/mmgr/portalmem.c123
-rw-r--r--src/include/catalog/catversion.h4
-rw-r--r--src/include/catalog/pg_proc.h4
-rw-r--r--src/include/utils/builtins.h5
-rw-r--r--src/include/utils/portal.h7
-rw-r--r--src/test/regress/expected/portals.out61
-rw-r--r--src/test/regress/expected/rules.out3
-rw-r--r--src/test/regress/sql/portals.sql25
17 files changed, 388 insertions, 32 deletions
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index c346005bd96..aee68c2f893 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -1,6 +1,6 @@
<!--
Documentation of the system catalogs, directed toward PostgreSQL developers
- $PostgreSQL: pgsql/doc/src/sgml/catalogs.sgml,v 2.117 2006/01/16 18:15:30 neilc Exp $
+ $PostgreSQL: pgsql/doc/src/sgml/catalogs.sgml,v 2.118 2006/01/18 06:49:25 neilc Exp $
-->
<chapter id="catalogs">
@@ -4360,6 +4360,11 @@
<tbody>
<row>
+ <entry><link linkend="view-pg-cursors"><structname>pg_cursors</structname></link></entry>
+ <entry>open cursors</entry>
+ </row>
+
+ <row>
<entry><link linkend="view-pg-group"><structname>pg_group</structname></link></entry>
<entry>groups of database users</entry>
</row>
@@ -4429,6 +4434,131 @@
</table>
</sect1>
+ <sect1 id="view-pg-cursors">
+ <title><structname>pg_cursors</structname></title>
+
+ <indexterm zone="view-pg-cursors">
+ <primary>pg_cursors</primary>
+ </indexterm>
+
+ <para>
+ The <structname>pg_cursors</structname> view lists the cursors that
+ are currently available. Cursors can be defined in several ways:
+ <itemizedlist>
+ <listitem>
+ <para>
+ via the <xref linkend="sql-declare" endterm="sql-declare-title">
+ statement in SQL
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ via the Bind message in the frontend/backend protocol, as
+ described in <xref linkend="protocol-flow-ext-query">
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ via the Server Programming Interface (SPI), as described in
+ <xref linkend="spi-interface">
+ </itemizedlist>
+
+ The <structname>pg_cursors</structname> view displays cursors
+ created by any of these means. Cursors only exist for the duration
+ of the transaction that defines them, unless they have been
+ declared <literal>WITH HOLD</literal>. Therefore non-holdable
+ cursors are only present in the view until the end of their
+ creating transaction.
+
+ <note>
+ <para>
+ Cursors are used internally to implement some of the components
+ of <productname>PostgreSQL</>, such as procedural languages.
+ Therefore, the <structname>pg_cursors</> view may include cursors
+ that have not been explicitly created by the user.
+ </para>
+ </note>
+ </para>
+
+ <table>
+ <title><structname>pg_cursors</> 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 name of the cursor</entry>
+ </row>
+
+ <row>
+ <entry><structfield>statement</structfield></entry>
+ <entry><type>text</type></entry>
+ <entry></entry>
+ <entry>The verbatim query string submitted to declare this cursor</entry>
+ </row>
+
+ <row>
+ <entry><structfield>is_holdable</structfield></entry>
+ <entry><type>boolean</type></entry>
+ <entry></entry>
+ <entry>
+ <literal>true</literal> if the cursor is holdable (that is, it
+ can be accessed after the transaction that declared the cursor
+ has committed); <literal>false</literal> otherwise
+ </entry>
+ </row>
+
+ <row>
+ <entry><structfield>is_binary</structfield></entry>
+ <entry><type>boolean</type></entry>
+ <entry></entry>
+ <entry>
+ <literal>true</literal> if the cursor was declared
+ <literal>BINARY</literal>; <literal>false</literal>
+ otherwise
+ </entry>
+ </row>
+
+ <row>
+ <entry><structfield>is_scrollable</structfield></entry>
+ <entry><type>boolean</type></entry>
+ <entry></entry>
+ <entry>
+ <literal>true</> if the cursor is scrollable (that is, it
+ allows rows to be retrieved in a nonsequential manner);
+ <literal>false</literal> otherwise
+ </entry>
+ </row>
+
+ <row>
+ <entry><structfield>creation_time</structfield></entry>
+ <entry><type>timestamptz</type></entry>
+ <entry></entry>
+ <entry>The time at which the cursor was declared</entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ <para>
+ The <structname>pg_cursors</structname> view is read only.
+ </para>
+
+ </sect1>
+
<sect1 id="view-pg-group">
<title><structname>pg_group</structname></title>
diff --git a/doc/src/sgml/protocol.sgml b/doc/src/sgml/protocol.sgml
index 07c9c642c75..689460ed92f 100644
--- a/doc/src/sgml/protocol.sgml
+++ b/doc/src/sgml/protocol.sgml
@@ -1,4 +1,4 @@
-<!-- $PostgreSQL: pgsql/doc/src/sgml/protocol.sgml,v 1.62 2005/08/14 22:19:49 petere Exp $ -->
+<!-- $PostgreSQL: pgsql/doc/src/sgml/protocol.sgml,v 1.63 2006/01/18 06:49:25 neilc Exp $ -->
<chapter id="protocol">
<title>Frontend/Backend Protocol</title>
@@ -602,7 +602,7 @@
</para>
</sect2>
- <sect2>
+ <sect2 id="protocol-flow-ext-query">
<title>Extended Query</title>
<para>
diff --git a/doc/src/sgml/ref/close.sgml b/doc/src/sgml/ref/close.sgml
index 3376476e26d..35f0993e6a2 100644
--- a/doc/src/sgml/ref/close.sgml
+++ b/doc/src/sgml/ref/close.sgml
@@ -1,5 +1,5 @@
<!--
-$PostgreSQL: pgsql/doc/src/sgml/ref/close.sgml,v 1.22 2005/01/04 00:39:53 tgl Exp $
+$PostgreSQL: pgsql/doc/src/sgml/ref/close.sgml,v 1.23 2006/01/18 06:49:26 neilc Exp $
PostgreSQL documentation
-->
@@ -76,6 +76,11 @@ CLOSE <replaceable class="PARAMETER">name</replaceable>
<xref linkend="sql-declare" endterm="sql-declare-title">
statement to declare a cursor.
</para>
+
+ <para>
+ You can see all available cursors by querying the
+ <structname>pg_cursors</structname> system view.
+ </para>
</refsect1>
<refsect1>
diff --git a/doc/src/sgml/ref/declare.sgml b/doc/src/sgml/ref/declare.sgml
index 0263145927d..f43e08f970d 100644
--- a/doc/src/sgml/ref/declare.sgml
+++ b/doc/src/sgml/ref/declare.sgml
@@ -1,5 +1,5 @@
<!--
-$PostgreSQL: pgsql/doc/src/sgml/ref/declare.sgml,v 1.33 2005/01/04 00:39:53 tgl Exp $
+$PostgreSQL: pgsql/doc/src/sgml/ref/declare.sgml,v 1.34 2006/01/18 06:49:26 neilc Exp $
PostgreSQL documentation
-->
@@ -253,6 +253,11 @@ DECLARE <replaceable class="parameter">name</replaceable> [ BINARY ] [ INSENSITI
the standard SQL cursor conventions, including those involving
<command>DECLARE</command> and <command>OPEN</command> statements.
</para>
+
+ <para>
+ You can see all available cursors by querying the
+ <structname>pg_cursors</structname> system view.
+ </para>
</refsect1>
<refsect1>
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index c44e9ed72f1..a6d8155a0a4 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -3,7 +3,7 @@
*
* Copyright (c) 1996-2005, PostgreSQL Global Development Group
*
- * $PostgreSQL: pgsql/src/backend/catalog/system_views.sql,v 1.24 2006/01/16 18:15:30 neilc Exp $
+ * $PostgreSQL: pgsql/src/backend/catalog/system_views.sql,v 1.25 2006/01/18 06:49:26 neilc Exp $
*/
CREATE VIEW pg_roles AS
@@ -148,6 +148,13 @@ CREATE VIEW pg_locks AS
transactionid xid, classid oid, objid oid, objsubid int2,
transaction xid, pid int4, mode text, granted boolean);
+CREATE VIEW pg_cursors AS
+ SELECT C.name, C.statement, C.is_holdable, C.is_binary,
+ C.is_scrollable, C.creation_time
+ FROM pg_cursor() AS C
+ (name text, statement text, is_holdable boolean, is_binary boolean,
+ is_scrollable boolean, creation_time timestamptz);
+
CREATE VIEW pg_prepared_xacts AS
SELECT P.transaction, P.gid, P.prepared,
U.rolname AS owner, D.datname AS database
diff --git a/src/backend/commands/portalcmds.c b/src/backend/commands/portalcmds.c
index 8246b25774e..b2dab9d98de 100644
--- a/src/backend/commands/portalcmds.c
+++ b/src/backend/commands/portalcmds.c
@@ -14,7 +14,7 @@
*
*
* IDENTIFICATION
- * $PostgreSQL: pgsql/src/backend/commands/portalcmds.c,v 1.44 2005/11/03 17:11:35 alvherre Exp $
+ * $PostgreSQL: pgsql/src/backend/commands/portalcmds.c,v 1.45 2006/01/18 06:49:26 neilc Exp $
*
*-------------------------------------------------------------------------
*/
@@ -28,6 +28,7 @@
#include "optimizer/planner.h"
#include "rewrite/rewriteHandler.h"
#include "tcop/pquery.h"
+#include "tcop/tcopprot.h"
#include "utils/memutils.h"
@@ -105,8 +106,12 @@ PerformCursorOpen(DeclareCursorStmt *stmt, ParamListInfo params)
query = copyObject(query);
plan = copyObject(plan);
+ /*
+ * XXX: debug_query_string is wrong here: the user might have
+ * submitted more than one semicolon delimited queries.
+ */
PortalDefineQuery(portal,
- NULL, /* unfortunately don't have sourceText */
+ pstrdup(debug_query_string),
"SELECT", /* cursor's query is always a SELECT */
list_make1(query),
list_make1(plan),
diff --git a/src/backend/commands/prepare.c b/src/backend/commands/prepare.c
index f523984e5a8..f0afdbba367 100644
--- a/src/backend/commands/prepare.c
+++ b/src/backend/commands/prepare.c
@@ -10,7 +10,7 @@
* Copyright (c) 2002-2005, PostgreSQL Global Development Group
*
* IDENTIFICATION
- * $PostgreSQL: pgsql/src/backend/commands/prepare.c,v 1.46 2006/01/16 18:15:30 neilc Exp $
+ * $PostgreSQL: pgsql/src/backend/commands/prepare.c,v 1.47 2006/01/18 06:49:26 neilc Exp $
*
*-------------------------------------------------------------------------
*/
@@ -162,11 +162,11 @@ ExecuteQuery(ExecuteStmt *stmt, ParamListInfo params,
paramLI = EvaluateParams(estate, stmt->params, entry->argtype_list);
}
- /*
- * Create a new portal to run the query in
- */
+ /* Create a new portal to run the query in */
portal = CreateNewPortal();
-
+ /* Don't display the portal in pg_cursors, it is for internal use only */
+ portal->visible = false;
+
/*
* For CREATE TABLE / AS EXECUTE, make a copy of the stored query so that
* we can modify its destination (yech, but this has always been ugly).
diff --git a/src/backend/executor/spi.c b/src/backend/executor/spi.c
index 21a9a901d62..278860600b4 100644
--- a/src/backend/executor/spi.c
+++ b/src/backend/executor/spi.c
@@ -8,7 +8,7 @@
*
*
* IDENTIFICATION
- * $PostgreSQL: pgsql/src/backend/executor/spi.c,v 1.145 2005/11/22 18:17:10 momjian Exp $
+ * $PostgreSQL: pgsql/src/backend/executor/spi.c,v 1.146 2006/01/18 06:49:27 neilc Exp $
*
*-------------------------------------------------------------------------
*/
@@ -921,8 +921,8 @@ SPI_cursor_open(const char *name, void *plan,
* Set up the portal.
*/
PortalDefineQuery(portal,
- NULL, /* unfortunately don't have sourceText */
- "SELECT", /* nor the raw parse tree... */
+ spiplan->query,
+ "SELECT", /* don't have the raw parse tree... */
list_make1(queryTree),
list_make1(planTree),
PortalGetHeapMemory(portal));
diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c
index 0fe8ee057d5..ca08849afe9 100644
--- a/src/backend/tcop/postgres.c
+++ b/src/backend/tcop/postgres.c
@@ -8,7 +8,7 @@
*
*
* IDENTIFICATION
- * $PostgreSQL: pgsql/src/backend/tcop/postgres.c,v 1.478 2006/01/08 07:00:25 neilc Exp $
+ * $PostgreSQL: pgsql/src/backend/tcop/postgres.c,v 1.479 2006/01/18 06:49:27 neilc Exp $
*
* NOTES
* this is the "main" module of the postgres backend and
@@ -956,6 +956,8 @@ exec_simple_query(const char *query_string)
* already is one, silently drop it.
*/
portal = CreatePortal("", true, true);
+ /* Don't display the portal in pg_cursors */
+ portal->visible = false;
PortalDefineQuery(portal,
query_string,
diff --git a/src/backend/utils/mmgr/portalmem.c b/src/backend/utils/mmgr/portalmem.c
index 0402005a372..1bd9cc61d85 100644
--- a/src/backend/utils/mmgr/portalmem.c
+++ b/src/backend/utils/mmgr/portalmem.c
@@ -12,15 +12,19 @@
* Portions Copyright (c) 1994, Regents of the University of California
*
* IDENTIFICATION
- * $PostgreSQL: pgsql/src/backend/utils/mmgr/portalmem.c,v 1.83 2005/11/22 18:17:27 momjian Exp $
+ * $PostgreSQL: pgsql/src/backend/utils/mmgr/portalmem.c,v 1.84 2006/01/18 06:49:27 neilc Exp $
*
*-------------------------------------------------------------------------
*/
#include "postgres.h"
-#include "miscadmin.h"
+#include "access/heapam.h"
+#include "catalog/pg_type.h"
#include "commands/portalcmds.h"
#include "executor/executor.h"
+#include "funcapi.h"
+#include "miscadmin.h"
+#include "utils/builtins.h"
#include "utils/hsearch.h"
#include "utils/memutils.h"
#include "utils/portal.h"
@@ -56,8 +60,8 @@ do { \
\
MemSet(key, 0, MAX_PORTALNAME_LEN); \
StrNCpy(key, NAME, MAX_PORTALNAME_LEN); \
- hentry = (PortalHashEnt*)hash_search(PortalHashTable, \
- key, HASH_FIND, NULL); \
+ hentry = (PortalHashEnt *) hash_search(PortalHashTable, \
+ key, HASH_FIND, NULL); \
if (hentry) \
PORTAL = hentry->portal; \
else \
@@ -70,8 +74,8 @@ do { \
\
MemSet(key, 0, MAX_PORTALNAME_LEN); \
StrNCpy(key, NAME, MAX_PORTALNAME_LEN); \
- hentry = (PortalHashEnt*)hash_search(PortalHashTable, \
- key, HASH_ENTER, &found); \
+ hentry = (PortalHashEnt *) hash_search(PortalHashTable, \
+ key, HASH_ENTER, &found); \
if (found) \
elog(ERROR, "duplicate portal name"); \
hentry->portal = PORTAL; \
@@ -85,8 +89,8 @@ do { \
\
MemSet(key, 0, MAX_PORTALNAME_LEN); \
StrNCpy(key, PORTAL->name, MAX_PORTALNAME_LEN); \
- hentry = (PortalHashEnt*)hash_search(PortalHashTable, \
- key, HASH_REMOVE, NULL); \
+ hentry = (PortalHashEnt *) hash_search(PortalHashTable, \
+ key, HASH_REMOVE, NULL); \
if (hentry == NULL) \
elog(WARNING, "trying to delete portal name that does not exist"); \
} while(0)
@@ -190,12 +194,15 @@ CreatePortal(const char *name, bool allowDup, bool dupSilent)
"Portal");
/* initialize portal fields that don't start off zero */
+ portal->status = PORTAL_NEW;
portal->cleanup = PortalCleanup;
portal->createSubid = GetCurrentSubTransactionId();
portal->strategy = PORTAL_MULTI_QUERY;
portal->cursorOptions = CURSOR_OPT_NO_SCROLL;
portal->atStart = true;
portal->atEnd = true; /* disallow fetches until query is set */
+ portal->visible = true;
+ portal->creation_time = GetCurrentTimestamp();
/* put portal in table (sets portal->name) */
PortalHashTableInsert(portal, name);
@@ -756,3 +763,103 @@ AtSubCleanup_Portals(SubTransactionId mySubid)
PortalDrop(portal, false);
}
}
+
+/* Find all available cursors */
+Datum
+pg_cursor(PG_FUNCTION_ARGS)
+{
+ FuncCallContext *funcctx;
+ HASH_SEQ_STATUS *hash_seq;
+ PortalHashEnt *hentry;
+
+ /* stuff done only on the first call of the function */
+ if (SRF_IS_FIRSTCALL())
+ {
+ MemoryContext oldcontext;
+ TupleDesc tupdesc;
+
+ /* 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);
+
+ if (PortalHashTable)
+ {
+ hash_seq = (HASH_SEQ_STATUS *) palloc(sizeof(HASH_SEQ_STATUS));
+ hash_seq_init(hash_seq, PortalHashTable);
+ funcctx->user_fctx = (void *) hash_seq;
+ }
+ else
+ funcctx->user_fctx = NULL;
+
+ /*
+ * build tupdesc for result tuples. This must match the
+ * definition of the pg_cursors view in system_views.sql
+ */
+ tupdesc = CreateTemplateTupleDesc(6, false);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 1, "name",
+ TEXTOID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 2, "statement",
+ TEXTOID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 3, "is_holdable",
+ BOOLOID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 4, "is_binary",
+ BOOLOID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 5, "is_scrollable",
+ BOOLOID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 6, "creation_time",
+ TIMESTAMPTZOID, -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);
+
+ /* loop until we find a visible portal or hit the end of the list */
+ while ((hentry = hash_seq_search(hash_seq)) != NULL)
+ {
+ if (hentry->portal->visible)
+ break;
+ }
+
+ if (hentry)
+ {
+ Portal portal;
+ Datum result;
+ HeapTuple tuple;
+ Datum values[6];
+ bool nulls[6];
+
+ portal = hentry->portal;
+ MemSet(nulls, 0, sizeof(nulls));
+
+ values[0] = DirectFunctionCall1(textin, CStringGetDatum(portal->name));
+ if (!portal->sourceText)
+ nulls[1] = true;
+ else
+ values[1] = DirectFunctionCall1(textin,
+ CStringGetDatum(portal->sourceText));
+ values[2] = BoolGetDatum(portal->cursorOptions & CURSOR_OPT_HOLD);
+ values[3] = BoolGetDatum(portal->cursorOptions & CURSOR_OPT_BINARY);
+ values[4] = BoolGetDatum(portal->cursorOptions & CURSOR_OPT_SCROLL);
+ values[5] = TimestampTzGetDatum(portal->creation_time);
+
+ tuple = heap_form_tuple(funcctx->tuple_desc, values, nulls);
+ result = HeapTupleGetDatum(tuple);
+ SRF_RETURN_NEXT(funcctx, result);
+ }
+
+ SRF_RETURN_DONE(funcctx);
+}
+
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;
/*
diff --git a/src/test/regress/expected/portals.out b/src/test/regress/expected/portals.out
index 3f0e0cdd265..cbff0b4245a 100644
--- a/src/test/regress/expected/portals.out
+++ b/src/test/regress/expected/portals.out
@@ -676,7 +676,30 @@ CLOSE foo10;
CLOSE foo11;
CLOSE foo12;
-- leave some cursors open, to test that auto-close works.
+-- record this in the system view as well (don't query the time field there
+-- however)
+SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors;
+ name | statement | is_holdable | is_binary | is_scrollable
+-------+------------------------------------------------------+-------------+-----------+---------------
+ foo13 | DECLARE foo13 SCROLL CURSOR FOR SELECT * FROM tenk1; | f | f | t
+ foo15 | DECLARE foo15 SCROLL CURSOR FOR SELECT * FROM tenk1; | f | f | t
+ foo19 | DECLARE foo19 SCROLL CURSOR FOR SELECT * FROM tenk1; | f | f | t
+ foo17 | DECLARE foo17 SCROLL CURSOR FOR SELECT * FROM tenk1; | f | f | t
+ foo14 | DECLARE foo14 SCROLL CURSOR FOR SELECT * FROM tenk2; | f | f | t
+ foo21 | DECLARE foo21 SCROLL CURSOR FOR SELECT * FROM tenk1; | f | f | t
+ foo23 | DECLARE foo23 SCROLL CURSOR FOR SELECT * FROM tenk1; | f | f | t
+ foo18 | DECLARE foo18 SCROLL CURSOR FOR SELECT * FROM tenk2; | f | f | t
+ foo20 | DECLARE foo20 SCROLL CURSOR FOR SELECT * FROM tenk2; | f | f | t
+ foo22 | DECLARE foo22 SCROLL CURSOR FOR SELECT * FROM tenk2; | f | f | t
+ foo16 | DECLARE foo16 SCROLL CURSOR FOR SELECT * FROM tenk2; | f | f | t
+(11 rows)
+
END;
+SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors;
+ name | statement | is_holdable | is_binary | is_scrollable
+------+-----------+-------------+-----------+---------------
+(0 rows)
+
--
-- NO SCROLL disallows backward fetching
--
@@ -695,6 +718,11 @@ END;
--
-- Cursors outside transaction blocks
--
+SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors;
+ name | statement | is_holdable | is_binary | is_scrollable
+------+-----------+-------------+-----------+---------------
+(0 rows)
+
BEGIN;
DECLARE foo25 SCROLL CURSOR WITH HOLD FOR SELECT * FROM tenk2;
FETCH FROM foo25;
@@ -728,6 +756,12 @@ FETCH ABSOLUTE -1 FROM foo25;
2968 | 9999 | 0 | 0 | 8 | 8 | 68 | 968 | 968 | 2968 | 2968 | 136 | 137 | EKAAAA | PUOAAA | VVVVxx
(1 row)
+SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors;
+ name | statement | is_holdable | is_binary | is_scrollable
+-------+----------------------------------------------------------------+-------------+-----------+---------------
+ foo25 | DECLARE foo25 SCROLL CURSOR WITH HOLD FOR SELECT * FROM tenk2; | t | f | t
+(1 row)
+
CLOSE foo25;
--
-- ROLLBACK should close holdable cursors
@@ -808,3 +842,30 @@ fetch all from c2;
drop function count_tt1_v();
drop function count_tt1_s();
+-- Create a cursor with the BINARY option and check the pg_cursors view
+BEGIN;
+SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors;
+ name | statement | is_holdable | is_binary | is_scrollable
+------+----------------------------------------------------------------------+-------------+-----------+---------------
+ c2 | declare c2 cursor with hold for select count_tt1_v(), count_tt1_s(); | t | f | f
+(1 row)
+
+DECLARE bc BINARY CURSOR FOR SELECT * FROM tenk1;
+SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors;
+ name | statement | is_holdable | is_binary | is_scrollable
+------+----------------------------------------------------------------------+-------------+-----------+---------------
+ c2 | declare c2 cursor with hold for select count_tt1_v(), count_tt1_s(); | t | f | f
+ bc | DECLARE bc BINARY CURSOR FOR SELECT * FROM tenk1; | f | t | t
+(2 rows)
+
+ROLLBACK;
+-- We should not see the portal that is created internally to
+-- implement EXECUTE in pg_cursors
+PREPARE cprep AS
+ SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors;
+EXECUTE cprep;
+ name | statement | is_holdable | is_binary | is_scrollable
+------+----------------------------------------------------------------------+-------------+-----------+---------------
+ c2 | declare c2 cursor with hold for select count_tt1_v(), count_tt1_s(); | t | f | f
+(1 row)
+
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 63c06ec75ac..90e38d013f3 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1277,6 +1277,7 @@ SELECT viewname, definition FROM pg_views WHERE schemaname <> 'information_schem
viewname | definition
--------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
iexit | SELECT ih.name, ih.thepath, interpt_pp(ih.thepath, r.thepath) AS exit FROM ihighway ih, ramp r WHERE (ih.thepath ## r.thepath);
+ pg_cursors | SELECT c.name, c."statement", c.is_holdable, c.is_binary, c.is_scrollable, c.creation_time FROM pg_cursor() c(name text, "statement" text, is_holdable boolean, is_binary boolean, is_scrollable boolean, creation_time timestamp with time zone);
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);
@@ -1321,7 +1322,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;
-(45 rows)
+(46 rows)
SELECT tablename, rulename, definition FROM pg_rules
ORDER BY tablename, rulename;
diff --git a/src/test/regress/sql/portals.sql b/src/test/regress/sql/portals.sql
index da4e3b0e3ae..c0307874e80 100644
--- a/src/test/regress/sql/portals.sql
+++ b/src/test/regress/sql/portals.sql
@@ -168,8 +168,14 @@ CLOSE foo12;
-- leave some cursors open, to test that auto-close works.
+-- record this in the system view as well (don't query the time field there
+-- however)
+SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors;
+
END;
+SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors;
+
--
-- NO SCROLL disallows backward fetching
--
@@ -188,6 +194,9 @@ END;
-- Cursors outside transaction blocks
--
+
+SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors;
+
BEGIN;
DECLARE foo25 SCROLL CURSOR WITH HOLD FOR SELECT * FROM tenk2;
@@ -204,6 +213,8 @@ FETCH BACKWARD FROM foo25;
FETCH ABSOLUTE -1 FROM foo25;
+SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors;
+
CLOSE foo25;
--
@@ -278,3 +289,17 @@ fetch all from c2;
drop function count_tt1_v();
drop function count_tt1_s();
+
+
+-- Create a cursor with the BINARY option and check the pg_cursors view
+BEGIN;
+SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors;
+DECLARE bc BINARY CURSOR FOR SELECT * FROM tenk1;
+SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors;
+ROLLBACK;
+
+-- We should not see the portal that is created internally to
+-- implement EXECUTE in pg_cursors
+PREPARE cprep AS
+ SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors;
+EXECUTE cprep;
generated by cgit v1.2.3 (git 2.25.1) at 2025-05-23 12:28:18 +0000