Hi,
I have a volatile function that returns multiple rows. It may also
return nothing. Now, I want to write an SQL statement that calls this
function until it returns an empty result set and returns all the rows.
So, in principle I want to:
WITH RECURSIVE
t AS (
SELECT * FROM xx()
UNION ALL
SELECT * FROM xx()
)
SELECT * FROM t;
But that's not recursive because the union all part lacks a reference to t.
Next I tried this:
WITH RECURSIVE
t AS (
SELECT * FROM xx()
UNION ALL
SELECT * FROM xx() WHERE EXISTS (SELECT 1 FROM t)
)
SELECT * FROM t;
But the reference to t is not allowed in a subquery.
What's the best (or at least a working) way to achieve what I want?
I can do it in plpgsql. But that would mean to accumulate the complete
result in memory first, right? I need to avoid that.
Thanks,
Torsten