To: pgsql-sql@postgresql.org
Path: not-for-mail
From: Jasen Betts <jasen@xnet.co.nz>
Newsgroups: gmane.comp.db.postgresql.sql
Subject: Re: LIMIT problem
Date: 30 Apr 2010 23:58:15 GMT
Organization: Dis (not Dat) Organisation
Lines: 59
Message-ID: <hrfqqn$u2r$1@reversiblemaps.ath.cx>
References: <4BDAD20A.1090503@bankir.ru>
NNTP-Posting-Host: localhost
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
User-Agent: slrn/pre1.0.0-2 (Linux)

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.