Thread: Can I use SPI in postgres.c

Helper,

I need to create a temp table for each db connection.  So, I add the
dollowing code into postgres.c
----------------
   SPI_connect();
   SPI_exec("create temp table tbl_tmp (n int);",0);
   SPI_exec("insert into tbl_tmp values (1);",0);
   SPI_finish();
----------------
right after
----------------
    /*
     * POSTGRES main processing loop begins here
     *
     * If an exception is encountered, processing resumes here so we
abort
     * the current transaction and start a new one.
     */
----------------

I checked the return of SPI_exec and both are fine.  Then I run psql and
got two error messages, which contradicts to each other!
-------------------
db1=> select * from tbl_tmp;
ERROR:  Relation 'tbl_tmp' does not exist
db1=> create temp table tbl_tmp (n int);
ERROR:  Relation 'tbl_tmp' already exists
db1=>
-------------------

I checked the SPI document, but cannot find solution.  Can anyone please
tells me which document should I look into?  Or I cannot use SPI like
that at the frist place.  If that's the case, is any workaround?  The
base line is I cannot ask db client program to create that temp table.

Thank you very much

--
LM Liu

Currently I am using 7.1beta4, but I just learned that these SPI code works
fine in 7.02.
We can issue "select * from tbl_tmp" and psql will return the correct
information in the temp table!

> I need to create a temp table for each db connection.  So, I add the
> dollowing code into postgres.c
> ----------------
>    SPI_connect();
>    SPI_exec("create temp table tbl_tmp (n int);",0);
>    SPI_exec("insert into tbl_tmp values (1);",0);
>    SPI_finish();
> ----------------
> right after
> ----------------
>     /*
>      * POSTGRES main processing loop begins here
>      *
>      * If an exception is encountered, processing resumes here so we
> abort
>      * the current transaction and start a new one.
>      */
> ----------------
>
> I checked the return of SPI_exec and both are fine.  Then I run psql and
> got two error messages, which contradicts to each other!
> -------------------
> db1=> select * from tbl_tmp;
> ERROR:  Relation 'tbl_tmp' does not exist
> db1=> create temp table tbl_tmp (n int);
> ERROR:  Relation 'tbl_tmp' already exists
> db1=>
> -------------------
>
> I checked the SPI document, but cannot find solution.  Can anyone please
> tells me which document should I look into?  Or I cannot use SPI like
> that at the frist place.  If that's the case, is any workaround?  The
> base line is I cannot ask db client program to create that temp table.
>
> Thank you very much
>
> --
>
> LM Liu
>