[root@AY1206230659041b61356 /home/www/postgresql-9.3.2/contrib/tablefunc]$find / -name pg_config_ext.h
/root/hy/postgresql-9.3.2/src/include/pg_config_ext.h
[root@AY1206230659041b61356 /home/www/postgresql-9.3.2/contrib/tablefunc]$
[root@AY1206230659041b61356 /home/www/postgresql-9.3.2/contrib/tablefunc]$gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wformat-security -fno-strict-aliasing -fwrapv -fpic -I. -I. -I/root/hy/postgresql-9.3.2/src/include/ -D_GNU_SOURCE -I/usr/include/libxml2 -c -o tablefunc.o tablefunc.c
[root@AY1206230659041b61356 /home/www/postgresql-9.3.2/contrib/tablefunc]$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
/usr/bin/ld: skipping incompatible /usr/lib/libm.so when searching for -lm
/usr/bin/ld: skipping incompatible /usr/lib/libm.a when searching for -lm
/usr/bin/ld: skipping incompatible /usr/lib/libc.so when searching for -lc
/usr/bin/ld: skipping incompatible /usr/lib/libc.a when searching for -lc
[root@AY1206230659041b61356 /home/www/postgresql-9.3.2/contrib/tablefunc]$make install
Makefile:19: ../../src/Makefile.global: 没有那个文件或目录
Makefile:20: /contrib/contrib-global.mk: 没有那个文件或目录
make: *** 没有规则可以创建目标“/contrib/contrib-global.mk”。 停止。
[root@AY1206230659041b61356 /home/www/postgresql-9.3.2/contrib/tablefunc]$ll
总计 120
drwxrwxr-x 2 www www 71 12-03 05:09 data
drwxrwxr-x 2 www www 26 12-03 05:09 expected
-rw-r--r-- 1 www www 431 12-03 04:57 Makefile
drwxrwxr-x 2 www www 26 12-03 05:09 sql
-rw-r--r-- 1 www www 2153 12-03 04:57 tablefunc--1.0.sql
-rw-r--r-- 1 www www 43443 12-03 04:57 tablefunc.c
-rw-r--r-- 1 www www 174 12-03 04:57 tablefunc.control
-rw-r--r-- 1 www www 1674 12-03 04:57 tablefunc.h
-rw-r--r-- 1 root root 26728 04-13 16:22 tablefunc.o
-rwxr-xr-x 1 root root 24740 04-13 16:22 tablefunc.so
-rw-r--r-- 1 www www 1144 12-03 04:57 tablefunc--unpackaged--1.0.sql
[root@AY1206230659041b61356 /home/www/postgresql-9.3.2/contrib/tablefunc]$psql
psql (9.3.2)
Type "help" for help.
ilove=> create extension tablefunc;
ERROR: could not open extension control file "/data/pgsql/share/extension/tablefunc.control": 没有那个文件或目录
ilove=> \q
[root@AY1206230659041b61356 /home/www/postgresql-9.3.2/contrib/tablefunc]$more tablefunc.control
# tablefunc extension
comment = 'functions that manipulate whole tables,including crosstab'
default_version = '1.0'
module_pathname = '$libdir/tablefunc'
relocatable = true
[root@AY1206230659041b61356 /home/www/postgresql-9.3.2/contrib/tablefunc]$cp tablefunc.control /data/pgsql/share/extension/
ERROR: permission denied to create extension "tablefunc"
HINT: Must be superuser to create this extension.
[root@AY1206230659041b61356 /home/www/postgresql-9.3.2/contrib/tablefunc]$su www
[www@AY1206230659041b61356 ~/postgresql-9.3.2/contrib/tablefunc]$psql
ilove=>
[www@AY1206230659041b61356 ~/postgresql-9.3.2/contrib/tablefunc]$psql -uwww
psql：无效选项 -- u
Try "psql --help" for more information.
[www@AY1206230659041b61356 ~/postgresql-9.3.2/contrib/tablefunc]$psql -Uwww
ilove=# create extension tablefunc;
ERROR: could not stat file "/data/pgsql/share/extension/tablefunc--1.0.sql": 没有那个文件或目录
ilove=#
ilove=# \q
[www@AY1206230659041b61356 ~/postgresql-9.3.2/contrib/tablefunc]$cp tablefunc--1.0.sql /data/pgsql/share/extension/
[www@AY1206230659041b61356 ~/postgresql-9.3.2/contrib/tablefunc]$
ERROR: could not access file "$libdir/tablefunc": 没有那个文件或目录
[www@AY1206230659041b61356 ~/postgresql-9.3.2/contrib/tablefunc]$which psql
[www@AY1206230659041b61356 ~/postgresql-9.3.2/contrib/tablefunc]$cp tablefunc.so /data/pgsql/lib/
ascii_and_mic.so libecpg_compat.a libpgtypes.so.3 plpgsql.so utf8_and_gbk.so
cyrillic_and_mic.so libecpg_compat.so libpgtypes.so.3.4 plpython2.so utf8_and_iso8859_1.so
dict_snowball.so libecpg_compat.so.3 libpq.a utf8_and_ascii.so utf8_and_iso8859.so
euc2004_sjis2004.so libecpg_compat.so.3.5 libpq.so utf8_and_big5.so utf8_and_johab.so
euc_cn_and_mic.so libecpg.so libpq.so.5 utf8_and_cyrillic.so utf8_and_sjis2004.so
euc_jp_and_sjis.so libecpg.so.6 libpq.so.5.6 utf8_and_euc2004.so utf8_and_sjis.so
euc_kr_and_mic.so libecpg.so.6.5 libpqwalreceiver.so utf8_and_euc_cn.so utf8_and_uhc.so
euc_tw_and_big5.so libpgcommon.a pgcrypto.so utf8_and_euc_jp.so utf8_and_win.so
latin2_and_win1250.so libpgport.a pg_upgrade_support.so utf8_and_euc_kr.so
latin_and_mic.so libpgtypes.a pgxs/ utf8_and_euc_tw.so
libecpg.a libpgtypes.so pkgconfig/ utf8_and_gb18030.so
CREATE EXTENSION
ilove=# create table t(day date,equipment varchar(20),output integer);
CREATE TABLE
ilove=# insert into t values('2010-04-01','DAT501',100);
INSERT 0 1
502',120);
ilove=# insert into t values('2010-04-02',110);
ilove=# insert into t values('2010-04-03',125);
ilove=# insert into t values('2010-04-04',200);
ilove=# SELECT * FROM crosstab('select day,equipment,output from t order by 1,2',$$values('DAT501'::text),('DAT502'::text),('DAT503'::text)$$) 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 |
2010-04-03 | | | 125
2010-04-04 | 100 | | 200
(4 rows)
ilove=#
