Re: Function Parameters in GROUP BY clause cause errors - Mailing list pgsql-sql

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')

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