I have to reuse a direct result massive nested Choose query in other queries within one transaction. Is it feasible?

-- Thanks!

Inside a saved procedure/function this can be done :

DECLARE mylist INTEGER[]
...
...
mylist := array( SELECT primarykey FROM bigquery );
...
...
SELECT foo FROM bar WHERE id =ANY( mylist );
SELECT x FROM y WHERE id =ANY( mylist );

That's pretty helpful to collect a listing of PKs (utilizing a large slow query) and do several queries onto it, especially thinking about plpgsql function can return several result sets towards the client (RETURN SETOF refcursor).

For example I grab 50 property listing ids utilizing a large search query (gist indexes and geolocalization) the query includes many posts, joins, sorts, hashes, having a final LIMIT/OFFSET, also it appears to become quite a bit faster not to drag all of the posts through all of this, rather only using the posts which are utilized in the search, grab a listing of ids, apply LIMIT/OFFSET, and return to grab all of the posts.

Then by using this listing of ids, I grab info using their company tables, like contacts, phone#, etc. Since one listing might have several phone# or contacts, it's simpler and faster to come back individuals individually using another cursor and allow the application restore it together, than using something similar to array_agg() to come back a listing of phone# in every result line.

Positive thing is you're able to chose if you are using pre-prepared claims, or also employ EXECUTE so postgres can replan the queries knowing the size of the array should you expect it to be large.

Another option would be only to

  • make use of a temporary table (reduced, however, you can Evaluate it that is very helpful)
  • or produce a cursor in your large query, and make up a plpgsql function that returns the items in this cursor, then technology-not only several occasions

You could utilize a brief table:

SELECT foo,bar INTO TEMP TABLE temp_table
    FROM real_table
    ....

Do some stuff with temp_table

DROP TABLE temp_table;

The temporary table can also be dropped whenever you close your connection.