Обсуждение: domains, case statements, functions: bug?
create domain m numeric(5,2);
create table t (c m);
create function f(t) returns m as $ select case when true then $1.c end $ language sql;
psql:/tmp/t1.sql:3: ERROR: return type mismatch in function declared to return m
DETAIL: Actual return type is numeric.
CONTEXT: SQL function "f"
create table t (c m);
create function f(t) returns m as $ select case when true then $1.c end $ language sql;
psql:/tmp/t1.sql:3: ERROR: return type mismatch in function declared to return m
DETAIL: Actual return type is numeric.
CONTEXT: SQL function "f"
On 07/08/2013 10:26 AM, Joe Van Dyk wrote: > create domain m numeric(5,2); > create table t (c m); > create function f(t) returns m as $ select case when true then $1.c end > $ language sql; > > > psql:/tmp/t1.sql:3: ERROR: return type mismatch in function declared to > return m > DETAIL: Actual return type is numeric. > CONTEXT: SQL function "f" Interesting: test=> select case when true then t.c end from t; case ------- 54.36 (1 row) test=> create function f(t) returns m as $$ select case when true then $1.c end $$ language sql; ERROR: return type mismatch in function declared to return m DETAIL: Actual return type is numeric. CONTEXT: SQL function "f" test=> create function f(t) returns numeric as $$ select case when true then $1.c end $$ language sql; CREATE FUNCTION test=> select f(t); ERROR: column "t" does not exist LINE 1: select f(t); I will admit I am at a loss. Maybe someone else can illuminate. -- Adrian Klaver adrian.klaver@gmail.com
Adrian Klaver <adrian.klaver@gmail.com> writes: > test=> create function f(t) returns m as $$ select case when true then > $1.c end $$ language sql; > ERROR: return type mismatch in function declared to return m > DETAIL: Actual return type is numeric. pg_typeof is somewhat helpful here: regression=# select pg_typeof(t.c) from t; pg_typeof ----------- m (1 row) regression=# select pg_typeof(case when true then t.c end) from t; pg_typeof ----------- numeric (1 row) The reason for this is that CASE uses select_common_type() to infer the output type, and select_common_type intentionally discriminates against domain types. The comment therein says: * If all input types are valid and exactly the same, just pick that type. * This is the only way that we will resolve the result as being a domain * type; otherwise domains are smashed to their base types for comparison. So the way to get a CASE to return a domain type is to be sure you provide an ELSE with the same result type: regression=# select pg_typeof(case when true then t.c else null::m end) from t; pg_typeof ----------- m (1 row) regards, tom lane
On 07/08/2013 06:58 PM, Tom Lane wrote: > Adrian Klaver <adrian.klaver@gmail.com> writes: >> test=> create function f(t) returns m as $$ select case when true then >> $1.c end $$ language sql; >> ERROR: return type mismatch in function declared to return m >> DETAIL: Actual return type is numeric. > > pg_typeof is somewhat helpful here: > > regression=# select pg_typeof(t.c) from t; > pg_typeof > ----------- > m > (1 row) > > regression=# select pg_typeof(case when true then t.c end) from t; > pg_typeof > ----------- > numeric > (1 row) > > The reason for this is that CASE uses select_common_type() to infer the > output type, and select_common_type intentionally discriminates against > domain types. The comment therein says: > > * If all input types are valid and exactly the same, just pick that type. > * This is the only way that we will resolve the result as being a domain > * type; otherwise domains are smashed to their base types for comparison. > > So the way to get a CASE to return a domain type is to be sure you > provide an ELSE with the same result type: > > regression=# select pg_typeof(case when true then t.c else null::m end) from t; > pg_typeof > ----------- > m > (1 row) Found out something new, thanks. So the following works: test=> CREATE OR REPLACE FUNCTION utility.f(t) RETURNS numeric LANGUAGE sql AS $function$ select case when true then $1.c else null::m end $function$ ; CREATE FUNCTION > > regards, tom lane > > -- Adrian Klaver adrian.klaver@gmail.com