CREATE OR REPLACE FUNCTION words_unban_user( in_uid integer) RETURNS integer AS $func$ UPDATE words_users SET banned_until = null, banned_reason = null WHERE uid = in_uid RETURNING uid; -- returns the user to be notified
$func$ LANGUAGE sql;
Here is my table:
words=> TABLE words_users; uid | created | visited | ip | vip_until | grand_until | banned_until | banned_reason | win | loss | draw | elo | medals | green | red | coins -----+-----------------------------+-----------------------------+-----------+-----------+-------------+-----------------------------+---------------+-----+------+------+------+--------+-------+-----+------- 1 | 2016-12-02 10:33:59.0761+01 | 2016-12-02 10:36:36.3521+01 | 127.0.0.1 | | | 2016-12-09 10:34:09.9151+01 | ban user 1 | 0 | 0 | 0 | 1500 | 0 | 0 | 0 | 0 (1 row)
And finally here is the failing usage of the function :
words=> SELECT uid FROM words_unban_user(1); ERROR: column "uid" does not exist LINE 1: SELECT uid FROM words_unban_user(1); ^
The background is that this is a websockets-based game and of the custom functions should return a list of user ids to be notified about changes (like player was banned, opponent has resigned, ...)
In the custom plpgsql functions I use OUT parameters or return table with RETURN NEXT and it works fine.
But in the above sql function this does not work...