Since someone on irc asked on why this was actually being called 28 times:
my $rs= $App->schema->resultset('PriceVersion')->search({});
print $rs->first->get_column('cnt'), "\n";
that first there doesn't limit the sql, it just gets the first row - the other
27 are there in the cursor. The SQL underneath is still:
SELECT ... FROM price_version;
It's not
SELECT ... FROM price_version LIMIT 1;
Think of the difference in these two statements:
SELECT 1 FROM table;
SELECT 1;
-ash
On 4 Oct 2010, at 13:25, Ash Berlin wrote:
> 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]
