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

    RETURN input;

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;


        RAISE NOTICE '%', _key;

The same results will pop up.

While the code:

        RAISE NOTICE '%', _value;

would pop up nothing. How did that happen?


1 Answer 1


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:


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 and acknowledge that you have read and understand our privacy policy and code of conduct.

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