Thread: calling functions in select

hi,

i'd like to ask if these two pl/pgsql fragmets are always giving same
results:

1)
FOR lr_rec IN SELECT func(table.x, ...) FROM table,... WHERE ... LOOP
END LOOP;

2)
FOR lr_rec IN SELECT * FROM table,... WHERE ... LOOP
    li_x := func(lr_rec.x, ...);
END LOOP;

function func has some "side effects", e.g. inserting rows somewhere...

i think that 1) and 2) should be the same but i'd like to make sure..

thanks,

kuba

> 1)
> FOR lr_rec IN SELECT func(table.x, ...) FROM table,... WHERE ... LOOP
> END LOOP;
>
> 2)
> FOR lr_rec IN SELECT * FROM table,... WHERE ... LOOP
>     li_x := func(lr_rec.x, ...);
> END LOOP;

i'll answer myself, it depends on whether func is iscachable or not,
right?

kuba

Jakub Ouhrabka <jouh8664@ss1000.ms.mff.cuni.cz> writes:
>> 1)
>> FOR lr_rec IN SELECT func(table.x, ...) FROM table,... WHERE ... LOOP
>> END LOOP;
>>
>> 2)
>> FOR lr_rec IN SELECT * FROM table,... WHERE ... LOOP
>> li_x := func(lr_rec.x, ...);
>> END LOOP;

> i'll answer myself, it depends on whether func is iscachable or not,
> right?

No, I don't believe so.  These should produce the same result except
possibly for the time at which func() is executed relative to what else
you might be doing inside that FOR loop.  plpgsql's FOR reads multiple
rows from the SELECT at a time, so if func() is in the SELECT then it
may get evaluated several rows ahead of where your FOR loop thinks it
is.  But in your case 2, func() is guaranteed to be executed in lockstep
with other actions taken in the FOR-loop body.

            regards, tom lane