【原创】PostgreSQL 实现阶乘方法列举

PostgreSQL 功能庞大，对实现乘法这类运算有诸多的方法，今天我来简单列举下以下几种便捷的途径。
比如我们要计算10！


1、 可以用SQL给它展开：

点击(此处)折叠或打开

1. t_girl=# select 1*2*3*4*5*6*7*8*9*10 as multiply_10;
   
2.  multiply_10
   
3. ------------
   
4.     3628800
   
5. (1 row)
   
6. 
   
7. 
   
8. Time: 0.854 ms

2、 用WITH递归

点击(此处)折叠或打开

1. t_girl=# with recursive g(m,n) as
   
2. t_girl-# (select 1 m, 10 n
   
3. t_girl(# union all
   
4. t_girl(# select m*n, (n-1) n from g where n > 1
   
5. t_girl(# )
   
6. t_girl-# select max(m) as factorial_10 from g;
   
7.  factorial_10
   
8. --------------
   
9.       3628800
   
10. (1 row)
    
11. 
    
12. 
    
13. Time: 3.893 ms

3、 用简单的函数来展开

点击(此处)折叠或打开

1. create or replace function func_get_factorial(
   
2. f_number int
   
3. ) returns bigint
   
4. as
   
5. $ytt$
   
6.   declare i int :=1;
   
7.   declare v_result bigint := 1;
   
8. begin
   
9.   for i in 1 .. f_number loop
   
10.     v_result := v_result * i;
    
11.   end loop;
    
12.   return v_result;
    
13. end;
    
14. $ytt$
    
15. language plpgsql;
    
16. 
    
17. 
    
18. t_girl=# select func_get_factorial(10) as factorial_10;
    
19.  factorial_10
    
20. --------------
    
21.       3628800
    
22. (1 row)
    
23. 
    
24. 
    
25. Time: 1.022 ms

4、 用游标和序列函数generate_series来展开

点击(此处)折叠或打开

1. create or replace function func_get_factorial2(
   
2. f_number int
   
3. ) returns bigint
   
4. as
   
5. $ytt$
   
6.   declare cs1 cursor for select n from generate_series(1,f_number,1) as g(n);
   
7.   declare v_result bigint := 1;
   
8.   declare v_n bigint := 0;
   
9. begin
   
10.     open cs1;
    
11.     loop
    
12.         fetch cs1 into v_n;
    
13.         exit when not found;
    
14. v_result := v_result * v_n;
    
15.     end loop;
    
16.     close cs1;
    
17.   return v_result;
    
18. end;
    
19. $ytt$
    
20. language plpgsql;
    
21. 
    
22. 
    
23. t_girl=# select func_get_factorial2(10) factorial_10;
    
24.  factorial_10
    
25. --------------
    
26.       3628800
    
27. (1 row)
    
28. 
    
29. 
    
30. Time: 2.238 ms
    
31. t_girl=#

5、 用自定义python函数

点击(此处)折叠或打开

1. create or replace function func_get_factorial_py(
   
2. f_number int
   
3. ) returns bigint
   
4. as
   
5. $ytt$
   
6. m = 1
   
7. n = 1
   
8. for i in range(1,f_number+1):
   
9.     m = m * i
   
10. n = m
    
11. return n
    
12. $ytt$
    
13. language plpythonu;
    
14. 
    
15. 
    
16. t_girl=# select func_get_factorial_py(10) factorial_10;
    
17.  factorial_10
    
18. --------------
    
19.       3628800
    
20. (1 row)
    
21. 
    
22. 
    
23. Time: 1.060 ms