Re: You will need to rewrite or cast the expression

Mon précédent message comporte la requête corrigée.

Le ven. 12 juil. 2019 à 11:21, Pierre Crumeyrolle <pierre(dot)crumeyrolle(at)c-s(dot)fr>
a écrit :

> plus precisement comment on fait
> Le 12/07/2019 à 11:19, Tumasgiu Rossini a écrit :
>
> Salut,
>
> en ajoutant un cast explicite ?
>
> INSERT INTO test (
> name
> ) SELECT unnest::jsonb FROM UNNEST(p_names);
>
> Le ven. 12 juil. 2019 à 11:07, CRUMEYROLLE Pierre <
> pierre(dot)crumeyrolle(at)c-s(dot)fr> a écrit :
>
>> 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=# 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
>>
>>
>>
>>
>>
>>
>>
>>