[Dbix-class] PostgreSQL's date_part in recent versions of DBIx::Class

[Xavier Robin]

Hi,

I'm trying to upgrade my Catalyst application. It is currently running
fine in Perl 5.12.2 with DBIx::Class 0.08124. I'm trying to upgrade to
perl 5.14.2, with the latest DBIx::Class from CPAN, namely 0.08196.


Here is the problematic part of my Controller:

> 	$c->stash->{'all_years'} = $c->model("PloggixDB::Post")->search({is_blog => 1}, {
> 		select => [ "date_part('year', created)" ],
> 		distinct => 1,
> 		order_by => "date_part('year', created)",
> 	})->get_column("date_part('year', created)");

The 'created' column is a timestamp with time zone. The model definition
(selected extracts):

> package Ploggix::Schema::PloggixDB::Result::Post;
> __PACKAGE__->add_columns(
>  [...]
>   "created",
>   { data_type => "timestamp with time zone", is_nullable => 0 },


In Perl 5.12.2 with DBIx::Class 0.08124 it used to translate to:

> SELECT date_part('year', created) FROM posts me WHERE ( is_blog = ? ) GROUP BY date_part('year', created) ORDER BY date_part('year', created)

And I obtain a list with all the years when an entry was posted to the
blog. (I don't know why DISTINCT doesn't appear in the statement but in
the end it was in effect).

Now with Perl 5.14.2 with DBIx::Class 0.08196, the same code translates to:

> SELECT me.date_part('year', created) FROM posts me WHERE ( is_blog = ? ) GROUP BY me.date_part('year', created) ORDER BY date_part('year', created)

Note the additional "me." before date_part. I get the following error
message:

> 14/jan/2012 18:43:51 [ERROR] undef error - DBIx::Class::ResultSetColumn::all(): DBI Exception: DBD::Pg::st execute failed: ERREUR:  le schéma « me » n'existe pas [for Statement "SELECT me.date_part('year', created) FROM posts me WHERE ( is_blog = ? ) GROUP BY me.date_part('year', created) ORDER BY date_part('year', created)" with ParamValues: 1='1'] at /home/xavier/Ploggix/root/lib/site/side_menu_blog.tt2 line 20


I can't find how I should be doing this exactly... would you please give
me some advice? How would you get the list of years present in a
timestamp column?


Thanks,
Xavier