How to reuse query result within transaction in PostgreSQL?

Question

I need to reuse a result of massive nested SELECT query in other queries within one transaction. Is it possible?

-- Thanks!

Answer 1

In a stored procedure/function you can do this :

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 useful to gather a list of PKs (using a big slow query) and do several queries on it, especially considering plpgsql function can return several result sets to the client (RETURN SETOF refcursor).

For instance I grab 50 real estate listing ids using a big search query (gist indexes and geolocalization) ; the query includes many columns, joins, sorts, hashes, with a final LIMIT/OFFSET, and it seems to be quite a lot faster to not drag all the columns through all this, instead using only the columns that are used in the search, then grab a list of ids, apply LIMIT/OFFSET, and go back to grab all the columns.

Then using this list of ids, I grab info from other tables, like contacts, phone#, etc. Since one listing can have several phone# or contacts, it's easier and faster to return those separately using another cursor and let the application put it back together, than using something like array_agg() to return a list of phone# in each result line.

Good thing is you get to chose if you use pre-prepared statements, or you can also use EXECUTE so postgres can replan the queries knowing the length of the array if you expect it to sometimes be very large.

Another solution is simply to

• use a temporary table (slower, but you can ANALYZE it which is very useful)
• or create a cursor on your big query, and create a plpgsql function that returns the contents of this cursor, then you can use it several times