Re: Custom SQL function does not like IF-statement

[Pavel Stehule <pavel.stehule@xxxxxxxxx>]

Hi

2016-09-26 20:22 GMT+02:00 Alexander Farber <alexander.farber@xxxxxxxxx>:

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?

SQL language doesn't support procedural statements like IF. You have to use plpgsql.

Regards

Pavel
 

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

Greetings from Bochum

Alex