Function Parameters in GROUP BY clause cause errors

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

Responses

Browse pgsql-bugs by date

  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

Browse pgsql-sql by date

  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?