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?
