From: | Alexander Farber <alexander(dot)farber(at)gmail(dot)com> |
---|---|
To: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Using UPDATE ... RETURNING in a custom SQL function, which RETURNS integer |
Date: | 2016-12-02 12:23:55 |
Message-ID: | CAADeyWi7FT-gVFU24ujbSjAVz74ckML9ZYoSs3URhepWguyFDQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Stephen Frost | 2016-12-02 13:15:09 | Re: Postgresql 9.6 and Big Data |
Previous Message | rob stone | 2016-12-02 12:20:36 | Re: Using UPDATE ... RETURNING in a custom SQL function, which RETURNS integer |