plv8-使用JavaScript编写存储过程，初体验

PLV8入门教程

PLV8是PostgreSQL 的Javascript语言扩展。它可以用于存储过程，触发器等。

PLV8适用于大多数版本的Postgres，但最适用于9.1和以上。

PLV8安装

使用docker安装plv8

# 拉取镜像
docker pull huanglg/oracle_fdw_plv8:v4
# 创建运行容器，并映射端口
docker volume create postgres
docker run -d --name postgres -p 5432:5432 -e POSTGRES_PASSWORD=python03$ --restart=always  --privileged=true  -v  --mount=postgres huanglg/oracle_fdw_plv8:v4
# 进入容器
docker exec -it plv8 bash
# 切换用户
psql -U postgres
1
2
3
4
5
6
7
8
9

--安装plv8扩展
CREATE EXTENSION plv8;
--查看安装情况
SELECT extversion FROM pg_extension WHERE extname = 'plv8';
1
2
3
4

使用

Scalar Function Calls

--创建一个Scalar函数
CREATE FUNCTION plv8_test(keys TEXT[], vals TEXT[]) RETURNS JSON AS 
$$
    var o = {};
    for(var i=0; i<keys.length; i++){
        o[keys[i]] = vals[i];
    }
    return o;
$$ LANGUAGE plv8 IMMUTABLE STRICT;
1
2
3
4
5
6
7
8
9

--调用Scalar函数
SELECT plv8_test(ARRAY['name', 'age'], ARRAY['Tom', '29']);
1
2

PLV8 可以返回set类型

--1.创建set类型
CREATE TYPE rec AS (i integer, t text);
--2.创建返回set类型的函数
CREATE FUNCTION set_of_records() RETURNS SETOF rec AS
$$
    plv8.return_next( { "i": 1, "t": "a" } );
    plv8.return_next( { "i": 2, "t": "b" } );
    return [ { "i": 3, "t": "c" }, { "i": 4, "t": "d" } ];
$$
LANGUAGE plv8;
--3.调用该函数
SELECT * FROM set_of_records();
--4.结果如下：
i | t
---+---
1 | a
2 | b
3 | c
4 | d
(4 rows)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

如果函数的返回类型为SETOF，tuplestore每次调用都会准备一次，可以根据需要调用plv8.return_next()返回一行，还可以返回array返回一组记录。

PLV8创建触发器函数

CREATE FUNCTION test_trigger() RETURNS TRIGGER AS
$$
    plv8.elog(NOTICE, "NEW = ", JSON.stringify(NEW));
    plv8.elog(NOTICE, "OLD = ", JSON.stringify(OLD));
    plv8.elog(NOTICE, "TG_OP = ", TG_OP);
    plv8.elog(NOTICE, "TG_ARGV = ", TG_ARGV);
    if (TG_OP == "UPDATE") {
        NEW.i = 102;
        return NEW;
    }
$$
LANGUAGE "plv8";

-- 创建触发器
CREATE TRIGGER test_trigger
    BEFORE INSERT OR UPDATE OR DELETE
    ON test_tb FOR EACH ROW
    EXECUTE PROCEDURE test_trigger('foo', 'bar');

-- 插入一条数据观察结果
INSERT INTO public.test_tb(
	id, name)
	VALUES (1, 'hlg');
	
-- 结果如下
NOTICE:  NEW =  {"id":"1         ","name":"hlg                 "}
NOTICE:  OLD =  undefined
NOTICE:  TG_OP =  INSERT
NOTICE:  TG_ARGV =  foo,bar
INSERT 0 1
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

如果触发器类型是INSERT或UPDATE，则可以指定NEW变量的属性 以更改此操作存储的实际元组。PLV8触发器函数将具有以下包含触发器状态的特殊参数：

• NEW
• OLD
• TG_NAME
• TG_WHEN
• TG_LEVEL
• TG_OP
• TG_RELID
• TG_TABLE_NAME
• TG_TABLE_SCHEMA
• TG_ARGV

有关更多信息，请参阅PostgreSQL手册中的触发器部分。

类型数组

在PLV8中数组类型和JavaScript中的对应，比如：

• plv8_int2array ->int2[]
• plv8_int4array ->int4[]
• plv8_float4array ->float4[]
• plv8_float8array ->float8[]

--求和函数
CREATE FUNCTION int4sum(ary plv8_int4array) RETURNS int8 AS
$$
	var sum=0;
	for (var i=0; i < ary.length; i++) {
		sum += ary[i];
	}
	return sum;
$$ LANGUAGE plv8 IMMUTABLE STRICT;

--使用该函数
select int4sum(ARRAY[1, 2, 3, 4, 5]);
1
2
3
4
5
6
7
8
9
10
11
12

plv8.elog可以向客户端或日志文件打印日志，日志级别如下：

• DEBUG5

• DEBUG4

• DEBUG3

• DEBUG2

• DEBUG1

• LOG

• INFO

• NOTICE

• WARNING

• ERROR

  例如:

  DO 
  $$
  	var msg = 'world';
  	plv8.elog(NOTICE, 'Hello', `${msg}!`);
  $$ language plv8;
  1
  2
  3
  4
  5

访问数据库

plv8.execute(sql [, args])
1

执行SQL语句并检索结果。该sql参数是必需的，而args参数是一个可选array含在SQL查询传递任何参数。对于SELECT查询，返回值是array 的objects。每个object代表一行，object属性映射为列名。对于非SELECT查询，返回结果是受影响的行数。

例如:

-- 查询
DO 
$$  
	var json_result = plv8.execute('SELECT * FROM test_tb');
	plv8.elog(NOTICE, JSON.stringify(json_result), 'inline', 'code');
$$ LANGUAGE plv8;
-- 更新
DO 
$$  
	var json_result = plv8.execute('SELECT * FROM test_tb');
	plv8.elog(NOTICE, JSON.stringify(json_result), 'inline', 'code');
$$ LANGUAGE plv8;
1
2
3
4
5
6
7
8
9
10
11
12

plv8.prepare

plv8.prepare(sql [, typenames])
1

打开或创建准备好的声明。该typename参数是array 每个元素string对应于每个bind参数的PostgreSQL类型名称的参数。返回值是该PreparedPlan类型的对象。plan.free()在离开函数之前必须释放此对象。

例如

DO
$$
	var plan = plv8.prepare('SELECT * FROM test_tb WHERE id = $1', [ 'character' ]);
	var rows = plan.execute([ '3' ]);
	for (var i = 0; i < rows.length; i++) {
	  plv8.elog(NOTICE, JSON.stringify(rows[i]), i);
	}
	// 释放
	plan.free();
$$ language plv8;
1
2
3
4
5
6
7
8
9
10

PreparedPlan.cursor

PreparedPlan.cursor([ args ])
1

从准备好的语句中打开游标。该args参数是一样的东西将需要plv8.execute()和PreparedPlan.execute()。返回的对象是类型Cursor。必须Cursor.close() 在离开功能之前将其关闭。

DO
$$
	var plan = plv8.prepare('SELECT * FROM test_tb WHERE id = $1', [ 'character' ]);
	var cursor = plan.cursor([ '2' ]);
	var row;
	while(row = cursor.fetch()) {
			plv8.elog(NOTICE, JSON.stringify(row));  
	}
	// 释放
  	cursor.close();
	plan.free();
$$ language plv8;
1
2
3
4
5
6
7
8
9
10
11
12

plv8.subtransaction

plv8.subtransaction(func)
1

plv8.execute()每次执行时都会创建一个子事务。如果需要原子操作，则需要调用plv8.subtransaction()以创建子事务块。

DO
$$
try {
		 plv8.subtransaction(function(){
			 plv8.execute(`insert into test_tb (id, name) values('4','hhxy1')`);
			 plv8.execute(`insert into test_tb (id, name) values(1/0,'hhxy1')`);
		});
	 }catch(e){
			plv8.elog(ERROR, e, 'rollback')
			}
$$ language plv8;
--或者
DO
$$
try {
	 plv8.execute(`insert into test_tb (id, name) values('4','hhxy1')`);
	 plv8.execute(`insert into test_tb (id, name) values(1/0,'hhxy1');`);
	 }catch(e){
			plv8.elog(ERROR, e, 'rollback')
			}
$$ language plv8;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21