SPI function to investigate query semantics - Mailing list pgsql-patches

Here's a patch containing the function SPI_iterate_query_roots(...). I'm
optimistic so it's complete with documentation :-)

I think that this function is needed so that PL/<lang> authors like
myself have a way to investigate the semantics of a prepared query. For
me this is essential since I want to prevent that savepoint related
statements are executed using normal SQL so that I can enforce the use
of the methods stipulated by the connection interface.

I forsee that this might be of interest for other PL/<lang> authors as
well. With this patch in place, it will be possible to do things like
this (returning false is rejecting in this case since false terminates
the iteration):

static bool rejectTransactionCommand(Query* query, void* clientData)
{
    return !(query->commandType == CMD_UTILITY &&
            IsA(query->utilityStmt, TransactionStmt));
}

and then use that like:

    result = !SPI_iterate_query_roots(ePlan, rejectTransactionCommand,
NULL);

The patch has no side effects since it's a pure addon.

Kind regards,
Thomas Hallgren

Index: doc/src/sgml/spi.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/spi.sgml,v
retrieving revision 1.35
diff -u -r1.35 spi.sgml
--- doc/src/sgml/spi.sgml    13 Sep 2004 20:05:25 -0000    1.35
+++ doc/src/sgml/spi.sgml    1 Dec 2004 19:05:28 -0000
@@ -1305,6 +1305,82 @@

 <!-- *********************************************** -->

+<refentry id="spi-spi-iterate-query-roots">
+ <refmeta>
+  <refentrytitle>SPI_iterate_query_roots</refentrytitle>
+ </refmeta>
+
+ <refnamediv>
+  <refname>SPI_iterate_query_roots</refname>
+  <refpurpose>investigate the semantics of a query</refpurpose>
+ </refnamediv>
+
+ <indexterm><primary>SPI_iterate_query_roots</primary></indexterm>
+
+ <refsynopsisdiv>
+<synopsis>
+bool SPI_iterate_query_roots(void * <parameter>plan</parameter>, QueryVisitor <parameter>callback</parameter>, void *
<parameter>clientData</parameter>)
+</synopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+  <title>Description</title>
+
+  <para>
+    The <function>SPI_iterate_query_roots</function> will invoke the
+    <symbol>queryVisitor</symbol> callback once for each top level
+    <symbol>Query</symbol> found in the supplied execution plan.
+    The iteration is cancelled when a callback returns <symbol>false</symbol>.
+    If no callback returns <symbol>false</symbol>, or if the plan is
+    <symbol>NULL</symbol>, the function returns <symbol>true</symbol>.
+  </para>
+ </refsect1>
+
+ <refsect1>
+  <title>Arguments</title>
+
+  <variablelist>
+   <varlistentry>
+    <term><literal>void * <parameter>plan</parameter></literal></term>
+    <listitem>
+     <para>
+      execution plan (returned by <function>SPI_prepare</function>)
+     </para>
+    </listitem>
+   </varlistentry>
+
+   <varlistentry>
+    <term><literal>QueryVisitor <parameter>callback</parameter></literal></term>
+    <listitem>
+     <para>
+      the callback to invoke for each query found
+     </para>
+    </listitem>
+   </varlistentry>
+
+   <varlistentry>
+    <term><literal>void * <parameter>clientData</parameter></literal></term>
+    <listitem>
+     <para>
+      user defined data that will be passed on to the callback
+     </para>
+    </listitem>
+   </varlistentry>
+  </variablelist>
+ </refsect1>
+
+ <refsect1>
+  <title>Return Value</title>
+  <para>
+    <symbol>true</symbol> when all callbacks returned <symbol>true</symbol> or
+    <symbol>false</symbol> when a callback returned <symbol>false</symbol> and
+    thus terminated the iteration.
+  </para>
+ </refsect1>
+</refentry>
+
+<!-- *********************************************** -->
+
 <refentry id="spi-spi-cursor-find">
  <refmeta>
   <refentrytitle>SPI_cursor_find</refentrytitle>
Index: src/backend/executor/spi.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/executor/spi.c,v
retrieving revision 1.132
diff -u -r1.132 spi.c
--- src/backend/executor/spi.c    16 Nov 2004 18:10:13 -0000    1.132
+++ src/backend/executor/spi.c    1 Dec 2004 19:05:29 -0000
@@ -1064,6 +1064,42 @@
     return false;
 }

+/**
+ * Invokes the queryVisitor callback for each top level Query found in an
+ * execution plan. The iteration is cancelled when a callback returns
+ * false. If no callbacks returns false, or if the plan is NULL, this
+ * function returns true.
+ *
+ * Arguments:
+ *     plan          An ExecutionPlan created by SPI_prepare
+ *     queryVisitor  The callback function
+ *     clientData    User defined data that will be passed on to the callback
+ * Returns: true if the plan is NULL or if all callback invocation returns true
+ */
+bool
+SPI_iterate_query_roots(void *plan, QueryVisitor queryVisitor, void *clientData)
+{
+    _SPI_plan *spiplan = (_SPI_plan *) plan;
+
+    if (spiplan != NULL)
+    {
+        ListCell *query_list_list_item;
+        List     *query_list_list = spiplan->qtlist;
+        foreach(query_list_list_item, query_list_list)
+        {
+            List     *query_list = lfirst(query_list_list_item);
+            ListCell *query_list_item;
+
+            foreach(query_list_item, query_list)
+            {
+                if(!queryVisitor((Query *)lfirst(query_list_item), clientData))
+                    return false;
+            }
+        }
+    }
+    return true;
+}
+
 /*
  * SPI_result_code_string --- convert any SPI return code to a string
  *
Index: src/include/executor/spi.h
===================================================================
RCS file: /projects/cvsroot/pgsql/src/include/executor/spi.h,v
retrieving revision 1.50
diff -u -r1.50 spi.h
--- src/include/executor/spi.h    16 Nov 2004 18:10:13 -0000    1.50
+++ src/include/executor/spi.h    1 Dec 2004 19:05:30 -0000
@@ -119,6 +119,9 @@
 extern void SPI_freetuple(HeapTuple pointer);
 extern void SPI_freetuptable(SPITupleTable *tuptable);

+typedef bool (*QueryVisitor)(Query* query, void *clientData);
+extern bool SPI_iterate_query_roots(void *plan, QueryVisitor queryVisitor, void* clientData);
+
 extern Portal SPI_cursor_open(const char *name, void *plan,
                 Datum *Values, const char *Nulls, bool read_only);
 extern Portal SPI_cursor_find(const char *name);