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

Thank you, Rob -

On Fri, Dec 2, 2016 at 11:12 AM, Rob Sargent <robjsargent(at)gmail(dot)com> wrote:

>
> > On Dec 2, 2016, at 2:52 AM, Alexander Farber <alexander(dot)farber(at)gmail(dot)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