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]
