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

[Steve Baldwin <steve.baldwin@xxxxxxxxx>]

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 <alexander.farber@xxxxxxxxx> 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;
        _uid        integer;
        _banned     boolean;
        _removed    boolean;
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":"aaaaa","photo":"https://vk.com/images/camera_200.png"},{"given":"Abcde2","social":2,"auth":"xxx","stamp":1477053188,"sid":"aaaaa"},{"given":"Abcde3","social":3,"auth":"xxx","stamp":1477053330,"sid":"aaaaa"}]'::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