Syntax error in CREATE FUNCTION statement in psql using knex.js

Question

I'm trying to create a function in postgres with the knex but it gives this error:

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)

I have no idea what's wrong with the syntax.

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
    `);
};

Have you tried running your sql statement in the psql terminal? It is good to try to isolate the issue you're having. Not sure if this is the reason, but you're missing some ;s after END IF and END. You are also missing the LANGUAGE keyword at the end. — vekerdyb

vekerdyb vekerdyb · Accepted Answer · 2019-07-13T16:12:35

As @Belayer pointed out you need the semi-colons and you're missing $$ wrapping your function definition, and the language specification:

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;

Syntax error in CREATE FUNCTION statement in psql using knex.js

2 Answers