how to query temporary table?

[wonb168]

Connector-X is renowned for its swift data table queries,
but I need to retrieve results that require several steps of computation through temporary tables, like

CREATE TEMP TABLE t1 AS ...; 
CREATE TABLE t2 AS ...; 
...
SELECT * FROM tn ...; 

CANNOT use the WITH clause approach; there is too much historical code, and the workload to refactor it would be too large.
How can I utilize Connector-X for this situation?

[pangjunrong]

Hey @wonb168, could you try wrapping your computations in a stored procedure and calling that? Your last statement in the stored procedure would be SELECT * FROM tn ...; after all the computations.

So from the connector-x perspective, you're really just executing a single statement like EXECUTE YourStoredProcedureName' to fetch a (presumably large) result set which is computed using database resources. Alternatively depending on your use case, you could run a scheduled pre-computation within the database and insert the results into a final result table, which you can just retrieve what you need with SELECT * FROM tn ...; .

If you want this to be in Python, you could also use pyodbc or its alternatives to perform the computations you require and use connector-x for the fetching of result set.

I would say that the computation logic isn't really something connector-x is built to address, so it should be solved at the underlying data source level.