Re: how to correctly cast json value to text?

[Pavel Stehule]

Hi

po 3. 5. 2021 v 11:15 odesílatel Pavel Stehule <pavel.steh...@gmail.com>
napsal:

> Hi
>
> I am testing a new subscripting interface for jsonb, and I found one issue.
>
> DO $$
> DECLARE v jsonb;
> BEGIN
>   v['a'] = '"Ahoj"';
>   RAISE NOTICE '%', v['a'];
> END;
> $$;
> NOTICE:  "Ahoj"
> DO
>
> When I use this interface for reading, the jsonb type is returned. What is
> the correct way for casting from jsonb text to text value? I would not
> double quotes inside the result. Cast to text doesn't help. For operator
> API we can use "->>" symbol. But we have nothing similar for subscript API.
>

now I need function like

CREATE OR REPLACE FUNCTION public.value_to_text(jsonb)
 RETURNS text
 LANGUAGE plpgsql
 IMMUTABLE
AS $function$
DECLARE x jsonb;
BEGIN
  x['x'] = $1;
  RETURN x->>'x';
END;
$function$

DO $$
DECLARE v jsonb;
BEGIN
  -- hodnota musi byt validni json
  v['a'] = '"Ahoj"';
  RAISE NOTICE '%', value_to_text(v['a']);
END;
$$;
NOTICE:  Ahoj
DO

Is it possible to do this with built functionality?

I miss the cast function for json scalar string value to string.

Regards

Pavel


> Regards
>
> Pavel
>
>
>