Some new SPI functions - Mailing list pgsql-patches

I need three new functions in the Server Programming Interface (SPI) when
mapping an ExecutionPlan to a Java prepared statement (Pl/Java project).


The execute method of the prepared statement needs to know if the result is
a ResultSet (SPI_cursor_open) or just a number indicated how many rows that
where affected (SPI_execp). Currently there's no way I can tell by just
looking at the plan unless I violate the data hiding and use spi_priv.h. I
really don't want to do that. Hence the need for SPI_is_cursor_plan

I send an array of java objects for the arguments. The
SPI_cursor_open/SPI_execp of course expects the arguments to be Datum's and
the mapper must convert java objects. The mapping code is based on Oid's so
I need a way to extract the number of expected arguments and the typeid of
each arguments.

I find it likely that other pl<lang> implementations where similar support
is planned might find these functions useful.


Thomas Hallgren

Index: src/backend/executor/spi.c
===================================================================
retrieving revision 1.109
diff -u -r1.109 spi.c
--- src/backend/executor/spi.c 2 Dec 2003 19:26:47 -0000 1.109
+++ src/backend/executor/spi.c 12 Feb 2004 11:13:11 -0000
@@ -918,6 +918,65 @@
  PortalDrop(portal, false);
 }

+/*
+ * Returns the Oid representing the type id for argument at argIndex. First
+ * parameter is at index zero.
+ */
+Oid
+SPI_getargtypeid(void *plan, int argIndex)
+{
+ if (plan == NULL || argIndex < 0 || argIndex >= ((_SPI_plan*)plan)->nargs)
+ {
+  SPI_result = SPI_ERROR_ARGUMENT;
+  return InvalidOid;
+ }
+ return ((_SPI_plan *) plan)->argtypes[argIndex];
+}
+
+/*
+ * Returns the number of arguments for the prepared plan.
+ */
+int
+SPI_getargcount(void *plan)
+{
+ if (plan == NULL)
+ {
+  SPI_result = SPI_ERROR_ARGUMENT;
+  return -1;
+ }
+ return ((_SPI_plan *) plan)->nargs;
+}
+
+/*
+ * Returns true if the plan contains exactly one command
+ * and that command originates from normal SELECT (i.e.
+ * *not* a SELECT ... INTO). In essence, the result indicates
+ * if the command can be used with SPI_cursor_open
+ *
+ * Parameters
+ *    plan A plan previously prepared using SPI_prepare
+ */
+bool
+SPI_is_cursor_plan(void *plan)
+{
+ List *qtlist;
+ _SPI_plan *spiplan = (_SPI_plan *) plan;
+ if (spiplan == NULL)
+ {
+  SPI_result = SPI_ERROR_ARGUMENT;
+  return false;
+ }
+
+ qtlist = spiplan->qtlist;
+ if(length(spiplan->ptlist) == 1 && length(qtlist) == 1)
+ {
+  Query *queryTree = (Query *) lfirst((List *) lfirst(qtlist));
+  if(queryTree->commandType == CMD_SELECT && queryTree->into == NULL)
+   return true;
+ }
+ return false;
+}
+
 /* =================== private functions =================== */

 /*
Index: src/include/executor/spi.h
===================================================================
retrieving revision 1.41
diff -u -r1.41 spi.h
--- src/include/executor/spi.h 2 Dec 2003 19:26:47 -0000 1.41
+++ src/include/executor/spi.h 12 Feb 2004 11:13:21 -0000
@@ -90,6 +90,10 @@
 extern void *SPI_saveplan(void *plan);
 extern int SPI_freeplan(void *plan);

+extern Oid SPI_getargtypeid(void *plan, int argIndex);
+extern int SPI_getargcount(void *plan);
+extern bool SPI_is_cursor_plan(void *plan);
+
 extern HeapTuple SPI_copytuple(HeapTuple tuple);
 extern TupleDesc SPI_copytupledesc(TupleDesc tupdesc);
 extern TupleTableSlot *SPI_copytupleintoslot(HeapTuple tuple,