From: Jean-Luc Lachance <jllachan@nsd.ca>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Dan Langille <dan@langille.org>
Cc: pgsql-sql@postgresql.org
Subject: Re:
Date: Mon, 29 Sep 2003 10:04:08 -0400
Message-ID: <3F783BD8.48101D85@nsd.ca> (raw)
References: <20030928151910.S78282@laptop.unixathome.org>
<10801.1064778322@sss.pgh.pa.us>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jllachan@nsd.ca, tgl@sss.pgh.pa.us, dan@langille.org
Subject: Re:
In-Reply-To: <3F783BD8.48101D85@nsd.ca>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox