4

As it is written here, json_object_agg(name, value) return type is json. Meanwhile, if I return the value returned by json_object_agg() from a stored procedure:

CREATE OR REPLACE FUNCTION _getlocales()

RETURNS json AS
$BODY$DECLARE
    var json;
BEGIN
    select into var json_object_agg("key", "values") from table;
    RETURN var;
END;$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

And call it in another function:

variable    =   _getlocales();    
RAISE NOTICE 'DATA TYPE %', pg_typeof(variable);
SELECT variable->>'property'

I can not get JSON object field. I run into:

ERROR: operator does not exist: text ->> unknown

And raised notice shows that data type is text.

NOTICE: DATA TYPE text

I've tried to change _getlocales() return type to jsonb, but nothing was changed:

CREATE OR REPLACE FUNCTION nav._getlocales()
RETURNS jsonb AS
$BODY$DECLARE
    _l18nJson   jsonb;
...

Why this happens?

Alexandr Lazarev
  • 12,554
  • 4
  • 38
  • 47
  • 2
    Please show more of the code in question. Crucially, we will need to see the definition of 'variable' – Richard Huxton Feb 25 '16 at 07:25
  • Oh, I've figured it out with your help. It was declared as text. Can't understand how I've missed it, thanks! You can post it as the answer, I'll be glad to accept it! – Alexandr Lazarev Feb 25 '16 at 07:35

1 Answers1

1

@Richard Huxton has pointed me the right direction, I was inattentive and I've declared variable with text data type. So, I've changed:

$BODY$DECLARE
    variable text;

To: 

$BODY$DECLARE
    variable json;

And the issue was solved.

Alexandr Lazarev
  • 12,554
  • 4
  • 38
  • 47