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

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

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(dot)klaver(at)aklaver(dot)com