From: | Merlin Moncure <mmoncure(at)gmail(dot)com> |
---|---|
To: | Torsten Förtsch <torsten(dot)foertsch(at)gmx(dot)net> |
Cc: | PostgreSQL General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: SQL advice needed |
Date: | 2014-03-17 20:42:50 |
Message-ID: | CAHyXU0yGmmOWL_BAD4Mf+B8FDX7BZ0a2ckJZrA+XCd4PKsOe0g@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Mon, Mar 17, 2014 at 3:21 PM, Torsten Förtsch
<torsten(dot)foertsch(at)gmx(dot)net> wrote:
> 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.
I would test that assumption. This is better handled in loop IMO.
LOOP
RETURN QUERY SELECT * FROM xx();
IF NOT found
THEN
RETURN;
END IF;
END LOOP;
merlin
From | Date | Subject | |
---|---|---|---|
Next Message | David Johnston | 2014-03-17 20:54:04 | Re: SQL advice needed |
Previous Message | David Johnston | 2014-03-17 20:24:15 | A user's interpretation (and thoughts) of the WAL replay bug in 9.3 |