Обсуждение: Function Parameters in GROUP BY clause cause errors
<p align="LEFT"><span lang="en-us"><font face="Arial" size="2">When I use a parameter in a query which aggregates it
failswith a GROUP BY error.</font></span><span lang="en-us"></span><span lang="en-us"><font face="Arial" size="2"> What
syntaxcan I use to avoid this error?</font></span><span lang="en-us"></span><span lang="en-us"></span><p
align="LEFT"><spanlang="en-us"><font face="Arial" size="2">CREATE TABLE test (email_creation_datetime
timestamp);</font></span><palign="LEFT"><span lang="en-us"><font face="Arial" size="2">INSERT INTO test VALUES
('2006-03-2009:00');</font></span><p align="LEFT"><span lang="en-us"><font face="Arial" size="2">INSERT INTO test
VALUES('2006-03-20 09:15');</font></span><p align="LEFT"><span lang="en-us"><font face="Arial" size="2">INSERT INTO
testVALUES ('2006-03-20 09:30');</font></span><p align="LEFT"><span lang="en-us"><font face="Arial" size="2">INSERT
INTOtest VALUES ('2006-03-20 09:45');</font></span><span lang="en-us"></span><span lang="en-us"></span><p
align="LEFT"><spanlang="en-us"><font face="Arial" size="2">Query without parameters works fine:</font></span><p
align="LEFT"><spanlang="en-us"><font face="Arial" size="2">select to_char(to_timestamp(EXTRACT(HOUR FROM
em.email_creation_datetime)|| ':' || (EXTRACT(MINUTE FROM em.email_creation_datetime)::integer/30) * 30, 'HH24:MI'),
'HH24:MI')as TheInterval</font></span><p align="LEFT"><span lang="en-us"><font face="Arial" size="2">from test
em</font></span><palign="LEFT"><span lang="en-us"><font face="Arial" size="2">group by
to_char(to_timestamp(EXTRACT(HOURFROM em.email_creation_datetime) || ':' || (EXTRACT(MINUTE FROM
em.email_creation_datetime)::integer/30)* 30, 'HH24:MI') , 'HH24:MI')</font></span><span lang="en-us"></span><span
lang="en-us"></span><palign="LEFT"><span lang="en-us"><font face="Arial" size="2">theinterval</font></span><p
align="LEFT"><spanlang="en-us"><font face="Arial" size="2">09:30</font></span><p align="LEFT"><span lang="en-us"><font
face="Arial"size="2">09:00</font></span><p align="LEFT"><span lang="en-us"><font face="Arial" size="2">But the same
querywith a parameter returns a GROUP BY error:</font></span><p align="LEFT"><span lang="en-us"><font face="Arial"
size="2">CREATEOR REPLACE FUNCTION EmailByInterval(IntervalMinutes int) RETURNS SETOF test AS $$</font></span><p
align="LEFT"><spanlang="en-us"><font face="Arial" size="2">DECLARE rec RECORD;</font></span><p align="LEFT"><span
lang="en-us"><fontface="Arial" size="2">BEGIN</font></span><p align="LEFT"><span lang="en-us"><font face="Arial"
size="2">FORrec IN </font></span><p align="LEFT"><span lang="en-us"> <font face="Arial" size="2">select
to_char(to_timestamp(EXTRACT(HOURFROM em.email_creation_datetime) || ':' || (EXTRACT(MINUTE FROM
em.email_creation_datetime)::integer/IntervalMinutes)* IntervalMinutes, 'HH24:MI'), 'HH24:MI') as
TheInterval</font></span><palign="LEFT"><span lang="en-us"> <font face="Arial" size="2">from test
em</font></span><palign="LEFT"><span lang="en-us"> <font face="Arial" size="2">group by
to_char(to_timestamp(EXTRACT(HOURFROM em.email_creation_datetime) || ':' || (EXTRACT(MINUTE FROM
em.email_creation_datetime)::integer/IntervalMinutes)* IntervalMinutes, 'HH24:MI') , 'HH24:MI')</font></span><p
align="LEFT"><spanlang="en-us"><font face="Arial" size="2">LOOP</font></span><p align="LEFT"><span lang="en-us">
<fontface="Arial" size="2">RETURN NEXT rec;</font></span><p align="LEFT"><span lang="en-us"><font face="Arial"
size="2">ENDLOOP;</font></span><p align="LEFT"><span lang="en-us"><font face="Arial" size="2">RETURN;</font></span><p
align="LEFT"><spanlang="en-us"><font face="Arial" size="2">END;</font></span><p align="LEFT"><span lang="en-us"><font
face="Arial"size="2">$$ LANGUAGE plpgsql;</font></span><span lang="en-us"></span><span lang="en-us"></span><p
align="LEFT"><spanlang="en-us"><font face="Arial" size="2">Query returned successfully with no result in 70
ms.</font></span><spanlang="en-us"></span><span lang="en-us"></span><p align="LEFT"><span lang="en-us"><font
face="Arial"size="2">select * from emailbyinterval(30);</font></span><span lang="en-us"></span><span
lang="en-us"></span><palign="LEFT"><span lang="en-us"><font face="Arial" size="2">ERROR: column
"em.email_creation_datetime"must appear in the GROUP BY clause or be used in an aggregate function</font></span><p
align="LEFT"><spanlang="en-us"><font face="Arial" size="2">CONTEXT: SQL statement " select
to_char(to_timestamp(EXTRACT(HOURFROM em.email_creation_datetime) || ':' || (EXTRACT(MINUTE FROM
em.email_creation_datetime)::integer/$1 ) * $2 , 'HH24:MI'), 'HH24:MI') as TheInterval from test em group by
to_char(to_timestamp(EXTRACT(HOURFROM em.email_creation_datetime) || ':' || (EXTRACT(MINUTE FROM
em.email_creation_datetime)::integer/$3 ) * $4 , 'HH24:MI') , 'HH24:MI')"</font></span><p align="LEFT"><span
lang="en-us"><fontface="Arial" size="2">PL/pgSQL function "emailbyinterval" line 3 at for over select
rows</font></span><spanlang="en-us"></span><span lang="en-us"></span>
"Davidson, Robert" <robdavid@amazon.com> writes:
> ERROR: column "em.email_creation_datetime" must appear in the GROUP BY =
> clause or be used in an aggregate function
> CONTEXT: SQL statement " select to_char(to_timestamp(EXTRACT(HOUR FROM =
> em.email_creation_datetime) || ':' || (EXTRACT(MINUTE FROM =
> em.email_creation_datetime)::integer/ $1 ) * $2 , 'HH24:MI'), =
> 'HH24:MI') as TheInterval from test em group by =
> to_char(to_timestamp(EXTRACT(HOUR FROM em.email_creation_datetime) || =
> ':' || (EXTRACT(MINUTE FROM em.email_creation_datetime)::integer/ $3 ) * =
> $4 , 'HH24:MI') , 'HH24:MI')"
> PL/pgSQL function "emailbyinterval" line 3 at for over select rows
Hmm, this seems like a plpgsql deficiency. It feels it can generate a
separate parameter symbol ($n) for each occurrence of each variable it
passes into a SQL query. But for this query to be legal, the two
instances of IntervalMinutes have to be represented by the *same*
parameter symbol (notice they are not in the regurgitated query).
It would be more efficient anyway to not generate multiple parameters
for the same value, so we oughta fix this.
In the short run, the only workaround I can think of for you is to run
the query using EXECUTE.
regards, tom lane
Just Put aggregate function to the fields you selected.
Like this:
select to_char(to_timestamp(EXTRACT(HOUR FROM max(em.email_creation_datetime)) || ':' || (EXTRACT(MINUTE FROM max(em.email_creation_datetime))::integer/30) * 30, 'HH24:MI'), 'HH24:MI') as TheInterval
from test em
group by to_char(to_timestamp(EXTRACT(HOUR FROM em.email_creation_datetime) || ':' || (EXTRACT(MINUTE FROM em.email_creation_datetime)::integer/30) * 30, 'HH24:MI') , 'HH24:MI')
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Davidson, Robert
Sent: Wednesday, March 22, 2006 1:12 PM
To: pgsql-sql@postgresql.org
Subject: [SQL] Function Parameters in GROUP BY clause cause errors
When I use a parameter in a query which aggregates it fails with a GROUP BY error. What syntax can I use to avoid this error?
CREATE TABLE test (email_creation_datetime timestamp);
INSERT INTO test VALUES ('2006-03-20 09:00');
INSERT INTO test VALUES ('2006-03-20 09:15');
INSERT INTO test VALUES ('2006-03-20 09:30');
INSERT INTO test VALUES ('2006-03-20 09:45');
Query without parameters works fine:
select to_char(to_timestamp(EXTRACT(HOUR FROM em.email_creation_datetime) || ':' || (EXTRACT(MINUTE FROM em.email_creation_datetime)::integer/30) * 30, 'HH24:MI'), 'HH24:MI') as TheInterval
from test em
group by to_char(to_timestamp(EXTRACT(HOUR FROM em.email_creation_datetime) || ':' || (EXTRACT(MINUTE FROM em.email_creation_datetime)::integer/30) * 30, 'HH24:MI') , 'HH24:MI')
theinterval
09:30
09:00
But the same query with a parameter returns a GROUP BY error:
CREATE OR REPLACE FUNCTION EmailByInterval(IntervalMinutes int) RETURNS SETOF test AS $$
DECLARE rec RECORD;
BEGIN
FOR rec IN
select to_char(to_timestamp(EXTRACT(HOUR FROM em.email_creation_datetime) || ':' || (EXTRACT(MINUTE FROM em.email_creation_datetime)::integer/IntervalMinutes) * IntervalMinutes, 'HH24:MI'), 'HH24:MI') as TheInterval
from test em
group by to_char(to_timestamp(EXTRACT(HOUR FROM em.email_creation_datetime) || ':' || (EXTRACT(MINUTE FROM em.email_creation_datetime)::integer/IntervalMinutes) * IntervalMinutes, 'HH24:MI') , 'HH24:MI')
LOOP
RETURN NEXT rec;
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql;
Query returned successfully with no result in 70 ms.
select * from emailbyinterval(30);
ERROR: column "em.email_creation_datetime" must appear in the GROUP BY clause or be used in an aggregate function
CONTEXT: SQL statement " select to_char(to_timestamp(EXTRACT(HOUR FROM em.email_creation_datetime) || ':' || (EXTRACT(MINUTE FROM em.email_creation_datetime)::integer/ $1 ) * $2 , 'HH24:MI'), 'HH24:MI') as TheInterval from test em group by to_char(to_timestamp(EXTRACT(HOUR FROM em.email_creation_datetime) || ':' || (EXTRACT(MINUTE FROM em.email_creation_datetime)::integer/ $3 ) * $4 , 'HH24:MI') , 'HH24:MI')"
PL/pgSQL function "emailbyinterval" line 3 at for over select rows
I choose Polesoft Lockspam to fight spam, and you?
http://www.polesoft.com/refer.html
I wrote:
> Hmm, this seems like a plpgsql deficiency. It feels it can generate a
> separate parameter symbol ($n) for each occurrence of each variable it
> passes into a SQL query. But for this query to be legal, the two
> instances of IntervalMinutes have to be represented by the *same*
> parameter symbol (notice they are not in the regurgitated query).
> It would be more efficient anyway to not generate multiple parameters
> for the same value, so we oughta fix this.
Patch applied to HEAD and 8.1 branches.
regards, tom lane
How amazing is that? I call it a night and come back to find that a bug has been identified and patched while I sleep.
When will it appear in the binaries (I see that the release version is still 8.1.3)? I thought about trying to compile
fromsource, but after looking at the mingw page was too intimidated by figuring out what to download to try compiling
source.
Best Regards,
Robert Davidson
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Wednesday, March 22, 2006 8:27 PM
To: Davidson, Robert
Cc: pgsql-sql@postgresql.org; pgsql-bugs@postgresql.org
Subject: Re: [SQL] Function Parameters in GROUP BY clause cause errors
I wrote:
> Hmm, this seems like a plpgsql deficiency. It feels it can generate a
> separate parameter symbol ($n) for each occurrence of each variable it
> passes into a SQL query. But for this query to be legal, the two
> instances of IntervalMinutes have to be represented by the *same*
> parameter symbol (notice they are not in the regurgitated query).
> It would be more efficient anyway to not generate multiple parameters
> for the same value, so we oughta fix this.
Patch applied to HEAD and 8.1 branches.
regards, tom lane