[Dbix-class] Bind parameters in select list and group by on Pg
dorian taylor
dorian.taylor.lists at gmail.com
Fri Oct 28 10:37:17 GMT 2011
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/
More information about the DBIx-Class
mailing list