Re: [HACKERS] Updating column on row update

>>>>> "Tom" == Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

>> Loops in plain SQL are no problem: see generate_series. The last
>> time we discussed this I demonstrated reasonably straightforward
>> SQL examples of how to do things like password-cracking (and that
>> was long before we had CTEs, so it would be even easier now); my
>> challenge to anyone to produce examples of malicious plpgsql code
>> that couldn't be reproduced in plain SQL went unanswered.

Tom> The fact remains though that the looping performance of anything
Tom> you can cons up in straight SQL will be an order of magnitude
Tom> worse than in plpgsql;

Well, let's see. How about generating all possible strings of 6 characters
from A-Z? We'll just count the results for now:

select count(chr(65+(i/676))||chr(65+(i/26)%26)||chr(65+i%26)
||chr(65+(j/676))||chr(65+(j/26)%26)||chr(65+j%26))
from generate_series(0,17575) i, generate_series(0,17575) j;
count
-----------
308915776
(1 row)

Time: 462570.563 ms

create function foo() returns bigint language plpgsql
as $f$
declare
c bigint := 0;
s text;
begin
for i in 0..17575 loop
for j in 0..17575 loop
s := chr(65+(i/676))||chr(65+(i/26)%26)||chr(65+i%26)
||chr(65+(j/676))||chr(65+(j/26)%26)||chr(65+j%26);
c := c + 1;
end loop;
end loop;
return c;
end;
$f$;

select foo();
foo
-----------
308915776
(1 row)

Time: 624809.671 ms

plpgsql comes out 35% _slower_, not "an order of magnitude worse".

--
Andrew.