oracle里面有比较成熟的内置行转列函数，postgres也有这么一个应用，名称就是 tablefunc. pg的这个应用在安装的时候默认是不安装
oracle里面有比较成熟的内置行转列函数，postgres也有这么一个应用，名称就是 tablefunc.
pg的这个应用在安装的时候默认是不安装的，如果已经有安装，则在$pghome/share/extension/路径下会有tablefunc*的三个文件。分别是：
[postgres @localhost extension]$ pwd
/home/postgres/share/extension
[postgres@localhost extension]$ ll tablefunc*
-rw-r--r-- 1 postgres postgres 2153 04-19 15:27 tablefunc--1.0.sql
-rw-r--r-- 1 postgres postgres 174 04-19 15:27 tablefunc.control
-rw-r--r-- 1 postgres postgres 1144 04-19 15:27 tablefunc--unpackaged--1.0.sql
没有的话则需要去pg的安装包里去编译一下。 下面主要介绍下crosstab这个函数的应用
1.编译安装tablefunc
# cd ~/setupfile/postgresql-9.1.2/contrib/tablefunc/
[postgres @greentea tablefunc]$ make
gcc -o2 -wall -wmissing-prototypes -wpointer-arith -wdeclaration-after-statement -wendif-labels -wformat-security -fno-strict-aliasing -fwrapv -fpic -i. -i. -i../../src/include -d_gnu_source -i/usr/include/libxml2 -c -o tablefunc.o tablefunc.c
gcc -o2 -wall -wmissing-prototypes -wpointer-arith -wdeclaration-after-statement -wendif-labels -wformat-security -fno-strict-aliasing -fwrapv -fpic -l../../src/port -l/usr/lib -wl,-rpath,'/home/postgres/lib',--enable-new-dtags -lm -shared -o tablefunc.so tablefunc.o
rm tablefunc.o
[postgres @greentea tablefunc]$ make install
/bin/mkdir -p '/home/postgres/share/extension'
/bin/mkdir -p '/home/postgres/lib'
/bin/sh ../../config/install-sh -c -m 644 ./tablefunc.control '/home/postgres/share/extension/'
/bin/sh ../../config/install-sh -c -m 644 ./tablefunc--1.0.sql ./tablefunc--unpackaged--1.0.sql '/home/postgres/share/extension/'
/bin/sh ../../config/install-sh -c -m 755 tablefunc.so '/home/postgres/lib/'
2.创建tablefunc
安装完成后$pghome/share/extension/就会出现开头提到的那三个文件，，此时需要在数据库里创建一下extension
较早的如8.*版本的创建的语法是psql -f tablefunc.sql 即可，9.*以后版本不行，使用的语法如下：
[postgres @greentea extension]$ psql -u postgres -d test
psql (9.1.2)
type help for help.
test=# create extension tablefunc;
create extension
test=#
此时客户端工具上也能出现一个新的拓展：
另外在public下面会建立11个函数
3.测试
测试数据：
create table t(day date,equipment varchar(20),output integer);
insert into t values('2010-04-01','dat501',100);
insert into t values('2010-04-01','dat502',120);
insert into t values('2010-04-01','dat503',130);
insert into t values('2010-04-02','dat501',110);
insert into t values('2010-04-02','dat502',105);
test=# select * from t;
day | equipment | output
------------+-----------+--------
2010-04-01 | dat501 | 100
2010-04-01 | dat502 | 120
2010-04-02 | dat501 | 110
2010-04-02 | dat502 | 105
2010-04-01 | dat503 | 130
(5 rows)
test=# select * from crosstab('select day, equipment, output from t order by 1,2') as t(day date, dat501 integer, dat502 integer,dat503 integer);
day | dat501 | dat502 | dat503
------------+--------+--------+--------
2010-04-01 | 100 | 120 | 130
2010-04-02 | 110 | 105 |
(2 rows)
4.总结
这个应用其实在统计月份、季度等带时间性质的场合下比较不错，有一个前提是后面组合的dat501,dat502,dat503是已知的，也就是说提前知道我们会分成几列。这也是一个不足，就是不能动态地生成列。
相关阅读：
postgresql 9.3物化视图使用
使用 postgresql 数据库日期类型的 4 个提示
postgresql删除表中重复数据行
postgresql缓存详述
windows平台编译 postgresql
ubuntu下lapp（linux+apache+postgresql+php)环境的配置与安装