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

Started by Alexander Farberover 9 years ago4 messagesgeneral

Jump to latest

Alexander Farber

alexander.farber@gmail.com

over 9 years ago

Hello,

why does this fail in PostgreSQL 9.5 please?

Here is my custom SQL function :

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;

Here is my table:

words=> TABLE words_users;
uid | created | visited |
ip | vip_until | grand_until | banned_until |
banned_reason | win | loss | draw | elo | medals | green | red | coins
-----+-----------------------------+-----------------------------+-----------+-----------+-------------+-----------------------------+---------------+-----+------+------+------+--------+-------+-----+-------
1 | 2016-12-02 10:33:59.0761+01 | 2016-12-02 10:36:36.3521+01 |
127.0.0.1 | | | 2016-12-09 10:34:09.9151+01 | ban
user 1 | 0 | 0 | 0 | 1500 | 0 | 0 | 0 | 0
(1 row)

And finally here is the failing usage of the function :

words=> SELECT uid FROM words_unban_user(1);
ERROR: column "uid" does not exist
LINE 1: SELECT uid FROM words_unban_user(1);
^

The background is that this is a websockets-based game and of the custom
functions should return a list of user ids to be notified about changes
(like player was banned, opponent has resigned, ...)

In the custom plpgsql functions I use OUT parameters or return table with
RETURN NEXT and it works fine.

But in the above sql function this does not work...

Regards
Alex

rob stone

floriparob@gmail.com

over 9 years ago

In reply to: Alexander Farber (#1)

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

And finally here is the failing usage of the function :

words=> SELECT uid FROM words_unban_user(1);
ERROR: column "uid" does not exist
LINE 1: SELECT uid FROM words_unban_user(1);
^

Shouldn't you be doing:-

SELECT words_unban_user(1);

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Alexander Farber

alexander.farber@gmail.com

over 9 years ago

In reply to: Alexander Farber (#1)

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@gmail.com> wrote:

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

Import Notes

Reply to msg id not found: 098C7600-3CAC-4039-A1CD-5F2053D97A52@gmail.com

Adrian Klaver

adrian.klaver@aklaver.com

over 9 years ago

In reply to: Alexander Farber (#3)

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@gmail.com
<mailto:robjsargent@gmail.com>> wrote:

On Dec 2, 2016, at 2:52 AM, Alexander Farber

<alexander.farber@gmail.com <mailto:alexander.farber@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...

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.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general