Thank you Vik and others - On Mon, Sep 26, 2016 at 8:43 PM, Vik Fearing <v...@2ndquadrant.fr> 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
