I am trying to write a stored procedure that simply echoes massaged data from a fairly small result-set that is achieved from a complex slow query.
My objective is to write a fast stored proc aka to execute that slow complex query just once or as few times as possible. I Cannot use temporary tables because the session is shared through connection pooling. Array seems like the right fit but not sure how to echo results from it.
delimiter // CREATE OR REPLACE PROCEDURE (...) AS DECLARE qry QUERY(...) = <my complex slow select statement that returns a handful of rows> arr ARRAY(RECORD(...)) = COLLECT(qry); aggr1 BIGINT = 0; aggr2 BIGINT = 0; BEGIN FOR r IN arr LOOP aggr1 += r.col1 aggr2 += r.col2 END LOOP; ECHO SELECT ... include aggr1 & aggr2 & cols... FROM qry JOIN qry ; END // delimiter ;
How do I avoid the second SELECT statement and ECHO the results from arr directly?