PLV8是PostgreSQL数据库受信任的JavaScript语言扩展。可以使用JavaScript来编写PostgreSQL数据库函数。本文介绍了如何安装和使用PLV8插件。

功能优势

  • 基于JavaScript,简单易用。
  • 使用Google的V8引擎,性能强大。
  • 使SQL函数更加丰富。

安装部署

  • 安装PLV8到数据库。
    CREATE EXTENSION plv8;
  • 验证安装结果。
    SELECT plv8_version();
    结果返回PLV8版本,表示安装成功。
  • 运行环境。

    运行环境会话独立,如果会话切换,则会初始化新的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;
    })
    说明 其中。
    • keysvals在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');
    如果触发器类型是INSERTUPDATE,您可以制定NEW变量的属性来更改此操作存储的实际元组。
    PLV8触发器函数包含以下特殊变量:
    • NEW
    • OLD
    • TG_NAME
    • TG_WHEN
    • TG_LEVEL
    • TG_OP
    • TG_RELID
    • TG_TABLE_NAME
    • TG_TABLE_SCHEMA
    • TG_ARGV
    更多信息,请参见PostgreSQL官方文档触发器函数
  • 内联语句调用
    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)
    如果以上类型是JavaScript兼容的,则可以转换成功。 否则,PLV8会尝试通过cstring表示形式对其进行转换。 仅当维度为1时才支持数组类型。 JavaScript对象在使用时将映射到元组。 除这些类型外,PLV8还支持ANYELEMENTANYARRAY等复合类型。 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模块版本。
  • 通过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创建自定义的窗口函数。 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开始)返回给该函数,位置在距离当前分区或帧中seektyperelpos行处。 seektype可以是WindowObject.SEEK_HEADWindowObject.SEEK_CURRENTWindowObject.SEEK_TAIL。 如果mark_postrue,则标记从中获取参数的行。 如果指定的行不在分区/帧中,则返回的值为undefined

    • WindowObject.get_func_arg_in_current( argno ):将argno中的参数值(从0开始)返回给该函数的当前行处。
      说明 返回值与该函数的参数变量相同。
    • WindowObject.get_partition_local( [size] ):返回分区本地值,该值在当前分区终止时释放。 如果没有存储任何内容,则返回undefinedsize参数(默认为1000)是第一次调用中分配的内存大小。 一旦分配内存,size值就不会改变。
    • WindowObject.set_partition_local( obj ):存储分区本地值,您可以通过get_partition_local()检索该值。 此函数在内部使用JSON.stringify()来序列化对象,因此,如果传入一个无法序列化的值,则最终可能会是一个异常的值。 如果序列化值的大小超过分配的内存,将引发异常。