javascript - 使用 knex.js 在 psql 中的 CREATE FUNCTION 语句中出现语法错误

Question

我试图在 postgres 中使用 knex 创建一个函数，但它给出了这个错误:

error: syntax error at or near "BEGIN" at Connection.parseE (E:\apps\node\api_moto_na_veia\node_modules\pg\lib\connection.js:553:11) at Connection.parseMessage (E:\apps\node\api_moto_na_veia\node_modules\pg\lib\connection.js:378:19) at Socket. (E:\apps\node\api_moto_na_veia\node_modules\pg\lib\connection.js:119:22) at Socket.emit (events.js:182:13) at addChunk (_stream_readable.js:283:12) at readableAddChunk (_stream_readable.js:264:11) at Socket.Readable.push (_stream_readable.js:219:10) at TCP.onStreamRead [as onread] (internal/stream_base_commons.js:94:17)

我不知道语法有什么问题。

exports.up = function(knex, Promise) {
    return knex.raw(`
        CREATE FUNCTION maxV(val1 numeric, val2 numeric) RETURNS numeric AS
            BEGIN
                IF (val1 > val2) THEN
                    RETURN val1;
                ELSE
                    RETURN val2;
                END IF
                RETURN NULL;
        END
    `);
};

Answer 1

正如@Belayer 指出的那样，您需要分号并且缺少 $$ 包装您的函数定义和语言规范:

CREATE FUNCTION maxV(val1 numeric, val2 numeric) RETURNS numeric AS $$
BEGIN
  IF (val1 > val2) THEN
    RETURN val1;
  ELSE
    RETURN val2;
  END IF;
  RETURN NULL;
END; 
$$
LANGUAGE plpgsql;