How to execute multiple queries in postgresql function?-CodePudding

The question is probably a bit misleading.

So I have this table Query with the column requests that contains queries in a form of a strings. Not all queries are in the same string. I mean I row of table = one string of query.

The question is how can I loop through this request column so the function would execute each and every reuqest in it?

I do know that there're such things as FOR IN LOOP and RETURN QUERY EXECUTE but the problem is since every single query is SELECT one , the result always will be the table with the different column names and different amount of columns, so I can't set RETURNS TABLE() properly.

Are there any other options to loop through this column of queries to execute them one by one?

Thank you beforehand!

CodePudding user response：

Yes. See here, section "43.7.3.5. Returning Cursors".

Your function should RETURN SETOF REFCURSOR.

Then, for each query, the function opens a cursor for it and returns it. See the last example in the section linked above.

Note the cursors will need to be named, but nothing stops you from generating names like "cur1", "cur2"...

Then, the application simply issues "FETCH ALL FROM cur1" followed by "FETCH ALL FROM cur2", etc.

Note that SETOF REFCURSOR does not mean all the cursors must have the same format. They can be different queries.

This all needs to happen in a transaction, because cursors are closed at the end of the transaction.

I have used this a long time ago. The problem was I had a search query that took a bit of time, and returned the ids of a bunch of records. These had a lot of one-to-many relationships with other tables, so returning the results in the form of a single table made the number of rows explode, with a lot of data duplication. For example, if the search returned 100 items, each belonging to the same 3 categories, why select the 3 categories 100 times in the result? The function returned SETOF REFCURSOR with all the data the application needed to reconstruct the objects and relationships, without wasting time transmitting the same data many times.