From: Tom Lane <tgl@sss.pgh.pa.us>
To: Davidson, Robert <robdavid@amazon.com>
Cc: pgsql-sql@postgresql.org
Cc: pgsql-bugs@postgresql.org
Subject: Re: [SQL] Function Parameters in GROUP BY clause cause errors
Date: Wed, 22 Mar 2006 16:53:48 -0500
Message-ID: <20117.1143064428@sss.pgh.pa.us> (raw)
In-Reply-To: <8333C841129E074E9F83FC80676BA76E0BF08D@exchg-sea3-03.ant.amazon.com>
References: <8333C841129E074E9F83FC80676BA76E0BF08D@exchg-sea3-03.ant.amazon.com>
"Davidson, Robert" <robdavid@amazon.com> 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
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: tgl@sss.pgh.pa.us, robdavid@amazon.com, pgsql-bugs@postgresql.org
Subject: Re: [SQL] Function Parameters in GROUP BY clause cause errors
In-Reply-To: <20117.1143064428@sss.pgh.pa.us>
* 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