Re: Custom SQL function does not like IF-statement

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(dot)farber(at)gmail(dot)com> wrote:

> Thank you Vik and others -
>
> On Mon, Sep 26, 2016 at 8:43 PM, Vik Fearing <vik(at)2ndquadrant(dot)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
>