From: Dan Langille <dan@langille.org>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-sql@postgresql.org
Subject: Re:
Date: Sun, 28 Sep 2003 16:06:20 -0400
Message-ID: <3F7706FC.13057.3006C804@localhost> (raw)
In-Reply-To: <10801.1064778322@sss.pgh.pa.us>
References: <20030928151910.S78282@laptop.unixathome.org>
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/
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: dan@langille.org, tgl@sss.pgh.pa.us
Subject: Re:
In-Reply-To: <3F7706FC.13057.3006C804@localhost>
* 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