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

[Alexander Farber <alexander.farber@xxxxxxxxx>]

Thank you, Rob -

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

> On Dec 2, 2016, at 2:52 AM, Alexander Farber <alexander.farber@xxxxxxxxx> 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