Обсуждение: Custom SQL function does not like IF-statement
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
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
Hi
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 storedEXISTS (SELECT 1 FROM words_gamesWHERE gid = in_gid AND(player1 = in_uid OR player2 = in_uid)) THENINSERT INTO words_chat (created,uid,gid,msg) VALUES (CURRENT_TIMESTAMP,in_uid,in_gid,in_msg);END IF;SELECTuid = in_uid,msgFROM words_chatWHERE gid = in_gidORDER 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 BochumAlex
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
Thank you Vik and others -
On Mon, Sep 26, 2016 at 8:43 PM, Vik Fearing <vik@2ndquadrant.fr> wrote:
Oops, I meant the INSERT.
As others have said, IF is not SQL (at least not the dialect thatOn 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?
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?
Could the both WHERE conditions be added there?
Regards
Alex
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?RegardsAlex