PostgreSQL return select results AND add them to temporary table?

Question

I want to select a set of rows and return them to the client, but I would also like to insert just the primary keys (integer id) from the result set into a temporary table for use in later joins in the same transaction.

This is for sync, where subsequent queries tend to involve a join on the results from earlier queries.

What's the most efficient way to do this?

I'm reticent to execute the query twice, although it may well be fast if it was added to the query cache. An alternative is store the entire result set into the temporary table and then select from the temporary afterward. That also seems wasteful (I only need the integer id in the temp table.) I'd be happy if there was a SELECT INTO TEMP that also returned the results.

Currently the technique used is construct an array of the integer ids in the client side and use that in subsequent queries with IN. I'm hoping for something more efficient.

I'm guessing it could be done with stored procedures? But is there a way without that?

Answer 1

I think you can do this with a Postgres feature that allows data modification steps in CTEs. The more typical reason to use this feature is, say, to delete records for a table and then insert them into a log table. However, it can be adapted to this purpose. Here is one possible method (I don't have Postgres on hand to test this):

with q as (
      <your query here>
     ),
     t as (
      insert into temptable(pk)
          select pk
          from q
     )
select *
from q;

Usually, you use the returning clause with the data modification queries in order to capture the data being modified.