You will need to rewrite or cast the expression

CRUMEYROLLE Pierre <[email protected]> Fri, 12 Jul 2019 11:07:20 +0200
Newsgroups gmane.comp.db.postgresql.french
Message-ID <[email protected]>
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