How to call JSONB_INSERT with integer as the new to-be-inserted value? - Mailing list pgsql-general

From Alexander Farber
Subject How to call JSONB_INSERT with integer as the new to-be-inserted value?
Date
Msg-id CAADeyWgGW7dhUV4nL17iTR3a4=uNW7hdHbGwK=gy4pD61Ar25Q@mail.gmail.com
Whole thread Raw
Responses Re: How to call JSONB_INSERT with integer as the new to-be-inserted value?
Re: How to call JSONB_INSERT with integer as the new to-be-inserted value?
List pgsql-general
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

pgsql-general by date:

Previous
From: Brian Dunavant
Date:
Subject: Re: Inserting many rows using "with"
Next
From: Steve Baldwin
Date:
Subject: Re: How to call JSONB_INSERT with integer as the new to-be-inserted value?