Thread: Custom SQL function does not like IF-statement

Custom SQL function does not like IF-statement

From

Alexander Farber

Date:

26 September 2016, 21:22:18

Good evening!

For a 2-player game I am trying to create a custom SQL function, which stores a new message (if not empty) into words_chat table and then return all messages from that table for a given game:

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?

Is it maybe possible by adding a WHERE part to the UPDATE statement?

Greetings from Bochum

Alex

Re: Custom SQL function does not like IF-statement

From

hubert depesz lubaczewski

Date:

26 September 2016, 21:26:11

On Mon, Sep 26, 2016 at 08:22:11PM +0200, Alexander Farber wrote:
> ERROR:  syntax error at or near "IF"
> LINE 11:         IF LENGTH(TRIM(in_msg)) > 0 AND
>                  ^

of course it doesn't like it, because sql doesn't have "if" command.

If you want to use such syntax, you have to use plpgsql language for the
function.

Best regards,

depesz

Re: Custom SQL function does not like IF-statement

From

Pavel Stehule

Date:

26 September 2016, 21:26:24

2016-09-26 20:22 GMT+02:00 Alexander Farber <alexander.farber@gmail.com>:

Good evening!

For a 2-player game I am trying to create a custom SQL function, which stores a new message (if not empty) into words_chat table and then return all messages from that table for a given game:

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?

SQL language doesn't support procedural statements like IF. You have to use plpgsql.

Regards

Pavel

Is it maybe possible by adding a WHERE part to the UPDATE statement?

Greetings from Bochum
Alex

Re: Custom SQL function does not like IF-statement

From

Vik Fearing

Date:

26 September 2016, 21:43:20

On 09/26/2016 08:22 PM, Alexander Farber wrote:
> Good evening!
>
> For a 2-player game I am trying to create a custom SQL function, which
> stores a new message (if not empty) into words_chat table and then
> return all messages from that table for a given game:
>
> 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?
--
Vik Fearing                                          +33 6 46 75 15 36
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support

Re: Custom SQL function does not like IF-statement

From

Alexander Farber

Date:

26 September 2016, 22:12:26

Thank you Vik and others -

On Mon, Sep 26, 2016 at 8:43 PM, Vik Fearing <vik@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

Re: Custom SQL function does not like IF-statement

From

Alexander Farber

Date:

26 September 2016, 22:15:39

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

Thank you Vik and others -

On Mon, Sep 26, 2016 at 8:43 PM, Vik Fearing <vik@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