From: Jasen Betts <jasen@xnet.co.nz>
To: pgsql-sql@postgresql.org
Subject: Re: LIMIT problem
Date: 30 Apr 2010 23:58:15 GMT
Message-ID: <hrfqqn$u2r$1@reversiblemaps.ath.cx> (raw)
References: <4BDAD20A.1090503@bankir.ru>
On 2010-04-30, silly sad <sad@bankir.ru> wrote:
> suppose i request
>
> SELECT foo(t.x) FROM t LIMIT 1;
>
> Whither it DEFINED how many times foo() will be executed?
foo will be executed repeatedly until it returns a result or all the
rows in t are exhausted.
> May anyone rely on it?
not sure
> Or we have to avoid this non SQLish trick?
This will execute it once (or not at all where t has no rows)
SELECT foo(x) FROM (SELECT x FROM t LIMIT 1) as bar;
But may return a number of records differing from 1 in the case where
foo is a set-returning function.
jasen=# select a from foo;
a
---
1
4
7
6
3
6 rows)
jasen=# select generate_series(1,a),a from foo limit 1;
generate_series | a
-----------------+---
1 | 1
(1 row)
the first row jas 1 and the first row from
generate_series(1,1) is returned
jasen=# select generate_series(5,a),a from foo limit 1;
generate_series | a
-----------------+---
5 | 7
(1 row)
the 1st row has 1 and generate_series(5,1) returns 0 rows
the 2nd row has 4 and generate_series(5,4) returns 0 rows
the 3rd row has 7 and generate_series(5,7) returns 3 rows
And the first of those is returned.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jasen@xnet.co.nz
Subject: Re: LIMIT problem
In-Reply-To: <hrfqqn$u2r$1@reversiblemaps.ath.cx>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox