PLV8是PostgreSQL数据库受信任的JavaScript语言扩展。可以使用JavaScript来编写PostgreSQL数据库函数。本文介绍了如何安装和使用PLV8插件。
功能优势
- 基于JavaScript,简单易用。
- 使用Google的V8引擎,性能强大。
- 使SQL函数更加丰富。
安装部署
- 安装PLV8到数据库。
CREATE EXTENSION plv8;
- 验证安装结果。
结果返回PLV8版本,表示安装成功。SELECT plv8_version();
- 运行环境。
运行环境会话独立,如果会话切换,则会初始化新的JS运行上下文,保证数据独立。
- 初始化设置。
SET plv8.start_proc = 'xxx';
说明- 只有管理员可以进行初始化设置。
- xxx表示初始化设置的函数或变量。示例如下:
//参考回归文件startup.sql和startup_pre.sql set plv8.start_proc = startup; do $$ plv8.elog(NOTICE, 'foo = ' + foo) $$ language plv8;
使用指南
PLV8可以在PostgreSQL内部执行多种类型的函数调用,也可以使用多个绑定到PLV8对象的内置函数。
- 标量函数调用在PLV8中,您通常可以使用
CREATE FUNCTION
语句在JavaScript中编写您需要调用的函数。示例如下:
在内部,该函数定义如下: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; =# SELECT plv8_test(ARRAY['name', 'age'], ARRAY['Tom', '29']); plv8_test --------------------------- {"name":"Tom","age":"29"} (1 row)
(function(keys, vals) { var o = {}; for(var i=0; i<keys.length; i++){ o[keys[i]] = vals[i]; } return o; })
说明 其中。- keys和vals在PostgreSQL内部进行类型检查和验证,并作为函数的参数调用。
- o作为JSON类型返回给PostgreSQL的对象。如果在创建函数时省略了参数名称,它们将在函数中以
$1
和$2
等形式出现。
- 集合返回函数调用PLV8支持从函数调用返回集合。
执行结果如下:CREATE TYPE rec AS (i integer, t text); CREATE FUNCTION set_of_records() RETURNS SETOF rec AS $$ // plv8.return _next() 将记录保存在内部元组存储中, // 并在函数终止时返回所有记录。 plv8.return_next( { "i": 1, "t": "a" } ); plv8.return_next( { "i": 2, "t": "b" } ); // 您还可以采用JSON数组形式返回记录。 return [ { "i": 3, "t": "c" }, { "i": 4, "t": "d" } ]; $$ LANGUAGE plv8;
SELECT * FROM set_of_records(); i | t ---+--- 1 | a 2 | b 3 | c 4 | d (4 rows)
说明- 如果函数声明为
RETURNS SETOF
,每次调用函数时PLV8都会准备一个元组存储。您可以根据需要多次调用plv8.return_next()
函数来返回一行。此外,您还可以通过返回一个数组来添加一组记录。 - 如果
return_next()
的参数对象具有参数未定义的额外属性,则return_next()
会引发错误。
- 如果函数声明为
- 触发器函数调用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_tbl FOR EACH ROW EXECUTE PROCEDURE test_trigger('foo', 'bar');
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 9.0及以上版本,PLV8支持
DO
块。DO $$ plv8.elog(NOTICE, 'this', 'is', 'inline', 'code') $$ LANGUAGE plv8;
- JavaScript和数据库内置类型之间的自动映射对于结果和参数,如果所需的数据库列类型是以下之一,则会自动映射数据库列类型和JavaScript数据类型。
- oid
- bool
- int2
- int4
- int8
- float4
- float8
- numeric
- date
- timestamp
- timestamptz
- bytea
- json (>= 9.2)
- jsonb (>= 9.4)
cstring
表示形式对其进行转换。 仅当维度为1时才支持数组类型。 JavaScript对象在使用时将映射到元组。 除这些类型外,PLV8还支持ANYELEMENT
和ANYARRAY
等复合类型。BYTEA
的转换略有不同,详情请参见类型化数组。 - 类型化数组PLV8提供了一种类型化数组,允许快速访问本机内存,主要是为了在浏览器中支持canvas。 PLV8通过它将
BYTEA
和各种数组类型映射到JavaScript数组。 对于BYTEA
,您可以将每个字节作为无符号字节数组访问。 对于int2 /int4 /float4 /float8数组类型,PLV8通过使用PLV8域类型完成对每个元素的直接访问。plv8_int2array
映射int2[]
plv8_int4array
映射int4[]
plv8_float4array
映射float4[]
plv8_float8array
映射float8[]
类型化数组仅仅是说明PLV8使用快速访问方法而不是常规方法的注解。对于这些类型化数组,只有一维数组没有任何NULL元素。目前没有办法在PLV8函数中创建这样的类型化数组,只有参数可以是类型化数组。您可以修改元素并返回值。类型化数组的示例如下: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]); int4sum --------- 15 (1 row)
- 内置函数PLV8提供了以下内置函数:
- plv8.elog:向客户端或PostgreSQL日志文件发送消息。错误级别如下所示:
- DEBUG5
- DEBUG4
- DEBUG3
- DEBUG2
- DEBUG1
- LOG
- INFO
- NOTICE
- WARNING
- ERROR
var msg = 'world'; plv8.elog(DEBUG1, 'Hello', `${msg}!`);
- plv8.quote_literal、plv8.nullable、plv8.quote_ident:每个quote类的函数都与同名的内置SQL函数相同。
- plv8.find_function:提供一个函数来访问已在数据库中注册、定义PLV8函数的其他函数。
使用CREATE FUNCTION callee(a int) RETURNS int AS $$ return a * a $$ LANGUAGE plv8; CREATE FUNCTION caller(a int, t int) RETURNS int AS $$ var func = plv8.find_function("callee"); return func(a); $$ LANGUAGE plv8;
plv8.find_function()
,您可以查找其他PLV8函数。 如果查找结果不是PLV8函数,则会引发错误。plv8.find_function()
的函数签名参数是regproc
(仅函数名称)或regprocedure
(具有参数类型的函数名称)。 对于纯JavaScript函数,您可以将内部类型作为参数,使用void
类型作为返回类型,以确保不会发生来自SQL语句的任何调用。 - plv8.version:PLV8对象提供的版本字符串。该字符串对应PLV8模块版本。
- plv8.elog:向客户端或PostgreSQL日志文件发送消息。错误级别如下所示:
- 通过SPI访问数据库
PLV8提供了用于数据库访问的函数,包括预编译语句和游标。
- plv8.execute( sql [, args] ):执行SQL语句并检索结果。其中,
sql
参数必选,args
是一个可选数组包含sql
查询中传递的任何参数。 对于SELECT
查询,返回值是一个对象数组。 每个对象代表一行,对象属性映射为列名。 对于非SELECT
命令,返回值是一个整数,表示受影响的行数。var json_result = plv8.execute('SELECT * FROM tbl'); var num_affected = plv8.execute('DELETE FROM tbl WHERE price > $1', [ 1000 ]);
- plv8.prepare( sql [, typenames] ):打开或创建预编译语句。
typename
参数是一个数组,其中每个元素都是一个字符串,对应于每个绑定参数的数据库类型名称。 返回值是PreparedPlan
类型的对象。 在退出函数之前,该对象必须通过plan.free()
释放。var plan = plv8.prepare( 'SELECT * FROM tbl WHERE col = $1', ['int'] ); var rows = plan.execute( [1] ); var sum = 0; for (var i = 0; i < rows.length; i++) { sum += rows[i].num; } plan.free(); return sum;
- PreparedPlan.execute( [args] ):执行预编译语句。其中,
args
参数和plv8.execute()
所需的参数相同。如果语句没有任何参数,则args
参数可以省略,其返回结果也和plv8.execute()
相同。 - PreparedPlan.cursor( [args] ):从预编译语句中打开一个游标。
args
参数和plv8.execute()
以及PreparedPlan.execute()
所需参数相同。返回值是游标类型的对象。在退出函数之前,必须由Cursor.close()
关闭。var plan = plv8.prepare( 'SELECT * FROM tbl WHERE col = $1', ['int'] ); var cursor = plan.cursor( [1] ); var sum = 0, row; while (row = cursor.fetch()) { sum += row.num; } cursor.close(); plan.free(); return sum;
- PreparedPlan.free():释放预编译语句。
- Cursor.fetch( [nrows] ):如果省略了
nrows
参数,则从游标中获取一行并将其作为对象返回(不是数组)。如果指定nrows
参数,则获取与nrows
参数相同的行数,直至超过该行,并返回一个对象数组。 如果是负值,则向后获取。 - Cursor.move( [nrows] ):将游标移动到
nrows
指定的值。如果是负值则向后移动。 - Cursor.close():关闭游标。
- plv8.subtransaction( func ):每次执行时,
plv8.execute()
会创建一个子事务。 如果需要原子操作,则需要调用plv8.subtransaction()
来创建子事务块。try{ plv8.subtransaction(function(){ plv8.execute("INSERT INTO tbl VALUES(1)"); // should be rolled back! plv8.execute("INSERT INTO tbl VALUES(1/0)"); // occurs an exception }); } catch(e) { ... do fall back plan ... }
说明 如果子事务块中的一条SQL执行失败,则该事务块中的所有操作都将回滚。 如果事务块中的进程引发JavaScript异常,则将继续执行。 因此,使用try ... catch
块来捕获异常,并在异常发生时执行替代操作。
- plv8.execute( sql [, args] ):执行SQL语句并检索结果。其中,
- 窗口函数
您可以使用PLV8创建自定义的窗口函数。 PLV8通过封装C-level窗口函数API以支持全部功能。 为创建窗口函数,首先需要调用
plv8.get_window_object()
来创建窗口对象,plv8.get_window_object()
提供以下接口:说明 有关用户自定义窗口函数的更多信息,请参见PostgreSQL官方文档创建函数。- WindowObject.get_current_position():返回分区中的当前位置,从0开始。
- WindowObject.get_partition_row_count():返回分区中的行数。
- WindowObject.set_mark_position( pos ):在指定行设置标记。 此位置上方的行将消失,后续将无法访问。
- WindowObject.rows_are_peers( pos1, pos2 ):如果pos1和pos2的行是对等的,则返回
true
。 - WindowObject.get_func_arg_in_partition( argno, relpos, seektype, mark_pos )、WindowObject.get_func_arg_in_frame(
argno, relpos, seektype, mark_pos ):
将
argno
中的参数值(从0开始)返回给该函数,位置在距离当前分区或帧中seektype
的relpos
行处。seektype
可以是WindowObject.SEEK_HEAD
,WindowObject.SEEK_CURRENT
或WindowObject.SEEK_TAIL
。 如果mark_pos
为true
,则标记从中获取参数的行。 如果指定的行不在分区/帧中,则返回的值为undefined
。 - WindowObject.get_func_arg_in_current( argno ):将
argno
中的参数值(从0开始)返回给该函数的当前行处。说明 返回值与该函数的参数变量相同。 - WindowObject.get_partition_local( [size] ):返回分区本地值,该值在当前分区终止时释放。 如果没有存储任何内容,则返回
undefined
。size
参数(默认为1000)是第一次调用中分配的内存大小。 一旦分配内存,size
值就不会改变。 - WindowObject.set_partition_local( obj ):存储分区本地值,您可以通过
get_partition_local()
检索该值。 此函数在内部使用JSON.stringify()
来序列化对象,因此,如果传入一个无法序列化的值,则最终可能会是一个异常的值。 如果序列化值的大小超过分配的内存,将引发异常。