[Dbix-class] how to execute stored procedures in DBIx::Class

[Dan Dascalescu]

On 2/14/07, Gavin Henry <ghenry at perl.me.uk> wrote:
> But I'm now stuck as this is like:
>
>
> SELECT * FROM top_employees_by_some_criteria('one', 'two');
>
> which in Postgres via psql would give:
>
> top_employees_by_some_criteria
> -------
>      myresult
> (1 row)
>
>
> (you can put your usual AS in above).
>
>
> I've yet to find our how to access this result. Using DBI and
> fetch_hashref gives me a hash key with the name
> 'top_employees_by_some_criteria'.
>
> How can I get this with dbic?

My top_employees_by_some_criteria SP returns a result that's defined like this:
CREATE TYPE top_employees_result AS
   (field1 text,
    field2 integer);

What I use now is:

package MyApp::Schema::Employees;
# Created manually
[...]
__PACKAGE__->load_components("Core");
__PACKAGE__->table("employees_but_apparently_not_used_anywhere");
__PACKAGE__->result_source_instance->name(\'(select * from
top_employees_by_some_criteria(?, ?))');

__PACKAGE__->add_columns(
  "field1",
  { data_type => "text", size => undef, },
  "field2",
  { data_type => "integer", size => 4, },
);



In a controller action I have something like:

my $items_rs = $c->model('MyAppSchema::Employees')->search(
    undef, {
        bind => [$param1, $param2],
    }
);

Then,

while (my $item = $items_rs->next) {
    push @matches, {
        field1 => $item->field1,
        field2 => $item->field2,
    };
}

Hope that helps,
Dan