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

[Gavin Henry]

<quote who="Dan Dascalescu">
>> As for SPs, we don't support ones that return multiple values/rows yet.
>> You could set the resultsource to a select that retrieves from such an
>> SP:
>>
>> __PACKAGE__->table('dummy')
>> __PACKAGE__->result_source_instance->name(\'(select * from
>> boardslist)');
>
> How would one access result sets returned by a Stored Procedure that
> takes parameters?
>
> Something like:
>
> SELECT me.name, me.salary FROM top_employees_by_some_criteria(?, ?) me
>
> ?

Well, what we do, if you actually return a row with columns, is the usual
->add_column/s etc.

but most of ours are like:

SELECT * FROM top_employees_by_some_criteria(?, ?)

i.e.

__PACKAGE__->result_source_instance->name( \'blah(?,?)' );

and then:

my $query = { bind => [ $blah1, $blah2 ]};
my $result = $cfg->{dbic_class}->search( {}, $query )->first;

etc.

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?

hanks,

Gavin.