From: Christian Paul B. Cosinas <cpc@cybees.com>
To: 'Davidson, Robert' <robdavid@amazon.com>
To: pgsql-sql@postgresql.org
Subject: Re: Function Parameters in GROUP BY clause cause errors
Date: Wed, 22 Mar 2006 18:33:33 -0700
Message-ID: <022c01c64e19$ccf36550$1e21100a@ghwk02002147> (raw)
In-Reply-To: <8333C841129E074E9F83FC80676BA76E0BF08D@exchg-sea3-03.ant.amazon.com>
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@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
On Behalf Of Davidson, Robert
Sent: Wednesday, March 22, 2006 1:12 PM
To: pgsql-sql@postgresql.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: cpc@cybees.com, robdavid@amazon.com
Subject: Re: Function Parameters in GROUP BY clause cause errors
In-Reply-To: <022c01c64e19$ccf36550$1e21100a@ghwk02002147>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox