X-Original-To: pgsql-bugs-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id B269A9DC83C; Wed, 22 Mar 2006 17:53:55 -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 70320-04; Wed, 22 Mar 2006 17:53:55 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id F1F219DD62D; Wed, 22 Mar 2006 17:53:50 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id k2MLrmZo020118; Wed, 22 Mar 2006 16:53:48 -0500 (EST) To: "Davidson, Robert" cc: pgsql-sql@postgresql.org, pgsql-bugs@postgresql.org Subject: Re: [SQL] Function Parameters in GROUP BY clause cause errors In-reply-to: <8333C841129E074E9F83FC80676BA76E0BF08D@exchg-sea3-03.ant.amazon.com> References: <8333C841129E074E9F83FC80676BA76E0BF08D@exchg-sea3-03.ant.amazon.com> Comments: In-reply-to "Davidson, Robert" message dated "Wed, 22 Mar 2006 12:11:51 -0800" Date: Wed, 22 Mar 2006 16:53:48 -0500 Message-ID: <20117.1143064428@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.114 required=5 tests=[AWL=0.114] X-Spam-Score: 0.114 X-Spam-Level: X-Archive-Number: 200603/187 X-Sequence-Number: 14673 "Davidson, Robert" writes: > 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 Hmm, this seems like a plpgsql deficiency. It feels it can generate a separate parameter symbol ($n) for each occurrence of each variable it passes into a SQL query. But for this query to be legal, the two instances of IntervalMinutes have to be represented by the *same* parameter symbol (notice they are not in the regurgitated query). It would be more efficient anyway to not generate multiple parameters for the same value, so we oughta fix this. In the short run, the only workaround I can think of for you is to run the query using EXECUTE. regards, tom lane