Re:
От | Dan Langille |
---|---|
Тема | Re: |
Дата | |
Msg-id | 3F7706FC.13057.3006C804@localhost обсуждение исходный текст |
Ответ на | Re: (Tom Lane <tgl@sss.pgh.pa.us>) |
Ответы |
Re:
|
Список | pgsql-sql |
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/
В списке pgsql-sql по дате отправления: