Skip site navigation (1) Skip section navigation (2)

Re:

From: Jean-Luc Lachance <jllachan(at)nsd(dot)ca>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Dan Langille <dan(at)langille(dot)org>, pgsql-sql(at)postgresql(dot)org
Subject: Re:
Date: 2003-09-29 14:04:08
Message-ID: 3F783BD8.48101D85@nsd.ca (view raw or flat)
Thread:
Lists: pgsql-sql
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(at)langille(dot)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

In response to

  • Re: at 2003-09-28 19:45:22 from Tom Lane

Responses

  • Re: at 2003-09-29 14:03:19 from Dan Langille
  • Re: at 2003-09-29 21:19:04 from Dan Langille

pgsql-sql by date

Next:From: Christoph HallerDate: 2003-09-29 14:04:15
Subject: Re: Data Calculation
Previous:From: Dan LangilleDate: 2003-09-29 14:03:19
Subject: Re:

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group