Re: [HACKERS] Updating column on row update

>>>>> "Tom" == Tom Lane <tgl@sss.pgh.pa.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.