Re: How to call JSONB_INSERT with integer as the new to-be-inserted value?
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?
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?
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