我在stackoverflow上发现了一种使用ROW值处理多变量case
语句的巧妙方法。它看起来多么干净真是太棒了。。。
但是,在直接比较由2个类型为text
的表列组成的行值与由字符串文字组成的行时,我会遇到错误。
我使用了一个具有函数spTup('Deposit', '' )
的变通方法,它可以工作,但可能较慢。另一种工作方式是将字符串文字显式转换为text
,但这会造成大量视觉混乱。
问题:
- 为什么Postgres不能推断字符串文字应该被处理作为
text
类型 - 为什么Postgres可以在单元素行中推导字符串文字的类型,而不能在有2个元素的行中推导
- 我以为我已经掌握了Postgres类型的处理,但我不太了解这种情况,有人能解释吗
- 有没有其他方法可以最大限度地减少视觉混乱
我在localhost上使用Postgres10.1,在test&生产服务器。
测试设置:
create table if not exists tblTest ( SeqID serial, EventType text, EventResult text, Amt decimal );
truncate table tblTest;
insert into tblTest( EventType, EventResult, Amt )
values ( 'Withdrawal', '', 1.11 ), ('Deposit', '', 2.22 ), ('Deposit', 'succeeded', 3.33 ), ('Deposit', 'failed', 4.44 );
create or replace function spTup( p_1 text, p_2 text )
returns record as $func$
select ( p_1, p_2 );
$func$ LANGUAGE sql IMMUTABLE;
-- Runs without error (using single element tuple)
select SeqID, EventType, case ( EventType ) when ( 'Deposit' ) then Amt else 9.999 end
from tblTest;
-- ERROR: cannot compare dissimilar column types text and unknown at record column 1
select SeqID, EventType, EventResult, case ( EventType, EventResult )
when ( 'Deposit', '' ) then Amt else 9.999 end
from tblTest;
-- Runs without error -- visually the cleanest apart from using spTup function
select SeqID, EventType, EventResult, case ( EventType, EventResult )::text
when ( 'Deposit', '' )::text then Amt else 9.999 end
from tblTest;
-- Runs without error
select SeqID, EventType, EventResult, case ( EventType, EventResult )
when ( 'Deposit'::text, ''::text ) then Amt else 9.999 end
from tblTest;
select SeqID, EventType, EventResult, case ( EventType, EventResult )
when spTup( 'Deposit', '' ) then Amt else 9.999 end
from tblTest;
-- ERROR: input of anonymous composite types is not implemented
select SeqID, EventType, EventResult, case ( EventType, EventResult )
when '( "Deposit", "" )' then Amt else 9.999 end
from tblTest;
-- Just out of interest
select ( 'Deposit', '' ), ( 'Deposit'::text, ''::text );
/**
row row
(Deposit,"") (Deposit,"")
**/
select SeqID, EventType, EventResult, ( EventType, EventResult )
from tblTest;
/**
seqid eventtype eventresult row
1 Withdrawal (Withdrawal,"")
2 Deposit (Deposit,"")
3 Deposit succeeded (Deposit,succeeded)
4 Deposit failed (Deposit,failed)
**/
这似乎是您正在使用的"简单"或"切换"CASE
的限制CASE
的替代语法变体在没有显式强制转换的情况下工作:
select SeqID, EventType, EventResult
,CASE WHEN (EventType, EventResult) = ('Deposit', '') THEN amt ELSE 9.999 END
from tblTest;
只要你有一个案例要测试,这个变体甚至"最小化视觉混乱">。另外两个字符,但更易于阅读(IMHO(。不过,对于多种情况,"切换"变体似乎更可取。
不同的行为显然是由"简单"CASE
中不同的工作流程造成的。手册:
计算第一个
expression
,然后将其与WHEN
子句中的每个value
表达式进行比较,直到找到一个与之相等的表达式。
简单表达式的代码路径-值比较不太难解析数据类型-对于匿名行值则失败。感觉像是执行中的一个缺点。人们可能期望两种变体都有相同的行为,并提交错误报告。
但至少从Postgres8.4开始,这种行为就一直是这样的(在第11页中也是如此(:
db<>小提琴这里
到目前为止,可能很少有人对切换的CASE
中的非类型行值有类似的想法。
留下你的问题:
为什么Postgres可以在单元素行中推导字符串文字的类型,而不能在有2个元素的行中推导
答案:因为在Postgres中几乎所有地方计算表达式时,具有单个元素((foo)
(的行值都被简化为其单个元素(foo
(。所以这个:
CASE (eventtype) WHEN ('Deposit') THEN ...
有效地简化为:
CASE eventtype WHEN 'Deposit' THEN ...