Thread

1. Can I use SPI in postgres.c

   Limin Liu <limin@pumpkinnet.com> — 2001-03-22T02:03:45Z

   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
   
   
   
   

2. Re: Can I use SPI in postgres.c

   Limin Liu <limin@pumpkinnet.com> — 2001-03-22T02:37:37Z

   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
   >