Postgres Pro
Downloads
Home   >   mailing lists

Thread: Using UPDATE ... RETURNING in a custom SQL function, which RETURNS integer

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

From
Alexander Farber
Date:
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


Re: Using UPDATE ... RETURNING in a custom SQL function, which RETURNS integer

From
rob stone
Date:

> 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);
>                ^
>
>
Shouldn't you be doing:-

SELECT words_unban_user(1);

Re: Using UPDATE ... RETURNING in a custom SQL function, which RETURNS integer

From
Alexander Farber
Date:
Thank you, Rob -

On Fri, Dec 2, 2016 at 11:12 AM, Rob Sargent <robjsargent@gmail.com> wrote:

> On Dec 2, 2016, at 2:52 AM, Alexander Farber <alexander.farber@gmail.com> wrote:
>
> 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;
>
> words=> SELECT uid FROM words_unban_user(1);
> ERROR:  column "uid" does not exist
> LINE 1: SELECT uid FROM words_unban_user(1);
>                ^
>
select words_unban_user(1) as uid;
Your function returns an int not a table.

this has worked well.

However if I rewrite the same function as "language plpgsql" - then suddenly both ways of calling work:

CREATE OR REPLACE FUNCTION words_unban_user(
        in_uid integer,
        OUT out_uid integer)
        RETURNS integer AS
$func$
BEGIN
        UPDATE words_users SET
        banned_until = null,
        banned_reason = null
        WHERE uid = in_uid
        RETURNING uid into out_uid;
END
$func$ LANGUAGE plpgsql;

words=> select out_uid AS uid from words_unban_user(1);
 uid
-----
   1
(1 row)

words=> select words_unban_user(1) AS uid;
 uid
-----
   1
(1 row)

I am curious, why is it so...

Regards
Alex





Re: Using UPDATE ... RETURNING in a custom SQL function, which RETURNS integer

From
Adrian Klaver
Date:
On 12/02/2016 04:23 AM, Alexander Farber wrote:
> Thank you, Rob -
>
> On Fri, Dec 2, 2016 at 11:12 AM, Rob Sargent <robjsargent@gmail.com
> <mailto:robjsargent@gmail.com>> wrote:
>
>
>     > On Dec 2, 2016, at 2:52 AM, Alexander Farber
>     <alexander.farber@gmail.com <mailto:alexander.farber@gmail.com>> wrote:
>     >
>     > 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;
>     >
>     > words=> SELECT uid FROM words_unban_user(1);
>     > ERROR:  column "uid" does not exist
>     > LINE 1: SELECT uid FROM words_unban_user(1);
>     >                ^
>     >
>
>     select words_unban_user(1) as uid;
>     Your function returns an int not a table.
>
>
> this has worked well.
>
> However if I rewrite the same function as "language plpgsql" - then
> suddenly both ways of calling work:
>
> CREATE OR REPLACE FUNCTION words_unban_user(
>         in_uid integer,
>         OUT out_uid integer)
>         RETURNS integer AS
> $func$
> BEGIN
>         UPDATE words_users SET
>         banned_until = null,
>         banned_reason = null
>         WHERE uid = in_uid
>         RETURNING uid into out_uid;
> END
> $func$ LANGUAGE plpgsql;
>
> words=> select out_uid AS uid from words_unban_user(1);
>  uid
> -----
>    1
> (1 row)
>
> words=> select words_unban_user(1) AS uid;
>  uid
> -----
>    1
> (1 row)
>
> I am curious, why is it so...

In the SQL function you are not just using RETURNING to pop out the uid,
which is not actually assigned to any output variable name but just
returned as an integer.

In the plpgsql case you actually assign uid to an output variable name.

FYI, you can have OUT in SQL functions also:

https://www.postgresql.org/docs/9.5/static/xfunc-sql.html#XFUNC-OUTPUT-PARAMETERS

>
> Regards
> Alex
>
>
>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com