Attached is a patch to create a new system view pg_cursors to list all
available cursors to the current session and transaction.

The patch itself is quite analogous to the patch for pg_prepared_statements
I submitted in December.

The attributes is_holdable, is_binary and is_scrollable are exposed in the
view.

There's a TODO item that only talks about WITH HOLD cursors, however the
proposed system view lists WITHOUT HOLD cursors as well.

   o %Allow pooled connections to list all open WITH HOLD cursors

     Because WITH HOLD cursors exist outside transactions, this allows
     them to be listed so they can be closed.


I noticed that there is no regression test for binary cursors. Should there
be one? I now create one to check the view but don't actually query it.


Joachim
diff -cr cvs/pgsql/doc/src/sgml/catalogs.sgml 
cvs.build/pgsql/doc/src/sgml/catalogs.sgml
*** cvs/pgsql/doc/src/sgml/catalogs.sgml        2006-01-08 08:00:24.000000000 
+0100
--- cvs.build/pgsql/doc/src/sgml/catalogs.sgml  2006-01-12 09:44:04.000000000 
+0100
***************
*** 4358,4363 ****
--- 4358,4368 ----
  
      <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>
***************
*** 4427,4432 ****
--- 4432,4519 ----
    </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 view <structname>pg_cursors</structname> lists all declared cursors for
+    the current session and transaction. Note that cursors that have been
+    declared <literal>WITHOUT HOLD</literal> are only valid within the current
+    transaction whereas <literal>WITH HOLD</literal> cursors can exist during
+    the whole session. Cursors can be created using <xref linkend="sql-declare"
+    endterm="sql-declare-title"> and removed with <xref linkend="sql-close"
+    endterm="sql-close-title">.
+   </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 was declared <literal>WITH 
HOLD</literal>; <literal>false</literal> if the cursor was declared 
<literal>WITHOUT HOLD</literal>.</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> if not.</entry>
+      </row>
+ 
+      <row>
+       <entry><structfield>is_scrollable</structfield></entry>
+       <entry><type>boolean</type></entry>
+       <entry></entry>
+       <entry><literal>true</literal> if the cursor was declared 
<literal>SCROLL</literal>; <literal>false</literal> if the cursor was declared 
<literal>NO SCROLL</literal>. If neither the <literal>SCROLL</literal> nor the 
<literal>NO SCROLL</literal> keyword has been given, 
<productname>PostgreSQL</productname> will choose one of both, its decision can 
be seen in this view.</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 -cr cvs/pgsql/doc/src/sgml/ref/close.sgml 
cvs.build/pgsql/doc/src/sgml/ref/close.sgml
*** cvs/pgsql/doc/src/sgml/ref/close.sgml       2005-01-04 01:39:53.000000000 
+0100
--- cvs.build/pgsql/doc/src/sgml/ref/close.sgml 2006-01-12 08:20:49.000000000 
+0100
***************
*** 76,81 ****
--- 76,86 ----
     <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 -cr cvs/pgsql/doc/src/sgml/ref/declare.sgml 
cvs.build/pgsql/doc/src/sgml/ref/declare.sgml
*** cvs/pgsql/doc/src/sgml/ref/declare.sgml     2005-01-04 01:39:53.000000000 
+0100
--- cvs.build/pgsql/doc/src/sgml/ref/declare.sgml       2006-01-12 
08:20:49.000000000 +0100
***************
*** 253,258 ****
--- 253,263 ----
      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 -cr cvs/pgsql/src/backend/catalog/system_views.sql 
cvs.build/pgsql/src/backend/catalog/system_views.sql
*** cvs/pgsql/src/backend/catalog/system_views.sql      2006-01-08 
08:00:25.000000000 +0100
--- cvs.build/pgsql/src/backend/catalog/system_views.sql        2006-01-12 
09:17:48.000000000 +0100
***************
*** 148,153 ****
--- 148,160 ----
       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 -cr cvs/pgsql/src/backend/commands/portalcmds.c 
cvs.build/pgsql/src/backend/commands/portalcmds.c
*** cvs/pgsql/src/backend/commands/portalcmds.c 2005-11-03 18:11:35.000000000 
+0100
--- cvs.build/pgsql/src/backend/commands/portalcmds.c   2006-01-12 
09:36:39.000000000 +0100
***************
*** 28,33 ****
--- 28,34 ----
  #include "optimizer/planner.h"
  #include "rewrite/rewriteHandler.h"
  #include "tcop/pquery.h"
+ #include "tcop/tcopprot.h"
  #include "utils/memutils.h"
  
  
***************
*** 106,112 ****
        plan = copyObject(plan);
  
        PortalDefineQuery(portal,
!                                         NULL,         /* unfortunately don't 
have sourceText */
                                          "SELECT", /* cursor's query is always 
a SELECT */
                                          list_make1(query),
                                          list_make1(plan),
--- 107,113 ----
        plan = copyObject(plan);
  
        PortalDefineQuery(portal,
!                                         pstrdup(debug_query_string),
                                          "SELECT", /* cursor's query is always 
a SELECT */
                                          list_make1(query),
                                          list_make1(plan),
diff -cr cvs/pgsql/src/backend/utils/mmgr/portalmem.c 
cvs.build/pgsql/src/backend/utils/mmgr/portalmem.c
*** cvs/pgsql/src/backend/utils/mmgr/portalmem.c        2005-11-22 
19:17:27.000000000 +0100
--- cvs.build/pgsql/src/backend/utils/mmgr/portalmem.c  2006-01-12 
09:38:37.000000000 +0100
***************
*** 18,26 ****
   */
  #include "postgres.h"
  
! #include "miscadmin.h"
  #include "commands/portalcmds.h"
  #include "executor/executor.h"
  #include "utils/hsearch.h"
  #include "utils/memutils.h"
  #include "utils/portal.h"
--- 18,30 ----
   */
  #include "postgres.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"
***************
*** 190,195 ****
--- 194,200 ----
                                                                                
   "Portal");
  
        /* initialize portal fields that don't start off zero */
+       /* portal->status will be set to PORTAL_NEW */
        portal->cleanup = PortalCleanup;
        portal->createSubid = GetCurrentSubTransactionId();
        portal->strategy = PORTAL_MULTI_QUERY;
***************
*** 197,202 ****
--- 202,215 ----
        portal->atStart = true;
        portal->atEnd = true;           /* disallow fetches until query is set 
*/
  
+       /* record the creation time only for named portals such that we can 
display
+        * it in the pg_cursors system view (this will also be set for portals 
that
+        * are called "<unnamed portal n>" but it's less expensive to just call
+        * GetCurrentTimestamp() for those as well than to check more 
thoroughly)
+        * */
+       if (name[0])
+               portal->creation_time = GetCurrentTimestamp();
+ 
        /* put portal in table (sets portal->name) */
        PortalHashTableInsert(portal, name);
  
***************
*** 756,758 ****
--- 769,884 ----
                PortalDrop(portal, false);
        }
  }
+ 
+ /* Find all available cursors */
+ Datum
+ pg_cursor(PG_FUNCTION_ARGS)
+ {
+       FuncCallContext    *funcctx;
+       HASH_SEQ_STATUS    *hash_seq;
+       PortalHashEnt      *hentry;
+       Portal                          portal;
+ 
+       /* 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 named portal or hit the end of the list */
+       while ((hentry = hash_seq_search(hash_seq)) != NULL)
+       {
+               portal = hentry->portal;
+               /* there can be a named portal created by CreateNewPortal, its 
name
+                * will be "<unnamed portal n>" (see CreateNewPortal function 
in this
+                * file). Those have a status of PORTAL_NEW. The status of 
cursors is
+                * PORTAL_READY however. */
+               if (portal->status != PORTAL_READY)
+                       continue;
+               if (portal->name[0] != '\0')
+                       break;
+       }
+ 
+       /* it's sufficient to check for hentry here because either we are not
+        * at the end of the list and hentry is != NULL, but then we loop above
+        * until we find a named portal. Then we have the named portal here.
+        * Or, we are at the end of the list but then hentry is NULL */
+       if (hentry)
+       {
+               Datum           result;
+               HeapTuple       tuple;
+               Datum           values[6];
+               bool            nulls[6];
+ 
+               MemSet(nulls, 0, sizeof(nulls));
+ 
+               values[0] = DirectFunctionCall1(textin, 
CStringGetDatum(portal->name));
+               /* XXX can this happen? */
+               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);
+               /* that should be sufficient, since if the cursor is not 
scrollable,
+                * the CURSOR_OPT_SCROLL bit won't be set */
+               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 -cr cvs/pgsql/src/include/catalog/pg_proc.h 
cvs.build/pgsql/src/include/catalog/pg_proc.h
*** cvs/pgsql/src/include/catalog/pg_proc.h     2006-01-12 08:09:19.000000000 
+0100
--- cvs.build/pgsql/src/include/catalog/pg_proc.h       2006-01-12 
08:20:49.000000000 +0100
***************
*** 3621,3626 ****
--- 3621,3628 ----
  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 -cr cvs/pgsql/src/include/utils/builtins.h 
cvs.build/pgsql/src/include/utils/builtins.h
*** cvs/pgsql/src/include/utils/builtins.h      2006-01-12 08:09:25.000000000 
+0100
--- cvs.build/pgsql/src/include/utils/builtins.h        2006-01-12 
08:20:49.000000000 +0100
***************
*** 865,868 ****
--- 865,871 ----
  /* 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 -cr cvs/pgsql/src/include/utils/portal.h 
cvs.build/pgsql/src/include/utils/portal.h
*** cvs/pgsql/src/include/utils/portal.h        2005-10-15 04:49:46.000000000 
+0200
--- cvs.build/pgsql/src/include/utils/portal.h  2006-01-12 08:20:49.000000000 
+0100
***************
*** 107,112 ****
--- 107,113 ----
        ResourceOwner resowner;         /* resources owned by portal */
        void            (*cleanup) (Portal portal);             /* cleanup hook 
*/
        SubTransactionId createSubid;           /* the ID of the creating 
subxact */
+       TimestampTz creation_time;
  
        /*
         * if createSubid is InvalidSubTransactionId, the portal is held over 
from
diff -cr cvs/pgsql/src/test/regress/expected/portals.out 
cvs.build/pgsql/src/test/regress/expected/portals.out
*** cvs/pgsql/src/test/regress/expected/portals.out     2005-04-11 
21:51:16.000000000 +0200
--- cvs.build/pgsql/src/test/regress/expected/portals.out       2006-01-12 
10:20:37.000000000 +0100
***************
*** 676,682 ****
--- 676,705 ----
  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,700 ****
--- 718,728 ----
  --
  -- 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,733 ****
--- 756,767 ----
      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,810 ****
--- 842,875 ----
  
  drop function count_tt1_v();
  drop function count_tt1_s();
+ --
+ -- Create a cursor with the BINARY option and check the pg_cursor view along
+ --
+ 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)
+ 
+ CLOSE bc;
+ --
+ -- We should not see <unnamed portal %d> named portals
+ --
+ 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)
+ 
+ DEALLOCATE cprep;
+ ROLLBACK;
diff -cr cvs/pgsql/src/test/regress/expected/rules.out 
cvs.build/pgsql/src/test/regress/expected/rules.out
*** cvs/pgsql/src/test/regress/expected/rules.out       2006-01-08 
08:00:26.000000000 +0100
--- cvs.build/pgsql/src/test/regress/expected/rules.out 2006-01-12 
10:20:37.000000000 +0100
***************
*** 1277,1282 ****
--- 1277,1283 ----
           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,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;
--- 1322,1328 ----
   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;
! (46 rows)
  
  SELECT tablename, rulename, definition FROM pg_rules 
        ORDER BY tablename, rulename;
diff -cr cvs/pgsql/src/test/regress/sql/portals.sql 
cvs.build/pgsql/src/test/regress/sql/portals.sql
*** cvs/pgsql/src/test/regress/sql/portals.sql  2005-04-11 21:51:16.000000000 
+0200
--- cvs.build/pgsql/src/test/regress/sql/portals.sql    2006-01-12 
10:17:08.000000000 +0100
***************
*** 168,175 ****
--- 168,181 ----
  
  -- 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,193 ****
--- 194,202 ----
  -- 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,209 ****
--- 213,220 ----
  
  FETCH ABSOLUTE -1 FROM foo25;
  
+ SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors;
+ 
  CLOSE foo25;
  
  --
***************
*** 278,280 ****
--- 289,316 ----
  
  drop function count_tt1_v();
  drop function count_tt1_s();
+ 
+ 
+ --
+ -- Create a cursor with the BINARY option and check the pg_cursor view along
+ --
+ 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;
+ 
+ CLOSE bc;
+ 
+ --
+ -- We should not see <unnamed portal %d> named portals
+ --
+ 
+ PREPARE cprep AS SELECT name, statement, is_holdable, is_binary, 
is_scrollable FROM pg_cursors;
+ 
+ EXECUTE cprep;
+ 
+ DEALLOCATE cprep;
+ 
+ ROLLBACK;
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Reply via email to