Re: You will need to rewrite or cast the expression
Tumasgiu Rossini <[email protected]> Fri, 12 Jul 2019 11:23:23 +0200
| Newsgroups | gmane.comp.db.postgresql.french |
|---|---|
| Message-ID | <CAJD9AWxvvCLaaZVJgECTxjDbESVZCLnXO0Jw_D3DkWrDeQ3dVw@mail.gmail.com> |
--000000000000ef5317058d7876ad Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Mon pr=C3=A9c=C3=A9dent message comporte la requ=C3=AAte corrig=C3=A9e. Le ven. 12 juil. 2019 =C3=A0 11:21, Pierre Crumeyrolle <pierre.crumeyrolle@= c-s.fr> a =C3=A9crit : > plus precisement comment on fait > Le 12/07/2019 =C3=A0 11:19, Tumasgiu Rossini a =C3=A9crit : > > 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 < > [email protected]> 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 >> >> >> >> >> >> >> >> --000000000000ef5317058d7876ad Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable <div dir=3D"ltr">Mon pr=C3=A9c=C3=A9dent message comporte la requ=C3=AAte c= orrig=C3=A9e.<br></div><br><div class=3D"gmail_quote"><div dir=3D"ltr" clas= s=3D"gmail_attr">Le=C2=A0ven. 12 juil. 2019 =C3=A0=C2=A011:21, Pierre Crume= yrolle <<a href=3D"mailto:[email protected]">pierre.crumeyrolle@= c-s.fr</a>> a =C3=A9crit=C2=A0:<br></div><blockquote class=3D"gmail_quot= e" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204)= ;padding-left:1ex"> =20 =20 =20 <div bgcolor=3D"#FFFFFF"> <p>plus precisement comment on fait <br> </p> <div class=3D"gmail-m_-2728214277762798589moz-cite-prefix">Le 12/07/201= 9 =C3=A0 11:19, Tumasgiu Rossini a =C3=A9crit=C2=A0:<br> </div> <blockquote type=3D"cite"> =20 <div dir=3D"ltr"> <div>Salut,</div> <div><br> </div> <div>en ajoutant un cast explicite ?</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 <<a href=3D"mailto:[email protected]= r" target=3D"_blank">[email protected]</a>> 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-left:1ex">bonjour<br> je fais un test d'insertion de jsonb via unnest en pgsql<br> comment puis je resoudre l'erreur "You will need to rewr= ite or cast=C2=A0 <br> the expression."<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 primar= y 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('["a&q= uot;, "b", "c"]'::jsonb));<br> <br> postgres=3D# select save_with_unnest(jsonb_array_to_text_array('["a",= =C2=A0 <br> "b", "c"]'::jsonb));<br> <br> ERROR:=C2=A0 column "name" is of type jsonb but express= ion 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 statement<br> <br> <br> <br> <br> <br> <br> <br> </blockquote> </div> </blockquote> </div> </blockquote></div> --000000000000ef5317058d7876ad--