Contents
実行環境
- 仮想マシンのゲスト側:Portable VirtualBox 4.3.6 + CentOS 6.6 + PostgreSQL 9.3.9
- ホスト側:Windows
7 32bit + ConEmu Build 150513 + SSH、psql
仮想マシンを、VirtualBox
直接関係ありませんがゲスト-ホスト間のネットワークは
yum でインストール
yumyum list | grep plv8 plv8_93.x86_64 1.4.1-1.rhel6 pgdg93 plv8_93-debuginfo.x86_64 1.4.1-1.rhel6 pgdg93 # yum -y install plv8_93 # service postgresql-9.3 restart
次は
CREATE DATABASE plv8_test TEMPLATE = template0 LC_COLLATE = 'C' LC_CTYPE = 'C';
# \c plv8_test
CREATE EXTENSION plv8; SELECT extname, extversion FROM pg_extension; extname | extversion ---------+------------ plpgsql | 1.0 plv8 | 1.4.1
テスト(Typed array)
昨日初めて試したCREATE OR REPLACE FUNCTION int4ary_sum (ary plv8_int4array) RETURNS int8 LANGUAGE plv8 IMMUTABLE STRICT AS $$ var sum = 0; for (var i = ary.length - 1; 0 <= i; i--) { sum += ary[i]; } return sum; $$; CREATE OR REPLACE FUNCTION int4rows_sum_sfunc (sum int8, val int4) RETURNS int8 LANGUAGE plv8 IMMUTABLE STRICT AS $$ return sum + val; $$; CREATE AGGREGATE int4rows_sum (int4) ( sfunc = int4rows_sum_sfunc, stype = int8, initcond = 0 );
次に、テスト用テーブルを二つ作成。片方は普通に
CREATE TABLE tb_example_rows AS SELECT generate_series(1, n) AS val FROM cast(pow(10, 6) AS int) AS n; CREATE TABLE tb_example_ary AS SELECT array_agg(gs) AS ary FROM cast(pow(10, 6) AS int) AS n, generate_series(1, n) AS gs; SELECT array_length(ary, 1) FROM tb_example_ary; array_length -------------- 1000000 (1 行)
今日は実行
SELECT sum(val) FROM tb_example_rows; sum -------------- 500000500000 (1 行) 時間: 98.672 ms SELECT int4rows_sum(val) FROM tb_example_rows; (1 行) 時間: 612.027 ms SELECT int4ary_sum(ary) FROM tb_example_ary; (1 行) 時間: 13.554 ms
データが配列になっていれば
今日で、PostgreSQL 9.5