Re: You will need to rewrite or cast the expression

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
>
>
>
>
>
>
>
>