Custom SQL function does not like IF-statement

From: Alexander Farber <alexander(dot)farber(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Custom SQL function does not like IF-statement
Date: 2016-09-26 18:22:11
Message-ID: CAADeyWinJxTWN5d5wTTjL9m_mzja=ceZ4WprLOyrQn-Qejnzig@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Pavel Stehule 2016-09-26 18:25:39 Re: Custom SQL function does not like IF-statement
Previous Message Melvin Davidson 2016-09-26 18:22:08 Re: Incrementally refreshed materialized view