tablefunc

os: centos 7.4
db: postgresql 10.10

tablefunc 是 contrib 包自带的 extension，使用起来很方便。

yum install

查看安装后的extension文件

# ls -l /usr/pgsql-10/lib/*tab*
-rwxr-xr-x 1 root root 23904 Oct 27 20:11 /usr/pgsql-10/lib/tablefunc.so

# ls -l /usr/pgsql-10/share/extension/*tab*
-rw-r--r-- 1 root root 2153 Oct 27 20:11 /usr/pgsql-10/share/extension/tablefunc--1.0.sql
-rw-r--r-- 1 root root  174 Oct 27 20:11 /usr/pgsql-10/share/extension/tablefunc.control
-rw-r--r-- 1 root root 1160 Oct 27 20:11 /usr/pgsql-10/share/extension/tablefunc--unpackaged--1.0.sql

create extension tablefunc

# su - postgres
$ psql

postgres=# select * from pg_available_extensions where name like '%tab%';
   name    | default_version | installed_version |                          comment                           
-----------+-----------------+-------------------+------------------------------------------------------------
 tablefunc | 1.0             |                   | functions that manipulate whole tables, including crosstab
(1 row)

postgres=# 
postgres=# create extension tablefunc;
CREATE EXTENSION
postgres=# \dx 
                                   List of installed extensions
    Name    | Version |   Schema   |                         Description                          
------------+---------+------------+--------------------------------------------------------------
 plpgsql    | 1.0     | pg_catalog | PL/pgSQL procedural language
 tablefunc  | 1.0     | public     | functions that manipulate whole tables, including crosstab
(2 rows)

postgres=# \dx+ tablefunc
                Objects in extension "tablefunc"
                       Object description                        
-----------------------------------------------------------------
 function connectby(text,text,text,text,integer)
 function connectby(text,text,text,text,integer,text)
 function connectby(text,text,text,text,text,integer)
 function connectby(text,text,text,text,text,integer,text)
 function crosstab2(text)
 function crosstab3(text)
 function crosstab4(text)
 function crosstab(text)
 function crosstab(text,integer)
 function crosstab(text,text)
 function normal_rand(integer,double precision,double precision)
 type tablefunc_crosstab_2
 type tablefunc_crosstab_3
 type tablefunc_crosstab_4
(14 rows)

使用

产生 20 个值，它们具有均值 5 和标准偏差 3：

postgres=# \x
Expanded display is on.
postgres=# \df+ normal_rand
List of functions
-[ RECORD 1 ]-------+--------------------------------------------
Schema              | public
Name                | normal_rand
Result data type    | SETOF double precision
Argument data types | integer, double precision, double precision
Type                | normal
Volatility          | volatile
Parallel            | unsafe
Owner               | postgres
Security            | invoker
Access privileges   | 
Language            | c
Source code         | normal_rand
Description         | 

postgres=# \x
Expanded display is off.
postgres=# SELECT * FROM normal_rand(20, 5, 3);
    normal_rand    
-------------------
  6.93602458861168
   5.7346109750846
  5.69459121399656
  3.35000172207249
  4.51973208231801
  10.2408276915543
  11.4084577737714
 0.182340590385939
  4.11664485074568
  12.8781589747503
  5.85821413039125
  8.74108054446905
  6.79258267166443
  5.50441029668803
  7.10179528434301
  3.89585531265877
  10.0224865505728
  7.32934991503747
   7.3979442096089
  5.50332990697003
(20 rows)

更多详细使用，可以参考官方文档

参考:
http://postgres.cn/docs/10/tablefunc.html