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

[Alexander Farber]

Thank you, Rob -

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

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