X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 2CB829DC87E for ; Wed, 22 Mar 2006 21:24:30 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 97177-08 for ; Wed, 22 Mar 2006 21:24:32 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from mailer.eglobalreach.net (mail.opm.ph [202.124.128.22]) by postgresql.org (Postfix) with ESMTP id 1E2059DC8A0 for ; Wed, 22 Mar 2006 21:24:25 -0400 (AST) Received: from ghwk02002147 (mailer.eglobalreach.net [127.0.0.1]) by mailer.eglobalreach.net (8.11.6p2/) with ESMTP id k2N1OR326658; Thu, 23 Mar 2006 09:24:27 +0800 From: "Christian Paul B. Cosinas" To: "'Davidson, Robert'" , 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> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_022D_01C64DDF.209613F0" X-Mailer: Microsoft Office Outlook 11 In-Reply-To: <8333C841129E074E9F83FC80676BA76E0BF08D@exchg-sea3-03.ant.amazon.com> X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1506 thread-index: AcZN7Ntkwk+F/zXDTbKBIRGOkotEUQALLwwQ X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.499 required=5 tests=[AWL=0.498, HTML_MESSAGE=0.001, UPPERCASE_25_50=0] X-Spam-Score: 0.499 X-Spam-Level: X-Archive-Number: 200603/373 X-Sequence-Number: 24719 This is a multi-part message in MIME format. ------=_NextPart_000_022D_01C64DDF.209613F0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 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 ------=_NextPart_000_022D_01C64DDF.209613F0 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable 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_creat= ion_datetime)) || ':' || (EXTRACT(MINUTE FROM max(em.email_creation_datetime))::int= eger/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 ------=_NextPart_000_022D_01C64DDF.209613F0--