On Fri, Oct 28, 2011 at 03:37:17AM -0700, dorian taylor wrote: > On Fri, Oct 28, 2011 at 12:48 AM, Peter Rabbitson <[email protected]> > 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) >
Could it be that Pg is so stupid as to trip on the parenthesizing difference of your statements: SELECT extract(year from period + $1::interval), ... vs SELECT extract(year from (period + ?::interval)), > > 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 ?". :) Pg has a very weird way of looking at this. I do not use Pg myself, just various memories from people with complaints *very* similar to yours. > I understand that. I guess PG doesn't do named placeholders à la > Oracle, does it? Not that I know of no. And in reality positionality was never really necessary. Let's solve your problem with positionless ?'s, there is absolutely no reason for things not to work like that :) _______________________________________________ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/[email protected]
