Re: Custom SQL function does not like IF-statement

Good evening!

For a 2-player game I am trying to create a custom SQL function, which stores a new message (if not empty) into words_chat table and then return all messages from that table for a given game:

CREATE OR REPLACE FUNCTION words_get_chat(

                in_uid integer,

                in_gid integer,

                in_msg varchar

        ) RETURNS TABLE (

                out_my boolean,

                out_msg varchar

        ) AS

$func$

        IF LENGTH(TRIM(in_msg)) > 0 AND

                -- ensure only messages of player1 and player2 are stored

                EXISTS (SELECT 1 FROM words_games 

                        WHERE gid = in_gid AND 

                        (player1 = in_uid OR player2 = in_uid)) THEN

                INSERT INTO words_chat (

                        created,

                        uid,

                        gid,

                        msg

                ) VALUES (

                        CURRENT_TIMESTAMP,

                        in_uid,

                        in_gid,

                        in_msg

                );

        END IF;

        

        SELECT 

                uid = in_uid,

                msg

        FROM words_chat

        WHERE gid = in_gid

        ORDER BY created DESC;

$func$ LANGUAGE sql;

Unfortunately, PostgreSQL 9.5.4 does not like the syntax:

ERROR:  syntax error at or near "IF"

LINE 11:         IF LENGTH(TRIM(in_msg)) > 0 AND
 

                 ^

Please, how to rewrite my queries, so that the SQL function syntax is ok?

Is it maybe possible by adding a WHERE part to the UPDATE statement?

Greetings from Bochum

Alex