It's not possible to use temporary tables in queries used with the sql:query(q) or sql:execute(q) built-in functions. This includes using these BIFs to call on SQL functions that use temp tables. Doing so will result in an error such as:
cannot PREPARE a transaction that has operated on temporary tables
It is not currently allowed to PREPARE a transaction that has executed any operations involving temporary tables, created any cursors WITH HOLD, or executed LISTEN, UNLISTEN, or NOTIFY. Those features are too tightly tied to the current session to be useful in a transaction to be prepared.
Furthermore, using temp tables in any scenario is discouraged because of the potential amount of memory consumed.