Re: Custom SQL function does not like IF-statement

[Alexander Farber <alexander.farber@xxxxxxxxx>]

Thank you Vik and others -

On Mon, Sep 26, 2016 at 8:43 PM, Vik Fearing <vik@xxxxxxxxxxxxxx> wrote:

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

Oops, I meant the INSERT.

Could the both WHERE conditions be added there?

Regards

Alex