[un-top-postifying] On Fri, Dec 20, 2019 at 2:53 PM Wu, Fei <wufei.f...@cn.fujitsu.com> wrote: >> On 02/10/2019 16:11, Tom Lane wrote: >> > Tom Mercha <merch...@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 where I 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.