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 <[EMAIL PROTECTED]> 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
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
