Re: Custom SQL function does not like IF-statement

On 09/26/2016 08:22 PM, Alexander Farber wrote:
> 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?

As others have said, IF is not SQL (at least not the dialect that
PostgreSQL understands).  You can rewrite the whole thing like this:

WITH cte AS (
    INSERT INTO words_chat (created, uid, gid, msg)
        SELECT current_timestamp, in_uid, in_gid, in_msg
        WHERE length(trim(in_msg)) > 0 AND
              EXISTS (SELECT 1 FROM words_games
                      WHERE gid = in_gid AND
                            in_uid in (player1, player2))
)
SELECT uid = in_uid, msg
FROM words_chat
WHERE gid = in_gid
ORDER BY created DESC;

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

Which UPDATE statement would that be?
--
Vik Fearing                                          +33 6 46 75 15 36
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support