Sorry, I've just realized you did that already.
And the WITH cte AS part is optional in this case...
Thank you
On Mon, Sep 26, 2016 at 9:12 PM, Alexander Farber <alexander.farber@xxxxxxxxx> wrote:
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?RegardsAlex
