On Thu, 2023-07-13 at 13:12 +0800, 黄宁 wrote: > I want to create some temporary tables in SPI, but after I created the table and inserted the data, I can’t query any data, why? > > the postgres version: > PostgreSQL 13.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.3.0, 64-bit > code: > > > int ret = 0; > SPI_connect(); > ret = SPI_execute("CREATE GLOBAL TEMP TABLE temp_table (id int, value text)", false, 0); > > ret = SPI_execute("INSERT INTO temp_table VALUES (1, 'a'), (2, 'b')", false, 0); > > ret = SPI_execute("SELECT * FROM temp_table", true, 0); > > if(SPI_processed > 0) ...
That's because you set "read_only" to "true" when you executed the query, so that the command counter is not incremented, and the query cannot see the results from the previous statement.
The documentation is quite clear here:
It is generally unwise to mix read-only and read-write commands within a single function using SPI; that could result in very confusing behavior, since the read-only queries would not see the results of any database updates done by the read-write queries.