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

[dorian taylor]

On Fri, Oct 28, 2011 at 12:48 AM, Peter Rabbitson <rabbit+dbic at rabbit.us> wrote:
> 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?

array-ref-ref question mark formulation:

SELECT extract(year from (period + ?::interval)), COUNT( employee_id )
FROM employee_job me GROUP BY extract(year from (period +
?::interval)) ORDER BY 1: '6 months', '6 months'
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
LINE 1: SELECT extract(year from (period + $1::interval)), COUNT( em...
                                  ^ [for Statement "SELECT
extract(year from (period + ?::interval)), COUNT( employee_id ) FROM
employee_job me GROUP BY extract(year from (period + ?::interval))
ORDER BY 1" with ParamValues: 1='6 months', 2='6 months'] at
/usr/local/share/perl/5.10.1/DBIx/Class/Schema.pm line 1078

scalar ref $1 formulation:

SELECT extract(year from period + $1::interval), COUNT( employee_id )
FROM employee_job me GROUP BY extract(year from period + $1::interval)
ORDER BY 1: '6 months'

(ok)

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

Interesting, because the literal would be "interval '6 months'", which
unless I'm huffing paint would translate to "interval ?". :)

>> 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.

I understand that. I guess PG doesn't do named placeholders à la
Oracle, does it?

Thanks,


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