Thread: Some new SPI functions

Some new SPI functions

From

"Thomas Hallgren"

Date:

12 February 2004, 11:16:35

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,

Re: Some new SPI functions

From

Tom Lane

Date:

12 February 2004, 15:17:08

"Thomas Hallgren" <thhal@mailblocks.com> writes:
> I need three new functions in the Server Programming Interface (SPI) when
> mapping an ExecutionPlan to a Java prepared statement (Pl/Java project).

These functions look reasonable, but where is the documentation patch?

            regards, tom lane

Re: Some new SPI functions

From

Bruce Momjian

Date:

12 February 2004, 17:05:42

Tom Lane wrote:
> "Thomas Hallgren" <thhal@mailblocks.com> writes:
> > I need three new functions in the Server Programming Interface (SPI) when
> > mapping an ExecutionPlan to a Java prepared statement (Pl/Java project).
>
> These functions look reasonable, but where is the documentation patch?

I assume it will come when the final patch is submitted.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: Some new SPI functions

From

Bruce Momjian

Date:

13 February 2004, 04:12:14

Thomas, if this is ready for application, would you make some SGML
changes to match, or give me text to add for them.  Thanks.

---------------------------------------------------------------------------

Thomas Hallgren wrote:
> 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,
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: Some new SPI functions

From

"Thomas Hallgren"

Date:

14 February 2004, 23:11:52

Sure, I'll provide some docs. Just wasn't aware that "patchers" did that.

- thomas

----- Original Message -----
From: "Bruce Momjian" <pgman@candle.pha.pa.us>
To: "Thomas Hallgren" <thhal@mailblocks.com>
Cc: <pgsql-patches@postgresql.org>
Sent: Friday, February 13, 2004 06:12
Subject: Re: [PATCHES] Some new SPI functions


>
> Thomas, if this is ready for application, would you make some SGML
> changes to match, or give me text to add for them.  Thanks.
>
> --------------------------------------------------------------------------
-
>
> Thomas Hallgren wrote:
> > 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,
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 8: explain analyze is your friend
> >
>
> --
>   Bruce Momjian                        |  http://candle.pha.pa.us
>   pgman@candle.pha.pa.us               |  (610) 359-1001
>   +  If your life is a hard drive,     |  13 Roberts Road
>   +  Christ can be your backup.        |  Newtown Square, Pennsylvania
19073
>

Re: Some new SPI functions

From

Christopher Kings-Lynne

Date:

15 February 2004, 03:47:49


Thomas Hallgren wrote:

> Sure, I'll provide some docs. Just wasn't aware that "patchers" did that.
>
> - thomas

Yeah, in PostgreSQL, whoever writes the patch also must submit docs.
It's the best way of keeping docs up to date :)

Chris

Re: Some new SPI functions

From

Tom Lane

Date:

15 February 2004, 04:40:19

"Thomas Hallgren" <thhal@mailblocks.com> writes:
> Sure, I'll provide some docs. Just wasn't aware that "patchers" did that.

Who did you think would do it?

            regards, tom lane

Re: Some new SPI functions

From

"Thomas Hallgren"

Date:

15 February 2004, 13:21:31

> Who did you think would do it?
>
> regards, tom lane
>
you :-)

Seriously, I didn't give it much though. This is undoubtedly the best way
although some projects handle it differently. A  tech-writer perhaps, with
better writing skills then programmers in general. But people like that are
probabaly not too common in the open source field. If you don't do like
JBoss and charge for the docs (I'm *not* in favor of that).

Anyway, here's the patch again. This time with documentation.

Index: doc/src/sgml/spi.sgml
===================================================================
retrieving revision 1.30
diff -u -r1.30 spi.sgml
--- doc/src/sgml/spi.sgml 1 Dec 2003 22:07:57 -0000 1.30
+++ doc/src/sgml/spi.sgml 15 Feb 2004 13:47:13 -0000
@@ -573,6 +573,190 @@

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

+<refentry id="spi-spi-getargcount">
+ <refmeta>
+  <refentrytitle>SPI_getargcount</refentrytitle>
+ </refmeta>
+
+ <refnamediv>
+  <refname>SPI_getargcount</refname>
+  <refpurpose>returns the number of arguments needed when executing a plan
+  prepared by <function>SPI_prepare</function></refpurpose>
+ </refnamediv>
+
+ <indexterm><primary>SPI_getargcount</primary></indexterm>
+
+ <refsynopsisdiv>
+<synopsis>
+int SPI_getargcount(void * <parameter>plan</parameter>)
+</synopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+  <title>Description</title>
+
+  <para>
+   <function>SPI_getargcount</function> returns the number of arguments
needed
+   when executing a plan prepared by <function>SPI_prepare</function>.
+  </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>
+  </variablelist>
+ </refsect1>
+
+ <refsect1>
+  <title>Return Value</title>
+  <para>
+    The expected argument count for the <parameter>plan</parameter> or
+    <symbol>SPI_ERROR_ARGUMENT</symbol> if the <parameter>plan
+    </parameter> is <symbol>NULL</symbol>
+  </para>
+ </refsect1>
+</refentry>
+
+<!-- *********************************************** -->
+
+<refentry id="spi-spi-getargtypeid">
+ <refmeta>
+  <refentrytitle>SPI_getargtypeid</refentrytitle>
+ </refmeta>
+
+ <refnamediv>
+  <refname>SPI_getargtypeid</refname>
+  <refpurpose>returns the expected typeid for the specified argument when
+  executing a plan prepared by
<function>SPI_prepare</function></refpurpose>
+ </refnamediv>
+
+ <indexterm><primary>SPI_getargtypeid</primary></indexterm>
+
+ <refsynopsisdiv>
+<synopsis>
+Oid SPI_getargtypeid(void * <parameter>plan</parameter>, int
<parameter>argIndex</parameter>)
+</synopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+  <title>Description</title>
+
+  <para>
+   <function>SPI_getargtypeid</function> returns the Oid representing the
type
+   id for argument at <parameter>argIndex</parameter> in a plan prepared by
+   <function>SPI_prepare</function>. First argument is at index zero.
+  </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>int <parameter>argIndex</parameter></literal></term>
+    <listitem>
+     <para>
+      zero based index of the argument
+     </para>
+    </listitem>
+   </varlistentry>
+  </variablelist>
+ </refsect1>
+
+ <refsect1>
+  <title>Return Value</title>
+  <para>
+    The type id of the argument at the given index or <symbol>
+    SPI_ERROR_ARGUMENT</symbol> if the <parameter>plan</parameter> is
+    <symbol>NULL</symbol> or <parameter>argIndex</parameter> is less than 0
or
+    not less than the number of arguments declared for the <parameter>plan
+    </parameter>
+  </para>
+ </refsect1>
+</refentry>
+
+<!-- *********************************************** -->
+
+<refentry id="spi-spi-is_cursor_plan">
+ <refmeta>
+  <refentrytitle>SPI_is_cursor_plan</refentrytitle>
+ </refmeta>
+
+ <refnamediv>
+  <refname>SPI_is_cursor_plan</refname>
+  <refpurpose>returns <symbol>true</symbol> if a plan
+  prepared by <function>SPI_prepare</function> can be passed
+  as an argument to <function>SPI_cursor_open</function></refpurpose>
+ </refnamediv>
+
+ <indexterm><primary>SPI_is_cursor_plan</primary></indexterm>
+
+ <refsynopsisdiv>
+<synopsis>
+bool SPI_is_cursor_plan(void * <parameter>plan</parameter>)
+</synopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+  <title>Description</title>
+
+  <para>
+   <function>SPI_is_cursor_plan</function> returns <symbol>true</symbol>
+   if a plan prepared by <function>SPI_prepare</function> can be passed
+   as an argument to <function>SPI_cursor_open</function> and <symbol>
+   false</symbol> if that is not the case. The criteria is that the
+   <parameter>plan</parameter> represents one single command and that this
+   command is a <command>SELECT</command> without an
<command>INTO</command>
+   clause.
+  </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>
+  </variablelist>
+ </refsect1>
+
+ <refsect1>
+  <title>Return Value</title>
+  <para>
+    <symbol>true</symbol> or <symbol>false</symbol> to indicate if the
+    <parameter>plan</parameter> can produce a cursor or not, or
+    <symbol>SPI_ERROR_ARGUMENT</symbol> if the <parameter>plan</parameter>
+    is <symbol>NULL</symbol>
+  </para>
+ </refsect1>
+</refentry>
+
+<!-- *********************************************** -->
+
 <refentry id="spi-spi-execp">
  <refmeta>
   <refentrytitle>SPI_execp</refentrytitle>
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 15 Feb 2004 13:47:18 -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 15 Feb 2004 13:47:32 -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,

Re: Some new SPI functions

From

Bruce Momjian

Date:

04 March 2004, 23:47:17

Patch applied.  Thanks.

---------------------------------------------------------------------------


Thomas Hallgren wrote:
> > Who did you think would do it?
> >
> > regards, tom lane
> >
> you :-)
>
> Seriously, I didn't give it much though. This is undoubtedly the best way
> although some projects handle it differently. A  tech-writer perhaps, with
> better writing skills then programmers in general. But people like that are
> probabaly not too common in the open source field. If you don't do like
> JBoss and charge for the docs (I'm *not* in favor of that).
>
> Anyway, here's the patch again. This time with documentation.
>
> Index: doc/src/sgml/spi.sgml
> ===================================================================
> retrieving revision 1.30
> diff -u -r1.30 spi.sgml
> --- doc/src/sgml/spi.sgml 1 Dec 2003 22:07:57 -0000 1.30
> +++ doc/src/sgml/spi.sgml 15 Feb 2004 13:47:13 -0000
> @@ -573,6 +573,190 @@
>
>  <!-- *********************************************** -->
>
> +<refentry id="spi-spi-getargcount">
> + <refmeta>
> +  <refentrytitle>SPI_getargcount</refentrytitle>
> + </refmeta>
> +
> + <refnamediv>
> +  <refname>SPI_getargcount</refname>
> +  <refpurpose>returns the number of arguments needed when executing a plan
> +  prepared by <function>SPI_prepare</function></refpurpose>
> + </refnamediv>
> +
> + <indexterm><primary>SPI_getargcount</primary></indexterm>
> +
> + <refsynopsisdiv>
> +<synopsis>
> +int SPI_getargcount(void * <parameter>plan</parameter>)
> +</synopsis>
> + </refsynopsisdiv>
> +
> + <refsect1>
> +  <title>Description</title>
> +
> +  <para>
> +   <function>SPI_getargcount</function> returns the number of arguments
> needed
> +   when executing a plan prepared by <function>SPI_prepare</function>.
> +  </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>
> +  </variablelist>
> + </refsect1>
> +
> + <refsect1>
> +  <title>Return Value</title>
> +  <para>
> +    The expected argument count for the <parameter>plan</parameter> or
> +    <symbol>SPI_ERROR_ARGUMENT</symbol> if the <parameter>plan
> +    </parameter> is <symbol>NULL</symbol>
> +  </para>
> + </refsect1>
> +</refentry>
> +
> +<!-- *********************************************** -->
> +
> +<refentry id="spi-spi-getargtypeid">
> + <refmeta>
> +  <refentrytitle>SPI_getargtypeid</refentrytitle>
> + </refmeta>
> +
> + <refnamediv>
> +  <refname>SPI_getargtypeid</refname>
> +  <refpurpose>returns the expected typeid for the specified argument when
> +  executing a plan prepared by
> <function>SPI_prepare</function></refpurpose>
> + </refnamediv>
> +
> + <indexterm><primary>SPI_getargtypeid</primary></indexterm>
> +
> + <refsynopsisdiv>
> +<synopsis>
> +Oid SPI_getargtypeid(void * <parameter>plan</parameter>, int
> <parameter>argIndex</parameter>)
> +</synopsis>
> + </refsynopsisdiv>
> +
> + <refsect1>
> +  <title>Description</title>
> +
> +  <para>
> +   <function>SPI_getargtypeid</function> returns the Oid representing the
> type
> +   id for argument at <parameter>argIndex</parameter> in a plan prepared by
> +   <function>SPI_prepare</function>. First argument is at index zero.
> +  </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>int <parameter>argIndex</parameter></literal></term>
> +    <listitem>
> +     <para>
> +      zero based index of the argument
> +     </para>
> +    </listitem>
> +   </varlistentry>
> +  </variablelist>
> + </refsect1>
> +
> + <refsect1>
> +  <title>Return Value</title>
> +  <para>
> +    The type id of the argument at the given index or <symbol>
> +    SPI_ERROR_ARGUMENT</symbol> if the <parameter>plan</parameter> is
> +    <symbol>NULL</symbol> or <parameter>argIndex</parameter> is less than 0
> or
> +    not less than the number of arguments declared for the <parameter>plan
> +    </parameter>
> +  </para>
> + </refsect1>
> +</refentry>
> +
> +<!-- *********************************************** -->
> +
> +<refentry id="spi-spi-is_cursor_plan">
> + <refmeta>
> +  <refentrytitle>SPI_is_cursor_plan</refentrytitle>
> + </refmeta>
> +
> + <refnamediv>
> +  <refname>SPI_is_cursor_plan</refname>
> +  <refpurpose>returns <symbol>true</symbol> if a plan
> +  prepared by <function>SPI_prepare</function> can be passed
> +  as an argument to <function>SPI_cursor_open</function></refpurpose>
> + </refnamediv>
> +
> + <indexterm><primary>SPI_is_cursor_plan</primary></indexterm>
> +
> + <refsynopsisdiv>
> +<synopsis>
> +bool SPI_is_cursor_plan(void * <parameter>plan</parameter>)
> +</synopsis>
> + </refsynopsisdiv>
> +
> + <refsect1>
> +  <title>Description</title>
> +
> +  <para>
> +   <function>SPI_is_cursor_plan</function> returns <symbol>true</symbol>
> +   if a plan prepared by <function>SPI_prepare</function> can be passed
> +   as an argument to <function>SPI_cursor_open</function> and <symbol>
> +   false</symbol> if that is not the case. The criteria is that the
> +   <parameter>plan</parameter> represents one single command and that this
> +   command is a <command>SELECT</command> without an
> <command>INTO</command>
> +   clause.
> +  </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>
> +  </variablelist>
> + </refsect1>
> +
> + <refsect1>
> +  <title>Return Value</title>
> +  <para>
> +    <symbol>true</symbol> or <symbol>false</symbol> to indicate if the
> +    <parameter>plan</parameter> can produce a cursor or not, or
> +    <symbol>SPI_ERROR_ARGUMENT</symbol> if the <parameter>plan</parameter>
> +    is <symbol>NULL</symbol>
> +  </para>
> + </refsect1>
> +</refentry>
> +
> +<!-- *********************************************** -->
> +
>  <refentry id="spi-spi-execp">
>   <refmeta>
>    <refentrytitle>SPI_execp</refentrytitle>
> 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 15 Feb 2004 13:47:18 -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 15 Feb 2004 13:47:32 -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,
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073