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.far...@gmail.com> wrote: > 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 >
