[GENERAL] Using UPDATE ... RETURNING in a custom SQL function, which RETURNS integer

[Alexander Farber]

Hello,

why does this fail in PostgreSQL 9.5 please?

Here is my custom SQL function :

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

Regards
Alex