From: | "Christian Paul B(dot) Cosinas" <cpc(at)cybees(dot)com> |
---|---|
To: | "'Davidson, Robert'" <robdavid(at)amazon(dot)com>, <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: Function Parameters in GROUP BY clause cause errors |
Date: | 2006-03-23 01:33:33 |
Message-ID: | 022c01c64e19$ccf36550$1e21100a@ghwk02002147 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs 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')
_____
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2006-03-23 04:27:11 | Re: [SQL] Function Parameters in GROUP BY clause cause errors |
Previous Message | Tom Lane | 2006-03-22 21:53:48 | Re: [SQL] Function Parameters in GROUP BY clause cause errors |
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2006-03-23 02:47:31 | Re: OUT parameter |
Previous Message | Owen Jacobson | 2006-03-22 22:39:13 | Re: OUT parameter |