From: | "Davidson, Robert" <robdavid(at)amazon(dot)com> |
---|---|
To: | <pgsql-sql(at)postgresql(dot)org> |
Subject: | Function Parameters in GROUP BY clause cause errors |
Date: | 2006-03-22 20:11:51 |
Message-ID: | 8333C841129E074E9F83FC80676BA76E0BF08D@exchg-sea3-03.ant.amazon.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2006-03-22 21:53:48 | Re: [SQL] Function Parameters in GROUP BY clause cause errors |
Previous Message | Tom Lane | 2006-03-22 17:49:57 | Re: BUG #2352: Bug with Pl/Python |
From | Date | Subject | |
---|---|---|---|
Next Message | Daniel Caune | 2006-03-22 20:25:19 | Custom type |
Previous Message | Maciej Piekielniak | 2006-03-22 20:01:34 | Re: How to optimize this query? |