Thread: LIMIT problem
suppose i request SELECT foo(t.x) FROM t LIMIT 1; Whither it DEFINED how many times foo() will be executed? May anyone rely on it? Or we have to avoid this non SQLish trick?
On 04/30/2010 06:20 PM, silly sad wrote: > suppose i request > > SELECT foo(t.x) FROM t LIMIT 1; > > Whither it DEFINED how many times foo() will be executed? > > May anyone rely on it? > Or we have to avoid this non SQLish trick? > It will execute foo only once, and give only one row out of the n rows it returns. If I'm wrong please correct me. -- Nilesh Govindarajan Site & Server Administrator www.itech7.com मेरा भारत महान ! मम भारत: महत्तम भवतु !
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 ---147 636 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 rowsthe 2nd row has 4 and generate_series(5,4) returns 0 rowsthe 3rdrow has 7 and generate_series(5,7) returns 3 rows And the first of those is returned.
On 04/30/10 16:57, Nilesh Govindarajan wrote: > On 04/30/2010 06:20 PM, silly sad wrote: >> suppose i request >> >> SELECT foo(t.x) FROM t LIMIT 1; >> >> Whither it DEFINED how many times foo() will be executed? >> >> May anyone rely on it? >> Or we have to avoid this non SQLish trick? >> > > It will execute foo only once, and give only one row out of the n rows > it returns. If I'm wrong please correct me. I did not asked how many times foo() would be executed.
On 05/01/10 03:58, Jasen Betts wrote: > 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; > this subselect "isolation" looks like a safe way. Unlike LIMITed select looks unpredictable to me. Thanx for assistance :)