Re: How to call JSONB_INSERT with integer as the new to-be-inserted value?

2020-09-13 Thread Alexander Farber 
Thank you!

Re: How to call JSONB_INSERT with integer as the new to-be-inserted value?

2020-09-12 Thread Tom Lane 
Alexander Farber  writes:
> ERROR:  function jsonb_insert(jsonb, unknown, integer) does not exist
> LINE 1: SELECT JSONB_INSERT(_user, '{uid}', _uid)
>^
> HINT:  No function matches the given name and argument types. You might
> need to add explicit type casts.
> QUERY:  SELECT JSONB_INSERT(_user, '{uid}', _uid)
> CONTEXT:  PL/pgSQL function words_get_user(jsonb) line 44 at assignment

I think it'd work to do

JSONB_INSERT(_user, '{uid}', to_jsonb(_uid));

The third argument has to be jsonb, not something else.

regards, tom lane

Re: How to call JSONB_INSERT with integer as the new to-be-inserted value?

2020-09-12 Thread Steve Baldwin 
Hi Alex,

Try something like this:

b2bc_dev=# select jsonb_insert('{"hello": "world"}'::jsonb,
'{uid}'::text[], to_jsonb(1));
 jsonb_insert
--
 {"uid": 1, "hello": "world"}
(1 row)

Steve

On Sun, Sep 13, 2020 at 6:55 AM Alexander Farber 
wrote:

> Good evening,
>
> I am trying to take a JSONB object (comes from an HTTP cookie set by my
> app) and add a property "uid" to it, which should hold an integer:
>
> CREATE OR REPLACE FUNCTION words_get_user(
> in_users jsonb,
> OUT out_user jsonb
> ) RETURNS jsonb AS
> $func$
> DECLARE
> _user   jsonb;
> _uidinteger;
> _banned boolean;
> _removedboolean;
> BEGIN
> -- in_users must be a JSON array with at least 1 element
> IF in_users IS NULL OR JSONB_ARRAY_LENGTH(in_users) = 0 THEN
> RAISE EXCEPTION 'Invalid users = %', in_users;
> END IF;
>
> -- ensure that every record has a valid auth
> FOR _user IN SELECT * FROM JSONB_ARRAY_ELEMENTS(in_users)
> LOOP
> IF NOT words_valid_user((_user->>'social')::int,
>  _user->>'sid',
>  _user->>'auth') THEN
> RAISE EXCEPTION 'Invalid user = %', _user;
> END IF;
>
> IF out_user IS NULL THEN
> SELECT
> uid,
> u.banned_until > CURRENT_TIMESTAMP,
> u.removed
> INTO STRICT
> _uid,
> _banned,
> _removed
> FROM words_social s
> LEFT JOIN words_users u USING(uid)
> WHERE s.social = (_user->>'social')::int
> AND s.sid = _user->>'sid';
>
> IF _banned THEN
> RAISE EXCEPTION 'Banned user = %', _user;
> END IF;
>
> IF _removed THEN
> RAISE EXCEPTION 'Removed user = %', _user;
> END IF;
>
> out_user := JSONB_INSERT(_user, '{uid}', _uid);
> END IF;
> END LOOP;
> END
> $func$ LANGUAGE plpgsql;
>
> Unfortunately, when I run my stored function it fails:
>
> words_en=> select out_user from
> words_get_user('[{"given":"Abcde1","social":1,"auth":"xxx","stamp":1480237061,"sid":"a","photo":"
> https://vk.com/images/camera_200.png
> "},{"given":"Abcde2","social":2,"auth":"xxx","stamp":1477053188,"sid":"a"},{"given":"Abcde3","social":3,"auth":"xxx","stamp":1477053330,"sid":"a"}]'::jsonb);
> ERROR:  function jsonb_insert(jsonb, unknown, integer) does not exist
> LINE 1: SELECT JSONB_INSERT(_user, '{uid}', _uid)
>^
> HINT:  No function matches the given name and argument types. You might
> need to add explicit type casts.
> QUERY:  SELECT JSONB_INSERT(_user, '{uid}', _uid)
> CONTEXT:  PL/pgSQL function words_get_user(jsonb) line 44 at assignment
>
> What is missing here please?
>
> Thank you
> Alex
>
>

How to call JSONB_INSERT with integer as the new to-be-inserted value?

2020-09-12 Thread Alexander Farber 
Good evening,

I am trying to take a JSONB object (comes from an HTTP cookie set by my
app) and add a property "uid" to it, which should hold an integer:

CREATE OR REPLACE FUNCTION words_get_user(
in_users jsonb,
OUT out_user jsonb
) RETURNS jsonb AS
$func$
DECLARE
_user   jsonb;
_uidinteger;
_banned boolean;
_removedboolean;
BEGIN
-- in_users must be a JSON array with at least 1 element
IF in_users IS NULL OR JSONB_ARRAY_LENGTH(in_users) = 0 THEN
RAISE EXCEPTION 'Invalid users = %', in_users;
END IF;

-- ensure that every record has a valid auth
FOR _user IN SELECT * FROM JSONB_ARRAY_ELEMENTS(in_users)
LOOP
IF NOT words_valid_user((_user->>'social')::int,
 _user->>'sid',
 _user->>'auth') THEN
RAISE EXCEPTION 'Invalid user = %', _user;
END IF;

IF out_user IS NULL THEN
SELECT
uid,
u.banned_until > CURRENT_TIMESTAMP,
u.removed
INTO STRICT
_uid,
_banned,
_removed
FROM words_social s
LEFT JOIN words_users u USING(uid)
WHERE s.social = (_user->>'social')::int
AND s.sid = _user->>'sid';

IF _banned THEN
RAISE EXCEPTION 'Banned user = %', _user;
END IF;

IF _removed THEN
RAISE EXCEPTION 'Removed user = %', _user;
END IF;

out_user := JSONB_INSERT(_user, '{uid}', _uid);
END IF;
END LOOP;
END
$func$ LANGUAGE plpgsql;

Unfortunately, when I run my stored function it fails:

words_en=> select out_user from
words_get_user('[{"given":"Abcde1","social":1,"auth":"xxx","stamp":1480237061,"sid":"a","photo":"
https://vk.com/images/camera_200.png
"},{"given":"Abcde2","social":2,"auth":"xxx","stamp":1477053188,"sid":"a"},{"given":"Abcde3","social":3,"auth":"xxx","stamp":1477053330,"sid":"a"}]'::jsonb);
ERROR:  function jsonb_insert(jsonb, unknown, integer) does not exist
LINE 1: SELECT JSONB_INSERT(_user, '{uid}', _uid)
   ^
HINT:  No function matches the given name and argument types. You might
need to add explicit type casts.
QUERY:  SELECT JSONB_INSERT(_user, '{uid}', _uid)
CONTEXT:  PL/pgSQL function words_get_user(jsonb) line 44 at assignment

What is missing here please?

Thank you
Alex