You will need to rewrite or cast the expression

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

Responses

Browse pgsql-fr-generale by date

  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