From: "Dan Langille" <dan@langille.org>
To: Tom Lane <tgl@sss.pgh.pa.us>
Date: Sun, 28 Sep 2003 16:06:20 -0400
MIME-Version: 1.0
Subject: Re: 
Cc: pgsql-sql@postgresql.org
Message-ID: <3F7706FC.13057.3006C804@localhost>
References: <20030928151910.S78282@laptop.unixathome.org> 
In-reply-to: <10801.1064778322@sss.pgh.pa.us>
Content-type: text/plain; charset=US-ASCII
Content-transfer-encoding: 7BIT
Content-description: Mail message body

On 28 Sep 2003 at 15:45, 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;

Thank you.  I had to replace the " with \', but here is what I came 
up with (after adding another item to the SELECT):

CREATE OR REPLACE FUNCTION LoginCounts(int) RETURNS SETOF 
logincounts_record AS '
DECLARE
    MaxDays ALIAS for $1;

    r   logincounts_record%rowtype;
    i   integer;

BEGIN
    raise notice ''MaxDays'';
    FOR i IN 1..MaxDays LOOP
        SELECT 1 AS days,
               count(*) as 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;
    RETURN;
END
'
LANGUAGE plpgsql;

However, the results are confusing.  I'm getting the wrong number of 
parameters.  The value being returned appears to be the value 
supplied.  But the log results show an interesting pattern in the 
number of selects being run.


working-copy.freshports.org=# select count(*) from LoginCounts(1);
NOTICE:  MaxDays
 count
-------
     1
(1 row)

The log says:

2003-09-28 16:01:54 [32813]  LOG:  query: select count(*) from 
LoginCounts(1);
2003-09-28 16:01:54 [32813]  NOTICE:  MaxDays
2003-09-28 16:01:54 [32813]  LOG:  query: select cast($1 as timestamp 
without time zone) - $2;


working-copy.freshports.org=# select count(*) from LoginCounts(2);
NOTICE:  MaxDays
 count
-------
     2
(1 row)

And the log says:

2003-09-28 16:02:04 [32813]  LOG:  query: select count(*) from 
LoginCounts(2);
2003-09-28 16:02:04 [32813]  NOTICE:  MaxDays
2003-09-28 16:02:04 [32813]  LOG:  query: select cast($1 as timestamp 
without time zone) - $2;
2003-09-28 16:02:04 [32813]  LOG:  query: select cast($1 as timestamp 
without time zone) - $2;
2003-09-28 16:02:04 [32813]  LOG:  query: select cast($1 as timestamp 
without time zone) - $2;


The type in question is:


CREATE TYPE logincounts_record AS (
    days            integer,
    count           integer
);
-- 
Dan Langille : http://www.langille.org/