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

From: Alexander Farber <alexander(dot)farber(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: How to call JSONB_INSERT with integer as the new to-be-inserted value?
Date: 2020-09-12 20:54:40
Message-ID: CAADeyWgGW7dhUV4nL17iTR3a4=uNW7hdHbGwK=gy4pD61Ar25Q@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Steve Baldwin 2020-09-12 21:20:34 Re: How to call JSONB_INSERT with integer as the new to-be-inserted value?
Previous Message Brian Dunavant 2020-09-11 17:43:01 Re: Inserting many rows using "with"