-- messages.sql: the cases, run against master (89829354de1) and three -- wordings of the new ereport() in coerce_type(): -- V0 as posted: message + error position -- V1 without the error position -- V2 position + errhint -- How the error for a non-literal unknown reads to a user, in the places it -- can be raised. Each case prints the full error: message, LINE with the -- cursor, and DETAIL/HINT when there is one. \set ON_ERROR_STOP off \set VERBOSITY default \set SHOW_CONTEXT errors \pset footer off create table t (a text, b text, c int); insert into t values ('x', 'y', 1); \echo '--- M1 the case jian raised: the result column is resolved to text' select cast(NULL::text as unknown); \echo '--- M2 same, from a column' select cast(a as unknown) from t; \echo '--- M3 one bad expression among several columns' select a, c, cast(b as unknown) as bad, c + 1 from t; \echo '--- M4 a multi-line CREATE VIEW, the bad expression on line 4' create view v as select a, c, cast(b as unknown) as bad from t; \echo '--- M5 the user asks for the conversion explicitly' select cast(a as unknown)::int from t; \echo '--- M6 as a function argument' select length(cast(a as unknown)) from t; \echo '--- M7 in a comparison' select 1 from t where cast(a as unknown) = 'x'; \echo '--- M8 in a UNION branch' select a from t union all select cast(b as unknown) from t; \echo '--- M9 inserted into a column' insert into t (b) select cast(a as unknown) from t; \echo '--- M10 the other way in: unknownin()' select unknownin('x')::text; \echo '--- M11 PL/pgSQL assignment' do $$ declare r int; begin r := '1'::text::unknown; end $$; \echo '--- M12 control, unchanged: a literal of type unknown' select 'x'::unknown::text; -- params.sql: parameters of type unknown are converted, before and after -- Parameters of type unknown: coerce_type() converts those through -- p_coerce_param_hook when the parser has one. A parameter declared as -- unknown in PREPARE is a fixed parameter; does it get here? \set ON_ERROR_STOP off \set VERBOSITY default \pset footer off \echo '--- P1 prepare with no types, $1 used as text' prepare p1 as select $1::text; execute p1('x'); \echo '--- P2 prepare p(unknown), $1::text' prepare p2(unknown) as select $1::text; execute p2('x'); \echo '--- P3 prepare p(unknown), $1 || text' prepare p3(unknown) as select $1 || 'a'::text; execute p3('x'); \echo '--- P4 prepare p(unknown), bare $1 in the target list' prepare p4(unknown) as select $1; execute p4('x'); \echo '--- P5 SQL function with an unknown argument' create function fu(unknown) returns text language sql as 'select $1::text'; ======== output: master CREATE TABLE INSERT 0 1 --- M1 the case jian raised: the result column is resolved to text ERROR: failed to find conversion function from unknown to text --- M2 same, from a column ERROR: failed to find conversion function from unknown to text --- M3 one bad expression among several columns ERROR: failed to find conversion function from unknown to text --- M4 a multi-line CREATE VIEW, the bad expression on line 4 ERROR: failed to find conversion function from unknown to text --- M5 the user asks for the conversion explicitly ERROR: failed to find conversion function from unknown to integer --- M6 as a function argument ERROR: failed to find conversion function from unknown to text --- M7 in a comparison ERROR: failed to find conversion function from unknown to text --- M8 in a UNION branch ERROR: failed to find conversion function from unknown to text --- M9 inserted into a column INSERT 0 1 --- M10 the other way in: unknownin() unknownin ----------- x --- M11 PL/pgSQL assignment DO --- M12 control, unchanged: a literal of type unknown text ------ x ======== output: V0 CREATE TABLE INSERT 0 1 --- M1 the case jian raised: the result column is resolved to text ERROR: cannot cast type unknown to text LINE 1: select cast(NULL::text as unknown); ^ --- M2 same, from a column ERROR: cannot cast type unknown to text LINE 1: select cast(a as unknown) from t; ^ --- M3 one bad expression among several columns ERROR: cannot cast type unknown to text LINE 1: select a, c, cast(b as unknown) as bad, c + 1 from t; ^ --- M4 a multi-line CREATE VIEW, the bad expression on line 4 ERROR: cannot cast type unknown to text LINE 4: cast(b as unknown) as bad ^ --- M5 the user asks for the conversion explicitly ERROR: cannot cast type unknown to integer LINE 1: select cast(a as unknown)::int from t; ^ --- M6 as a function argument ERROR: cannot cast type unknown to text LINE 1: select length(cast(a as unknown)) from t; ^ --- M7 in a comparison ERROR: cannot cast type unknown to text LINE 1: select 1 from t where cast(a as unknown) = 'x'; ^ --- M8 in a UNION branch ERROR: cannot cast type unknown to text --- M9 inserted into a column INSERT 0 1 --- M10 the other way in: unknownin() unknownin ----------- x --- M11 PL/pgSQL assignment DO --- M12 control, unchanged: a literal of type unknown text ------ x ======== output: V1 CREATE TABLE INSERT 0 1 --- M1 the case jian raised: the result column is resolved to text ERROR: cannot cast type unknown to text --- M2 same, from a column ERROR: cannot cast type unknown to text --- M3 one bad expression among several columns ERROR: cannot cast type unknown to text --- M4 a multi-line CREATE VIEW, the bad expression on line 4 ERROR: cannot cast type unknown to text --- M5 the user asks for the conversion explicitly ERROR: cannot cast type unknown to integer --- M6 as a function argument ERROR: cannot cast type unknown to text --- M7 in a comparison ERROR: cannot cast type unknown to text --- M8 in a UNION branch ERROR: cannot cast type unknown to text --- M9 inserted into a column INSERT 0 1 --- M10 the other way in: unknownin() unknownin ----------- x --- M11 PL/pgSQL assignment DO --- M12 control, unchanged: a literal of type unknown text ------ x ======== output: V2 CREATE TABLE INSERT 0 1 --- M1 the case jian raised: the result column is resolved to text ERROR: cannot cast type unknown to text LINE 1: select cast(NULL::text as unknown); ^ HINT: Cast the expression to the type you want directly, not to type unknown. --- M2 same, from a column ERROR: cannot cast type unknown to text LINE 1: select cast(a as unknown) from t; ^ HINT: Cast the expression to the type you want directly, not to type unknown. --- M3 one bad expression among several columns ERROR: cannot cast type unknown to text LINE 1: select a, c, cast(b as unknown) as bad, c + 1 from t; ^ HINT: Cast the expression to the type you want directly, not to type unknown. --- M4 a multi-line CREATE VIEW, the bad expression on line 4 ERROR: cannot cast type unknown to text LINE 4: cast(b as unknown) as bad ^ HINT: Cast the expression to the type you want directly, not to type unknown. --- M5 the user asks for the conversion explicitly ERROR: cannot cast type unknown to integer LINE 1: select cast(a as unknown)::int from t; ^ HINT: Cast the expression to the type you want directly, not to type unknown. --- M6 as a function argument ERROR: cannot cast type unknown to text LINE 1: select length(cast(a as unknown)) from t; ^ HINT: Cast the expression to the type you want directly, not to type unknown. --- M7 in a comparison ERROR: cannot cast type unknown to text LINE 1: select 1 from t where cast(a as unknown) = 'x'; ^ HINT: Cast the expression to the type you want directly, not to type unknown. --- M8 in a UNION branch ERROR: cannot cast type unknown to text HINT: Cast the expression to the type you want directly, not to type unknown. --- M9 inserted into a column INSERT 0 1 --- M10 the other way in: unknownin() unknownin ----------- x --- M11 PL/pgSQL assignment DO --- M12 control, unchanged: a literal of type unknown text ------ x ======== output of params.sql, patched (same on master) --- P1 prepare with no types, $1 used as text PREPARE text ------ x --- P2 prepare p(unknown), $1::text PREPARE text ------ x --- P3 prepare p(unknown), $1 || text PREPARE ?column? ---------- xa --- P4 prepare p(unknown), bare $1 in the target list PREPARE ?column? ---------- x --- P5 SQL function with an unknown argument ERROR: SQL functions cannot have arguments of type unknown