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

[dorian taylor]

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

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

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?

Or any other ideas?

Thanks,

-- 
Dorian Taylor
http://doriantaylor.com/