pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feed(no subject)
6+ messages / 3 participants
[nested] [flat]
* (no subject)
@ 2003-09-28 19:24 Dan Langille <dan@langille.org>
0 siblings, 2 replies; 6+ messages in thread
From: Dan Langille @ 2003-09-28 19:24 UTC (permalink / raw)
To: pgsql-sql
I'm trying to create a function which returns a result set using a dynamic
query. The problem occurs when it compiles. I suspect it's my quoting,
but I'm not sure of the cause.
CREATE OR REPLACE FUNCTION LoginCounts(int) RETURNS SETOF
logincounts_record AS '
DECLARE
MaxDays ALIAS for $1;
r logincounts_record%rowtype;
i integer;
BEGIN
FOR i IN 1..MaxDays LOOP
EXECUTE ''
SELECT count(*)
INTO r
FROM users
WHERE lastlogin between current_date - interval \''' ||
quote_literal(i - 1) || '' days\'
AND current_date - interval \''' ||
quote_literal(i) || '' days\''';
RETURN NEXT r;
END LOOP;
RETURN;
END
'
LANGUAGE plpgsql;
# select * from LoginCounts(2);
WARNING: Error occurred while executing PL/pgSQL function logincounts
WARNING: line 9 at execute statement
ERROR: parser: parse error at or near "days" at character 151
thnks
--
Dan Langille - http://www.langille.org/
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re:
@ 2003-09-28 19:45 Tom Lane <tgl@sss.pgh.pa.us>
parent: Dan Langille <dan@langille.org>
1 sibling, 1 reply; 6+ messages in thread
From: Tom Lane @ 2003-09-28 19:45 UTC (permalink / raw)
To: Dan Langille <dan@langille.org>; +Cc: pgsql-sql
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;
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re:
@ 2003-09-28 20:06 Dan Langille <dan@langille.org>
parent: Dan Langille <dan@langille.org>
1 sibling, 1 reply; 6+ messages in thread
From: Dan Langille @ 2003-09-28 20:06 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: 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: 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/
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re:
@ 2003-09-28 20:14 Tom Lane <tgl@sss.pgh.pa.us>
parent: Dan Langille <dan@langille.org>
0 siblings, 0 replies; 6+ messages in thread
From: Tom Lane @ 2003-09-28 20:14 UTC (permalink / raw)
To: Dan Langille <dan@langille.org>; +Cc: pgsql-sql
"Dan Langille" <dan@langille.org> writes:
> 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.
I dunno where the cast() queries are coming from, but note that they're
not your SELECT. You are misunderstanding how the code works if you
expect to see query LOG entries from plpgsql queries. For a
non-EXECUTEd plpgsql command, log_statement will only show the query the
first time it is executed in a session, because that log entry is
generated as a side-effect of parsing and planning.
As a means of tracing the execution of plpgsql functions, log_statement
is pretty worthless :-(. I would like us to develop a full-up tracing
and debugging facility for plpgsql, but we haven't got it yet.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re:
@ 2003-09-29 14:04 Jean-Luc Lachance <jllachan@nsd.ca>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: Jean-Luc Lachance @ 2003-09-29 14:04 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Dan Langille <dan@langille.org>; 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@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;
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re:
@ 2003-09-29 21:19 Dan Langille <dan@langille.org>
parent: Jean-Luc Lachance <jllachan@nsd.ca>
0 siblings, 0 replies; 6+ messages in thread
From: Dan Langille @ 2003-09-29 21:19 UTC (permalink / raw)
To: Jean-Luc Lachance <jllachan@nsd.ca>; +Cc: Dan Langille <dan@langille.org>; pgsql-sql
On 29 Sep 2003 at 10:04, Jean-Luc Lachance wrote:
> 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?
Yes it would, by a factor of 5.
P.S. but it would not show dates for which there are no logins. The
above can return zero rows. The previous example always returns
MaxDays rows.
--
Dan Langille : http://www.langille.org/
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2003-09-29 21:19 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-09-28 19:24 Dan Langille <dan@langille.org>
2003-09-28 19:45 ` Re: Tom Lane <tgl@sss.pgh.pa.us>
2003-09-29 14:04 ` Re: Jean-Luc Lachance <jllachan@nsd.ca>
2003-09-29 21:19 ` Re: Dan Langille <dan@langille.org>
2003-09-28 20:06 ` Re: Dan Langille <dan@langille.org>
2003-09-28 20:14 ` Re: Tom Lane <tgl@sss.pgh.pa.us>
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