Re: Function Parameters in GROUP BY clause cause errors

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(at)postgresql(dot)org [mailto:pgsql-sql-owner(at)postgresql(dot)org]
On Behalf Of Davidson, Robert
Sent: Wednesday, March 22, 2006 1:12 PM
To: pgsql-sql(at)postgresql(dot)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