Re: You will need to rewrite or cast the expression

--0000000000007e303b058d7867c3
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Salut,

en ajoutant un cast explicite ?

     INSERT INTO test (
            name
            ) SELECT unnest::jsonb FROM UNNEST(p_names);

Le ven. 12 juil. 2019 =C3=A0 11:07, CRUMEYROLLE Pierre <pierre.crumeyrolle@=
c-s.fr>
a =C3=A9crit :

> bonjour
> je fais un test d'insertion de jsonb via unnest en pgsql
> comment puis je resoudre l'erreur "You will need to rewrite or cast
> the expression."
> voir ci sessous
> merci
>
>
> drop table test;
> CREATE TABLE test (
>         id   serial NOT NULL primary key,
>         name jsonb
> );
>
> CREATE OR REPLACE FUNCTION jsonb_array_to_text_array(
>    p_input jsonb
> ) RETURNS TEXT[] AS $BODY$
>
> DECLARE v_output text[];
>
> BEGIN
>
>    SELECT array_agg(ary)::text[]
>    INTO v_output
>    FROM jsonb_array_elements_text(p_input) AS ary;
>
>    RETURN v_output;
>
> END;
>
> $BODY$
> LANGUAGE plpgsql VOLATILE;
>
> CREATE OR REPLACE FUNCTION save_with_unnest(IN p_names text[])
> RETURNS void AS $BODY$
> BEGIN
>         INSERT INTO test (
>         name
>         ) SELECT * FROM UNNEST(p_names);
>
> END;
> $BODY$
> LANGUAGE plpgsql VOLATILE SECURITY DEFINER COST 100;
>
>
> select save_with_unnest(jsonb_array_to_text_array('["a", "b",
> "c"]'::jsonb));
>
> postgres=3D# select save_with_unnest(jsonb_array_to_text_array('["a",
> "b", "c"]'::jsonb));
>
> ERROR:  column "name" is of type jsonb but expression is of type text
> LINE 3: ) SELECT * FROM UNNEST(p_names)
>                   ^
> HINT:  You will need to rewrite or cast the expression.
> QUERY:  INSERT INTO test (
>      name
> ) SELECT * FROM UNNEST(p_names)
> CONTEXT:  PL/pgSQL function save_with_unnest(text[]) line 3 at SQL
> statement
>
>
>
>
>
>
>
>

--0000000000007e303b058d7867c3
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>Salut,</div><div><br></div><div>en ajoutant un cast e=
xplicite ?</div><div><br></div><div>=C2=A0=C2=A0=C2=A0=C2=A0
INSERT INTO test (<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 name<br>=C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 ) SELECT unnest::jsonb FROM UNNEST(p_names);

</div></div><br><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_=
attr">Le=C2=A0ven. 12 juil. 2019 =C3=A0=C2=A011:07, CRUMEYROLLE Pierre &lt;=
<a href=3D"mailto:[email protected]">[email protected]</a>&=
gt; a =C3=A9crit=C2=A0:<br></div><blockquote class=3D"gmail_quote" style=3D=
"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-le=
ft:1ex">bonjour<br>
je fais un test d&#39;insertion de jsonb via unnest en pgsql<br>
comment puis je resoudre l&#39;erreur &quot;You will need to rewrite or cas=
t=C2=A0 <br>
the expression.&quot;<br>
voir ci sessous<br>
merci<br>
<br>
<br>
drop table test;<br>
CREATE TABLE test (<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 id=C2=A0 =C2=A0serial NOT NULL primary key,<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 name jsonb<br>
);<br>
<br>
CREATE OR REPLACE FUNCTION jsonb_array_to_text_array(<br>
=C2=A0 =C2=A0p_input jsonb<br>
) RETURNS TEXT[] AS $BODY$<br>
<br>
DECLARE v_output text[];<br>
<br>
BEGIN<br>
<br>
=C2=A0 =C2=A0SELECT array_agg(ary)::text[]<br>
=C2=A0 =C2=A0INTO v_output<br>
=C2=A0 =C2=A0FROM jsonb_array_elements_text(p_input) AS ary;<br>
<br>
=C2=A0 =C2=A0RETURN v_output;<br>
<br>
END;<br>
<br>
$BODY$<br>
LANGUAGE plpgsql VOLATILE;<br>
<br>
CREATE OR REPLACE FUNCTION save_with_unnest(IN p_names text[])<br>
RETURNS void AS $BODY$<br>
BEGIN<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 INSERT INTO test (<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 name<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 ) SELECT * FROM UNNEST(p_names);<br>
<br>
END;<br>
$BODY$<br>
LANGUAGE plpgsql VOLATILE SECURITY DEFINER COST 100;<br>
<br>
<br>
select save_with_unnest(jsonb_array_to_text_array(&#39;[&quot;a&quot;, &quo=
t;b&quot;, &quot;c&quot;]&#39;::jsonb));<br>
<br>
postgres=3D# select save_with_unnest(jsonb_array_to_text_array(&#39;[&quot;=
a&quot;,=C2=A0 <br>
&quot;b&quot;, &quot;c&quot;]&#39;::jsonb));<br>
<br>
ERROR:=C2=A0 column &quot;name&quot; is of type jsonb but expression is of =
type text<br>
LINE 3: ) SELECT * FROM UNNEST(p_names)<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ^<br>
HINT:=C2=A0 You will need to rewrite or cast the expression.<br>
QUERY:=C2=A0 INSERT INTO test (<br>
=C2=A0 =C2=A0 =C2=A0name<br>
) SELECT * FROM UNNEST(p_names)<br>
CONTEXT:=C2=A0 PL/pgSQL function save_with_unnest(text[]) line 3 at SQL sta=
tement<br>
<br>
<br>
<br>
<br>
<br>
<br>
<br>
</blockquote></div>

--0000000000007e303b058d7867c3--