4

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?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Eloff
  • 20,828
  • 17
  • 83
  • 112

1 Answers1

8

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Interesting, I'll try these. With the second format, is there a reason to wrap the whole thing in "with"? Shouldn't "insert into temp select * from ... returning *" work by itself? – Eloff Aug 30 '14 at 01:07
  • @Eloff . . . Yes. I just prefer to have `select` statements return data and `update`/`delete`/`insert` modify data as they do in other databases. – Gordon Linoff Aug 30 '14 at 01:10
  • The first (cheaper) query should actually work. `Data-modifying statements in WITH are executed exactly once, and always to completion.` Details: http://stackoverflow.com/questions/15809463/postgresql-using-foreign-keys-delete-parent-if-its-not-referenced-by-any-othe/15810159#15810159 – Erwin Brandstetter Aug 30 '14 at 01:13
  • @ErwinBrandstetter . . . My concern is whether the `q` from the `with` would be in the right scope for the `insert`. – Gordon Linoff Aug 30 '14 at 01:26
  • CTEs referencing each other are executed in sequence automatically (while the order of execution is arbitrary without cross-references). So, yes, visibility works out. I have used this before. – Erwin Brandstetter Aug 30 '14 at 01:29