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

Reply via email to