Thread: Is querying SPITupleTable with SQL possible?

Dear Hackers

I am using PostgreSQL's SPI to execute a simple SQL query (SELECT * FROM 
...) via SPI_exec. As a a result, I get an SPITupleTable with the 
results of my query.

Now that I have the SPITupleTable, I was wondering if it would be 
possible to later query over it further in my SQL statements using SPI, 
for example, something a bit similar to SPI_Exec ("Select * FROM 
:mySPITupleTable", 0);

My motivation is to treat, and use the SPITupleTable as 'intermediate' 
or 'temporary' tables which I would discard early - I want to apply a 
series of manipulations to my SPITupleTable before I would finally store 
it in the tablespace. Therefore, minimization of any overheads is also 
very important. I understand that I could introduce a CREATE TABLE to my 
SQL query and reference a table in that way, but I am under the 
impression that it would incur unnecessary overheads?

So, I would be grateful if anyone could help me understand how to 
manipulate the SPITupleTable further with SQL or indicate if it is at 
all possible. In the case that it is not possible, I would also be 
interested in alternatives and discussion on overheads.

Thanks in advance.

Best,
Tom

Tom Mercha <mercha_t@hotmail.com> writes:
> I am using PostgreSQL's SPI to execute a simple SQL query (SELECT * FROM 
> ...) via SPI_exec. As a a result, I get an SPITupleTable with the 
> results of my query.
> Now that I have the SPITupleTable, I was wondering if it would be 
> possible to later query over it further in my SQL statements using SPI, 
> for example, something a bit similar to SPI_Exec ("Select * FROM 
> :mySPITupleTable", 0);

It's possible you could use the "transition table" (aka
EphemeralNamedRelation) infrastructure for this, though I'm not sure
if it's really a close fit, or whether it's been built out enough to
support this usage.  From memory, it wants to work with tuplestores,
which are a bit heavier-weight than SPITupleTables.

            regards, tom lane

On 02/10/2019 16:11, Tom Lane wrote:
> Tom Mercha <mercha_t@hotmail.com> writes:
>> I am using PostgreSQL's SPI to execute a simple SQL query (SELECT * FROM
>> ...) via SPI_exec. As a a result, I get an SPITupleTable with the
>> results of my query.
>> Now that I have the SPITupleTable, I was wondering if it would be
>> possible to later query over it further in my SQL statements using SPI,
>> for example, something a bit similar to SPI_Exec ("Select * FROM
>> :mySPITupleTable", 0);
> 
> It's possible you could use the "transition table" (aka
> EphemeralNamedRelation) infrastructure for this, though I'm not sure
> if it's really a close fit, or whether it's been built out enough to
> support this usage.  From memory, it wants to work with tuplestores,
> which are a bit heavier-weight than SPITupleTables.
> 
>             regards, tom lane
> 

Thanks for this feedback! The EphemeralNamedRelation seems that it could 
be a good fit for what I'm looking for.

However, I'm not quite so sure how I can query over the 
EphemeralNamedRelation using SQL? Could someone indicate where I can 
find an example?

Regards
Tom

On 10/2/19 2:53 PM, Tom Mercha wrote:

> However, I'm not quite so sure how I can query over the 
> EphemeralNamedRelation using SQL? Could someone indicate where I can 
> find an example?

You could look in the documentation for CREATE TRIGGER in PG 10
or later, specifically the clauses like REFERENCING NEW TABLE AS foo.

https://www.postgresql.org/docs/10/sql-createtrigger.html

While the trigger function is executing, it can do SPI SQL
queries exactly as if there is a table named 'foo' sitting there,
but it only "exists" for that function and only until it returns.

Regards,
-Chap

Hi,
I have had see your discussion about node EphemeralNamedRelation with the Community.
Now, I want to use this node in SQL(for test), I have saw the manual but could not understand,
can you show me a example on how to use it in SQL?

Thanks a lot~

Regards, wu fei

-----Original Message-----
From: Tom Mercha [mailto:mercha_t@hotmail.com] 
Sent: 2019年10月3日 2:53
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-hackers@postgresql.org
Subject: Re: Is querying SPITupleTable with SQL possible?

On 02/10/2019 16:11, Tom Lane wrote:
> Tom Mercha <mercha_t@hotmail.com> writes:
>> I am using PostgreSQL's SPI to execute a simple SQL query (SELECT * 
>> FROM
>> ...) via SPI_exec. As a a result, I get an SPITupleTable with the 
>> results of my query.
>> Now that I have the SPITupleTable, I was wondering if it would be 
>> possible to later query over it further in my SQL statements using 
>> SPI, for example, something a bit similar to SPI_Exec ("Select * FROM 
>> :mySPITupleTable", 0);
> 
> It's possible you could use the "transition table" (aka
> EphemeralNamedRelation) infrastructure for this, though I'm not sure 
> if it's really a close fit, or whether it's been built out enough to 
> support this usage.  From memory, it wants to work with tuplestores, 
> which are a bit heavier-weight than SPITupleTables.
> 
>             regards, tom lane
> 

Thanks for this feedback! The EphemeralNamedRelation seems that it could be a good fit for what I'm looking for.

However, I'm not quite so sure how I can query over the EphemeralNamedRelation using SQL? Could someone indicate where
Ican find an example?
 

Regards
Tom

[un-top-postifying]

On Fri, Dec 20, 2019 at 2:53 PM Wu, Fei <wufei.fnst@cn.fujitsu.com> wrote:
>> On 02/10/2019 16:11, Tom Lane wrote:
>> > Tom Mercha <mercha_t@hotmail.com> writes:
>> >> I am using PostgreSQL's SPI to execute a simple SQL query (SELECT *
>> >> FROM
>> >> ...) via SPI_exec. As a a result, I get an SPITupleTable with the
>> >> results of my query.
>> >> Now that I have the SPITupleTable, I was wondering if it would be
>> >> possible to later query over it further in my SQL statements using
>> >> SPI, for example, something a bit similar to SPI_Exec ("Select * FROM
>> >> :mySPITupleTable", 0);
>> >
>> > It's possible you could use the "transition table" (aka
>> > EphemeralNamedRelation) infrastructure for this, though I'm not sure
>> > if it's really a close fit, or whether it's been built out enough to
>> > support this usage.  From memory, it wants to work with tuplestores,
>> > which are a bit heavier-weight than SPITupleTables.
>>
>> Thanks for this feedback! The EphemeralNamedRelation seems that it could be a good fit for what I'm looking for.
>>
>> However, I'm not quite so sure how I can query over the EphemeralNamedRelation using SQL? Could someone indicate
whereI can find an example?
 
>
> I have had see your discussion about node EphemeralNamedRelation with the Community.
> Now, I want to use this node in SQL(for test), I have saw the manual but could not understand,
> can you show me a example on how to use it in SQL?

I missed this thread before.  If you want to expose an ENR to SQL
you'll need to write some C code for now (unless someone has added
support for other languages?).  Try something like this (not tested):

EphemeralNamedRelation enr = palloc0(sizeof(*enr));

enr->md.name = "my_magic_table";
enr->md.reliddesc = InvalidOid;
enr->md.tupdesc = my_magic_table_tuple_descriptor;
enr->md.enrtype = ENR_NAMED_TUPLESTORE;
enr->md.enrtuples = how_many_tuples_to_tell_the_planner_we_have;
enr->reldata = my_tupestorestate;
rc = SPI_register_relation(enr);
if (rc != SPI_OK_REL_REGISTER)
  explode();

You will need to come up with a TupleDesc that describes the columns
in your magic table, and a Tuplestorestate that holds the tuples.
After that you should be able to plan and execute read-only SQL
queries against that tuplestore using that name, via the usual
SPI_xxx() interfaces.  I'm not sure how you'd really do this though:
you might need to make a function that takes a query as a string, then
does the above setup in a new SPI connection, and then executes the
query.  This would probably be a lot more fun from a PL like Python.
(If you wanted to create ENRs that are available to your top level
connection, I haven't looked into it but I suspect that'd require more
machinery than we have right now, and I'm not sure if it'd be a good
idea.)

In theory, there could be a new type ENR_SPI_TUPLE_TABLE that could
work with SPITupleTable instead of Tuplestore.  The idea was that we
might be able to do more clever things like that in the future, which
is why we tried to make it at least a little bit general.  One thing
that could be nice would be SQL Server style table variables; you
could have a functions that receive them as parameters, return them,
and be able to insert/update/delete.  That's a bit far fetched, but
gives some idea of the reason we invented QueryEnvironment and passed
to all the right places in the planner and executor (or perhaps not
enough yet, we do occasionally find places that we forgot to pass
it...).  So yeah, to use an SPITupleTable now you'd need to insert its
contents into a Tuplestorestate.

As mentioned, this is the mechanism that is used to support SQL
standard "transition tables" in triggers.  You can see that the
function SPI_register_trigger_data() just does what I showed above to
expose all the transition tables to your trigger's SQL statements.
That part even works in Perl, Python, TCL triggers, but to make your
own tables in higher level languages you'd need to expose more
interfaces and figure out sane ways to get TupleDescriptor and
interact with Tuplestore.  If you want to see examples of SQL queries
inside triggers that access transition tables, check out
src/test/regress/expected/triggers.out and
src/pl/plpython/expected/plpython_trigger.out.