[Dbix-class] Strange beaviour with bind_params for function call

[Ash Berlin]

If you want the function to be called once and don't want to use ->dbh->do the other option is to create a resultset for this. See:

  http://search.cpan.org/~frew/DBIx-Class-0.08123/lib/DBIx/Class/Manual/Cookbook.pod#Arbitrary_SQL_through_a_custom_ResultSource
  http://search.cpan.org/~frew/DBIx-Class-0.08123/lib/DBIx/Class/ResultSource/View.pm

-ash

On 4 Oct 2010, at 12:55, Rolf Schaufelberger wrote:
> Hi, 
> I want to call a stored function  in Postgresql with several bind_parameters:
> 
> The function definition looks like:
> 
> create or replace function system.test_prices ( commentval varchar(255), delta_eigen float, delta_material float, set_active integer, paper_ids integer[] ) returns integer AS
> $BODY$
> DECLARE
> 
> BEGIN
> raise notice 'ARGS : Comment %, delta_e %, delta_m %, act %', commentval, delta_eigen, delta_material, set_active;
> insert into xx (val) values (delta_eigen) ;
> return 123;
> END;
> $BODY$
> LANGUAGE plpgsql;
> 
> 
> The functions loggs the args to console and inserts some debug value  in a table;
> The special thing here is, that the last arg is an Array of Integer (which is why I have to call with bind params), yet I believe that is not the problem, since calling a function  with one arg of type integer  array works fine.
> 
> My code for calling the function is:
> ...
> my $ids = [6024];  #the integer array
> my $rs= $App->schema->resultset('PriceVersion')->search({},
>                { select => {'test_prices' => \[ "?, ?, ?, ?, ?::integer[]", 
> 	                                                                       [comment=> 'MyComment'],
>                                                                               [delta_eigen =>5.0], 
>                                                                               [delta_material => 10.0],
>                                                                                [active=>0],
>                                                                                [paper =>$ids]]},
>                  as     => 'cnt'});
> print $rs->first->get_column('cnt'), "\n";
> 
> Now,  running it gives me :
> 
> ~/perl/xx.pl 
> HINWEIS:  ARGS : Comment MyComment, delta_e 5, delta_m 10, act 0
> HINWEIS:  ARGS : Comment MyComment, delta_e 5, delta_m 10, act 0
> HINWEIS:  ARGS : Comment MyComment, delta_e 5, delta_m 10, act 0
> HINWEIS:  ARGS : Comment MyComment, delta_e 5, delta_m 10, act 0
> HINWEIS:  ARGS : Comment MyComment, delta_e 5, delta_m 10, act 0
> HINWEIS:  ARGS : Comment MyComment, delta_e 5, delta_m 10, act 0
> HINWEIS:  ARGS : Comment MyComment, delta_e 5, delta_m 10, act 0
> HINWEIS:  ARGS : Comment MyComment, delta_e 5, delta_m 10, act 0
> HINWEIS:  ARGS : Comment MyComment, delta_e 5, delta_m 10, act 0
> HINWEIS:  ARGS : Comment MyComment, delta_e 5, delta_m 10, act 0
> HINWEIS:  ARGS : Comment MyComment, delta_e 5, delta_m 10, act 0
> HINWEIS:  ARGS : Comment MyComment, delta_e 5, delta_m 10, act 0
> HINWEIS:  ARGS : Comment MyComment, delta_e 5, delta_m 10, act 0
> HINWEIS:  ARGS : Comment MyComment, delta_e 5, delta_m 10, act 0
> HINWEIS:  ARGS : Comment MyComment, delta_e 5, delta_m 10, act 0
> HINWEIS:  ARGS : Comment MyComment, delta_e 5, delta_m 10, act 0
> HINWEIS:  ARGS : Comment MyComment, delta_e 5, delta_m 10, act 0
> HINWEIS:  ARGS : Comment MyComment, delta_e 5, delta_m 10, act 0
> HINWEIS:  ARGS : Comment MyComment, delta_e 5, delta_m 10, act 0
> HINWEIS:  ARGS : Comment MyComment, delta_e 5, delta_m 10, act 0
> HINWEIS:  ARGS : Comment MyComment, delta_e 5, delta_m 10, act 0
> HINWEIS:  ARGS : Comment MyComment, delta_e 5, delta_m 10, act 0
> HINWEIS:  ARGS : Comment MyComment, delta_e 5, delta_m 10, act 0
> HINWEIS:  ARGS : Comment MyComment, delta_e 5, delta_m 10, act 0
> HINWEIS:  ARGS : Comment MyComment, delta_e 5, delta_m 10, act 0
> HINWEIS:  ARGS : Comment MyComment, delta_e 5, delta_m 10, act 0
> HINWEIS:  ARGS : Comment MyComment, delta_e 5, delta_m 10, act 0
> HINWEIS:  ARGS : Comment MyComment, delta_e 5, delta_m 10, act 0
> 123
> 
> The function is called 28 times !!
> 
> Changing the "raise notice" to "raise exception" gives :
> 
> rs at ubuntu:	~/perl/xx.pl DBIx::Class::ResultSet::first(): DBI Exception: DBD::Pg::st execute failed: FEHLER: ARGS : Comment MyComment, delta_e 5, delta_m 10, act 0 [for Statement "SELECT TEST_PRICES( ?, ?, ?, ?, ?::integer[] ) FROM price_version me" with ParamValues: 1='MyComment', 2='5', 3='10', 4='0', 5='{"6024"}'] at /home/rs/perl/xx.pl line 37 
> rs at ubuntu:
> 
> That looks quite perfect, so the syntax for binding seems to be ok. But why is the function called 28 times ??
> DBIx::Class is 0.08121
> 
> 
> Rolf Schaufelberger
> 
> 
> 
> 
> 
> 
> _______________________________________________
> 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/dbix-class@lists.scsys.co.uk