SQL advice needed

Поиск
Список
Период
Сортировка
От Torsten Förtsch
Тема SQL advice needed
Дата
Msg-id 53275950.9060701@gmx.net
обсуждение исходный текст
Ответы Re: SQL advice needed  (Merlin Moncure <mmoncure@gmail.com>)
Re: SQL advice needed  (David Johnston <polobo@yahoo.com>)
Список pgsql-general
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


В списке pgsql-general по дате отправления:

Предыдущее
От: David Johnston
Дата:
Сообщение: Re: pgpgout/s without swapping--what does it mean?
Следующее
От: David Johnston
Дата:
Сообщение: A user's interpretation (and thoughts) of the WAL replay bug in 9.3