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, Commented Jul 13, 2019 at 16:00

vekerdyb · Accepted Answer · 2019-07-13 16:12:35Z

2

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;

answered Jul 13, 2019 at 16:12

vekerdyb

1,27313 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Belayer · Accepted Answer · 2019-07-13 15:56:21Z

1

Missing semicolon. If statements terminate with END IF;

answered Jul 13, 2019 at 15:56

Belayer

15.1k2 gold badges13 silver badges26 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related