Re: Custom SQL function does not like IF-statement

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