From: | CRUMEYROLLE Pierre <pierre(dot)crumeyrolle(at)c-s(dot)fr> |
---|---|
To: | pgsql-fr-generale(at)lists(dot)postgresql(dot)org |
Subject: | You will need to rewrite or cast the expression |
Date: | 2019-07-12 09:07:20 |
Message-ID: | 20190712110720.Horde.MRyM-UNqbS_-B96oFP25dQ1@messagerie.c-s.fr |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-fr-generale |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tumasgiu Rossini | 2019-07-12 09:19:03 | Re: You will need to rewrite or cast the expression |
Previous Message | Wilfried Roset | 2019-07-10 15:56:06 | OVH Job Posting |