Re: how to correctly cast json value to text?

Hi

po 3. 5. 2021 v 11:15 odesílatel Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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
>
>
>