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 : > > r...@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 > r...@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/[email protected] _______________________________________________ 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]
