[Dbix-class] Bind parameters in select list and group by on Pg

[Peter Rabbitson]

On Thu, Oct 27, 2011 at 06:11:29PM -0700, dorian taylor wrote:
> Hey all,
> 
> I'm trying to do this:
> 
> # this doesn't work with either $1 or ? formulation
> # my $col = [q{extract(year from (period + ?::interval))}, [ foo => '6
> months']];
> 
> # this does when passed a bind parameter in the attributes
> my $col = 'extract(year from period + $1::interval)';
> 
> my $rs = $db->resultset('Employee::Job')->search(
>     {},
>     {
>         select   => [\$col, { avg => 'wages_earned' }],
>         as       => ['year', 'average_wage'],
>         group_by => [\$col],
>         # use this in conjunction with the scalar ref
>         bind     => [[asdf => '6 months']],
>         order_by => 1,
>     }
> );
> 
> That is, I want to aggregate by year, offset by an arbitrary month
> (e.g July to July). Now, the problem is, when I use a question mark
> placeholder and the arrayref-ref formulation, the driver appears to
> consider the expression in the group-by clause different from the one
> in the select list, yielding this error:
> 
> DBI Exception: DBD::Pg::st execute failed: ERROR:  column "me.period"
> must appear in the GROUP BY clause or be used in an aggregate function

Where is the DBIC_TRACE of the generated SQL that lead to this error?

> (also worth noting that the expression "interval ?" yields a syntax
> error in pg 8.4.)

That... would be you using illegal syntax :)

> I've found that I can work around it by adding a bind list to the
> search attributes and if I use the $N-style identifiers, but Postgres
> of course won't let me mix those with question marks, meaning I'm
> screwed for any other placeholders that get generated from
> DBIx::Class.
> 
> Unless, is there a way to tell it to use that style of placeholder instead?

No, because the individual bits of SQL are generated independently from each
other, so there would be no sane way to number which placeholder is 1 which
is 2 and so on.

Please provide the generated SQL so we can help further.

Cheers