Re: Custom SQL function does not like IF-statement

Hi

2016-09-26 20:22 GMT+02:00 Alexander Farber <alexander(dot)farber(at)gmail(dot)com>:

> 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
>
>