postgresql-9.3.2 tablefunc 安装

[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]$

[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]$

[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]$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]$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]$

[root@AY1206230659041b61356 /home/www/postgresql-9.3.2/contrib/tablefunc]$cp tablefunc.control /data/pgsql/share/extension/

[root@AY1206230659041b61356 /home/www/postgresql-9.3.2/contrib/tablefunc]$psql

psql (9.3.2)

Type "help" for help.

ilove=> create extension tablefunc;

ERROR:  permission denied to create extension "tablefunc"

HINT:  Must be superuser to create this extension.

ilove=> \q

[root@AY1206230659041b61356 /home/www/postgresql-9.3.2/contrib/tablefunc]$su www

[www@AY1206230659041b61356 ~/postgresql-9.3.2/contrib/tablefunc]$psql

psql (9.3.2)

Type "help" for help.

ilove=> create extension tablefunc;

ERROR:  permission denied to create extension "tablefunc"

HINT:  Must be superuser to create this extension.

ilove=> 

ilove=> \q

[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

psql (9.3.2)

Type "help" for help.

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]$

[www@AY1206230659041b61356 ~/postgresql-9.3.2/contrib/tablefunc]$psql -Uwww

psql (9.3.2)

Type "help" for help.

ilove=# create extension tablefunc;

ERROR:  could not access file "$libdir/tablefunc": 没有那个文件或目录

ilove=# \q

[www@AY1206230659041b61356 ~/postgresql-9.3.2/contrib/tablefunc]$which psql

/data/pgsql/bin/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    

[www@AY1206230659041b61356 ~/postgresql-9.3.2/contrib/tablefunc]$cp tablefunc.so /data/pgsql/lib/

[www@AY1206230659041b61356 ~/postgresql-9.3.2/contrib/tablefunc]$psql -Uwww

psql (9.3.2)

Type "help" for help.

ilove=# create extension tablefunc;

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

ilove=# insert into t values('2010-04-01','DAT502',120);

INSERT 0 1

ilove=# insert into t values('2010-04-01','DAT503',130);

INSERT 0 1

ilove=# insert into t values('2010-04-02','DAT501',110);

INSERT 0 1

ilove=# insert into t values('2010-04-02','DAT502',105); 

INSERT 0 1

ilove=# insert into t values('2010-04-03','DAT503',125);

INSERT 0 1

ilove=# insert into t values('2010-04-04','DAT501',100);

INSERT 0 1

ilove=# insert into t values('2010-04-04','DAT503',200);

INSERT 0 1

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=#