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
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 |