Re: Is querying SPITupleTable with SQL possible?

[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.