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

[Adrian Klaver]

On 12/02/2016 04:23 AM, Alexander Farber wrote:

Thank you, Rob -

On Fri, Dec 2, 2016 at 11:12 AM, Rob Sargent > wrote:


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

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  wrote:

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

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

[rob stone]

> 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

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

[Alexander Farber]

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