Re: calling plpgsql from c

Tom Lane wrote:
> Max Jacob <Max.Jacob@ircam.fr> writes:
> > I'm trying to call plpgsql functions from c functions directly through
> > the Oid, but i have a problem: it seems that the plpgsql interpreter
> > calls SPI_connect and fails even if the caller has already
> > spi-connected.
>
> This is a safety check.  If you are connected to SPI, you need to call
> SPI_push() and SPI_pop() around any operation that might involve
> recursive use of SPI.  That helps delimit "your" calls versus "their"
> calls versus "no man's land".
>
> It does seem that this is quite undocumented though.  Jan?

I have documented SPI_push() and SPI_pop() with the attached SGML patch.

--
  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
Index: doc/src/sgml/spi.sgml
===================================================================
RCS file: /cvsroot/pgsql-server/doc/src/sgml/spi.sgml,v
retrieving revision 1.32
diff -c -c -r1.32 spi.sgml
*** doc/src/sgml/spi.sgml    5 Mar 2004 01:00:45 -0000    1.32
--- doc/src/sgml/spi.sgml    17 Mar 2004 01:04:25 -0000
***************
*** 199,204 ****
--- 199,266 ----

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

+ <refentry id="spi-spi-push">
+  <refmeta>
+   <refentrytitle>SPI_push</refentrytitle>
+  </refmeta>
+
+  <refnamediv>
+   <refname>SPI_push</refname>
+   <refpurpose>pushes SPI stack to allow recursive SPI calls</refpurpose>
+  </refnamediv>
+
+  <indexterm><primary>SPI_push</primary></indexterm>
+
+  <refsynopsisdiv>
+ <synopsis>
+ void SPI_push(void)
+ </synopsis>
+  </refsynopsisdiv>
+
+  <refsect1>
+   <title>Description</title>
+
+   <para>
+    <function>SPI_push</function> pushes a new environment on to the
+    SPI call stack, allowing recursive calls to use a new environment.
+   </para>
+  </refsect1>
+
+ </refentry>
+
+ <!-- *********************************************** -->
+
+ <refentry id="spi-spi-pop">
+  <refmeta>
+   <refentrytitle>SPI_pop</refentrytitle>
+  </refmeta>
+
+  <refnamediv>
+   <refname>SPI_pop</refname>
+   <refpurpose>pops SPI stack to allow recursive SPI calls</refpurpose>
+  </refnamediv>
+
+  <indexterm><primary>SPI_pop</primary></indexterm>
+
+  <refsynopsisdiv>
+ <synopsis>
+ void SPI_pop(void)
+ </synopsis>
+  </refsynopsisdiv>
+
+  <refsect1>
+   <title>Description</title>
+
+   <para>
+    <function>SPI_pop</function> pops the previous environment from the
+    SPI call stack.  For use when returning from recursive SPI calls.
+   </para>
+  </refsect1>
+
+ </refentry>
+
+ <!-- *********************************************** -->
+
  <refentry id="spi-spi-exec">
   <refmeta>
    <refentrytitle>SPI_exec</refentrytitle>
Index: src/backend/executor/spi.c
===================================================================
RCS file: /cvsroot/pgsql-server/src/backend/executor/spi.c,v
retrieving revision 1.110
diff -c -c -r1.110 spi.c
*** src/backend/executor/spi.c    5 Mar 2004 00:47:01 -0000    1.110
--- src/backend/executor/spi.c    17 Mar 2004 01:04:27 -0000
***************
*** 201,212 ****
--- 201,214 ----
      SPI_tuptable = NULL;
  }

+ /* Pushes SPI stack to allow recursive SPI calls */
  void
  SPI_push(void)
  {
      _SPI_curid++;
  }

+ /* Pops SPI stack to allow recursive SPI calls */
  void
  SPI_pop(void)
  {
Index: src/include/executor/spi.h
===================================================================
RCS file: /cvsroot/pgsql-server/src/include/executor/spi.h,v
retrieving revision 1.42
diff -c -c -r1.42 spi.h
*** src/include/executor/spi.h    5 Mar 2004 00:47:01 -0000    1.42
--- src/include/executor/spi.h    17 Mar 2004 01:04:28 -0000
***************
*** 81,88 ****
  extern int    SPI_finish(void);
  extern void SPI_push(void);
  extern void SPI_pop(void);
! extern int    SPI_exec(const char *src, int tcount);
! extern int SPI_execp(void *plan, Datum *values, const char *Nulls,
            int tcount);
  extern int SPI_execp_current(void *plan, Datum *values, const char *Nulls,
                               bool useCurrentSnapshot, int tcount);
--- 81,88 ----
  extern int    SPI_finish(void);
  extern void SPI_push(void);
  extern void SPI_pop(void);
! extern int  SPI_exec(const char *src, int tcount);
! extern int  SPI_execp(void *plan, Datum *values, const char *Nulls,
            int tcount);
  extern int SPI_execp_current(void *plan, Datum *values, const char *Nulls,
                               bool useCurrentSnapshot, int tcount);