1
CREATE OR REPLACE FUNCTION my_function(input jsonb)
  RETURNS jsonb
  LANGUAGE plpgsql AS  -- language declaration required
$func$
DECLARE
   _key   text;
   _value text;
BEGIN
    FOR _key, _value IN
      SELECT jsonb_each_text($1)
    LOOP
        -- do some math operation on its corresponding value
        RAISE NOTICE '%: %', _key, _value;
    END LOOP;

    RETURN input;
END
$func$;

For the function, if I call:

my_function('{"a":1, "b":2}');

It will raise notice that looks like (a, 1), (b, 2) in the message box.

However, if I change the code:

        RAISE NOTICE '%: %', _key, _value;

into:

        RAISE NOTICE '%', _key;

The same results will pop up.

While the code:

        RAISE NOTICE '%', _value;

would pop up nothing. How did that happen?

  • You seem to have two separate logins by mistake "JaneL" and "jane.L". Notify a moderator who will help you to merge the accounts. – Erwin Brandstetter Mar 1 at 17:48
0

Guess I am to blame for this confusion.
The code example is from my answer to your previous question, which had a subtle bug (now fixed).

What was going wrong?

jsonb_each_text() is a set-returning function, it returns a set of rows. When called with:

SELECT * FROM jsonb_each_text('{"a":1, "b":2}')

it returns:

 key | value 
-----+-------
 a   | 1
 b   | 2

But when called like above with:

SELECT jsonb_each_text('{"a":1, "b":2}')

it returns:

 jsonb_each_text 
-----------------
 (a,1)
 (b,2)

Each row is returned with a single value, a row value, the text representation of which is as displayed.

Now, the assignment in:

  FOR _key, _value IN SELECT jsonb_each_text($1) ...

assigns the first value per row to the first variable provided, which is _key in the example. The row value is cast to text in the assignment. Since there is no second value, _value is set to NULL. So, the later:

 RAISE NOTICE '%: %', _key, _value;

outputs the messages:

NOTICE:  (a,1): <NULL>
NOTICE:  (b,2): <NULL>

Which can be pretty misleading. (But note the dangling : <NULL>!)

Change to:

FOR _key, _value IN SELECT * FROM jsonb_each_text($1)

.. and the mists should clear.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.