Message-ID: <3F783BD8.48101D85@nsd.ca>
Date: Mon, 29 Sep 2003 10:04:08 -0400
From: Jean-Luc Lachance <jllachan@nsd.ca>
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Dan Langille <dan@langille.org>, pgsql-sql@postgresql.org
Subject: Re: 
References: <20030928151910.S78282@laptop.unixathome.org>
	<10801.1064778322@sss.pgh.pa.us>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Wouldn't:

insert into r 
select count(*) 
from users 
where date( lastlogin) > current_date - MaxDays * interval '' 1 day''
group by date( lastlogin);

be more efficient?


Tom Lane wrote:
> 
> Dan Langille <dan@langille.org> writes:
> >          WHERE lastlogin between current_date - interval \''' ||
> > quote_literal(i - 1) || '' days\'
> >                              AND current_date - interval \''' ||
> > quote_literal(i)     || '' days\''';
> 
> IIRC, quote_literal() puts single quotes around its result.  So you have
> too many quotes there.  Given that you know i is an integer, you don't
> really need quote_literal for it.  Actually, you don't need EXECUTE
> here at all.  Why not just
> 
>     FOR i IN 1..MaxDays LOOP
>         SELECT count(*)
>           INTO r
>           FROM users
>          WHERE lastlogin between current_date - (i-1) * interval ''1 day''
>                              AND current_date - i * interval ''1 day'';
>         RETURN NEXT r;
>     END LOOP;
> 
>                         regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings