PostgreSQL returns selection results and adds them to a temporary table?
I want to select a set of rows and return them to the client, but I would also like to insert only the primary keys (integer id) from the result set into a temporary table for use in subsequent joins in a single transaction.
This is for synchronization, where subsequent requests usually involve merging the results of previous requests.
What's the most efficient way to do this?
I am discreetly running the request twice, although it might be fast if it was added to the request cache. An alternative is to store the entire result set into a temporary table and then select from the temporary one. This also seems wasteful (I only need an integer id in the temp table.) I would be glad if there was a SELECT INTO TEMP that also returned results.
The current method is to build an array of integer IDs on the client side and use that in subsequent requests using IN. I am hoping for something more efficient.
I am guessing it can be done with stored procedures? But is there a way without this?
source to share
I think you can do this with a Postgres function that allows you to perform data modification steps in a CTE. A more typical reason to use this function is, say, to delete records for a table and then insert them into the log table. However, it can be adapted for this purpose. Here's one possible way (I don't have Postgres to test this):
with q as (
<your query here>
),
t as (
insert into temptable(pk)
select pk
from q
)
select *
from q;
Typically, you use a returning
data change request clause to commit changed data.
source to share