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