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: MaxDayscount
------- 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: MaxDayscount
------- 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/
