Re:

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