传递两个类型相同的参数值
CREATE OR REPLACE FUNCTION IF(BLN boolean,INVALUE1 ANYELEMENT,INVALUE2 ANYELEMENT) RETURNS ANYELEMENT AS $$
begin
if bln=true then
-- IF pg_typeof(INVALUE1) = 'timestamp with time zone' then return inValue1::timestamp with time zone;
-- END IF;
-- IF pg_typeof(INVALUE1) = 'timestamp without time zone' then return inValue1::timestamp without time zone;
-- END IF;
-- IF pg_typeof(INVALUE1) = 'integer' then return inValue1::integer;
-- END IF;
return inValue1;
else
-- IF pg_typeof(INVALUE2) = 'timestamp with time zone' then return inValue2::timestamp with time zone;
-- END IF;
-- IF pg_typeof(INVALUE2) = 'timestamp without time zone' then return inValue2::timestamp without time zone;
-- END IF;
return inValue2;
end if;
end;
$$ LANGUAGE PLPGSQL;
CREATE OR REPLACE FUNCTION IF(BLN boolean,INVALUE1 text,INVALUE2 text) RETURNS text AS `$$`
begin
if bln=true then
return inValue1;
else
return inValue2;
end if;
end;
`$$` LANGUAGE PLPGSQL;
text和numeric类型
CREATE OR REPLACE FUNCTION IF(BLN boolean,INVALUE1 numeric,INVALUE2 text) RETURNS text AS $$
begin
if bln=true then
return inValue1;
else
return inValue2;
end if;
end;
$$ LANGUAGE PLPGSQL;
CREATE OR REPLACE FUNCTION IF(BLN boolean,INVALUE1 text,INVALUE2 numeric) RETURNS text AS $$
begin
if bln=true then
return inValue1;
else
return inValue2;
end if;
end;
$$ LANGUAGE PLPGSQL;
日期参数 带时区的参数
CREATE OR REPLACE FUNCTION IF(BLN boolean,INVALUE1 numeric,INVALUE2 timestamptz) RETURNS text AS $$
begin
if bln=true then
return inValue1;
else
return inValue2;
end if;
end;
$$ LANGUAGE PLPGSQL;
CREATE OR REPLACE FUNCTION IF(BLN boolean,INVALUE1 timestamptz,INVALUE2 numeric) RETURNS text AS $$
begin
if bln=true then
return inValue1;
else
return inValue2;
end if;
end;
$$ LANGUAGE PLPGSQL;
CREATE OR REPLACE FUNCTION IF(BLN boolean,INVALUE1 text,INVALUE2 timestamptz) RETURNS text AS $$
begin
if bln=true then
return inValue1;
else
return inValue2;
end if;
end;
$$ LANGUAGE PLPGSQL;
CREATE OR REPLACE FUNCTION IF(BLN boolean,INVALUE1 timestamptz,INVALUE2 text) RETURNS text AS $$
begin
if bln=true then
return inValue1;
else
return inValue2;
end if;
end;
$$ LANGUAGE PLPGSQL;
日期函数
CREATE OR REPLACE FUNCTION IF(BLN boolean,INVALUE1 numeric,INVALUE2 timestamp) RETURNS text AS $$
begin
if bln=true then
return inValue1;
else
return inValue2;
end if;
end;
$$ LANGUAGE PLPGSQL;
CREATE OR REPLACE FUNCTION IF(BLN boolean,INVALUE1 timestamp,INVALUE2 numeric) RETURNS text AS $$
begin
if bln=true then
return inValue1;
else
return inValue2;
end if;
end;
$$ LANGUAGE PLPGSQL;
CREATE OR REPLACE FUNCTION IF(BLN boolean,INVALUE1 text,INVALUE2 timestamp) RETURNS text AS $$
begin
if bln=true then
return inValue1;
else
return inValue2;
end if;
end;
$$ LANGUAGE PLPGSQL;
CREATE OR REPLACE FUNCTION IF(BLN boolean,INVALUE1 timestamp,INVALUE2 text) RETURNS text AS $$
begin
if bln=true then
return inValue1;
else
return inValue2;
end if;
end;
$$ LANGUAGE PLPGSQL;
bigint类型和integer类型 (和上面的相比,重复了)
-- CREATE OR REPLACE FUNCTION IF(BLN boolean,INVALUE1 bigint,INVALUE2 integer) RETURNS bigint AS $$
-- begin
-- if bln=true then
-- return inValue1;
-- else
-- return inValue2;
-- end if;
-- end;
-- $$ LANGUAGE PLPGSQL;
-- CREATE OR REPLACE FUNCTION IF(BLN boolean,INVALUE1 integer,INVALUE2 bigint) RETURNS bigint AS $$
-- begin
-- if bln=true then
-- return inValue1;
-- else
-- return inValue2;
-- end if;
-- end;
-- $$ LANGUAGE PLPGSQL;
bigint类型和text类型
CREATE OR REPLACE FUNCTION IF(BLN boolean,INVALUE1 bigint,INVALUE2 text) RETURNS text AS $$
begin
if bln=true then
return inValue1;
else
return INVALUE2;
end if;
end;
$$ LANGUAGE PLPGSQL;
CREATE OR REPLACE FUNCTION IF(BLN boolean,INVALUE1 text,INVALUE2 bigint) RETURNS text AS $$
begin
if bln=true then
return INVALUE1;
else
return INVALUE2;
end if;
end;
$$ LANGUAGE PLPGSQL;
integer类型和text类型
CREATE OR REPLACE FUNCTION IF(BLN boolean,INVALUE1 integer,INVALUE2 text) RETURNS text AS $$
begin
if bln=true then
return inValue1;
else
return INVALUE2;
end if;
end;
$$ LANGUAGE PLPGSQL;
CREATE OR REPLACE FUNCTION IF(BLN boolean,INVALUE1 text,INVALUE2 integer) RETURNS text AS $$
begin
if bln=true then
return inValue1;
else
return INVALUE2;
end if;
end;
$$ LANGUAGE PLPGSQL;
integer类型和numeric类型
CREATE OR REPLACE FUNCTION IF(BLN boolean,INVALUE1 numeric,INVALUE2 integer) RETURNS numeric AS $$
begin
if bln=true then
return inValue1;
else
return INVALUE2;
end if;
end;
$$ LANGUAGE PLPGSQL;
CREATE OR REPLACE FUNCTION IF(BLN boolean,INVALUE1 integer,INVALUE2 numeric) RETURNS numeric AS $$
begin
if bln=true then
return inValue1;
else
return INVALUE2;
end if;
end;
$$ LANGUAGE PLPGSQL;
-- CREATE OR REPLACE FUNCTION IF(BLN boolean,INVALUE1 numeric,INVALUE2 bigint) RETURNS numeric AS $$
-- begin
-- if bln=true then
-- return inValue1;
-- else
-- return INVALUE2;
-- end if;
-- end;
-- $$ LANGUAGE PLPGSQL;
-- CREATE OR REPLACE FUNCTION IF(BLN boolean,INVALUE1 bigint,INVALUE2 numeric) RETURNS numeric AS $$
-- begin
-- if bln=true then
-- return inValue1;
-- else
-- return INVALUE2;
-- end if;
-- end;
-- $$ LANGUAGE PLPGSQL;
测试
select * from t_rule;
select * from t_resource;
select "id",if("type" = 'SQL',"description","created_at") from t_rule;
select "id",if("type" = 'SQL',"created_at","updated_at") from t_rule;
select "id",if("type" = 'SQL','2023-10-07'::timestamp without time zone,'2023-02-11'::timestamp without time zone) from t_rule;
select "id",if("type" = 'SQL',1,'B') from t_rule;
select "id",if("type" = 'SQL','A',2) from t_rule;
select "id",if("type" = 'SQL','A','B') from t_rule;
select "id",if("type" = 'SQL',1,2) from t_rule;
select "id",if("type" = 'SQL',1::integer,2::integer) from t_rule;
select "id",if("type" = 'SQL','2023-10-07'::timestamp without time zone,2) from t_rule;
select "id",if("type" = 'SQL',1,'2023-02-11'::timestamp without time zone) from t_rule;
select "id",if("type" = 'SQL','2023-10-07'::timestamp with time zone,2) from t_rule;
select "id",if("type" = 'SQL',1,'2023-02-11'::timestamp with time zone) from t_rule;
select "id",if("type" = 'SQL',0.1,0.2) from t_rule;
select "id",if("type" = 'SQL',true,false) from t_rule;
-- select "id",if("type" = 'SQL',1::bigint,2::bigint) from t_rule;
-- select "id",if("type" = 'SQL',1::bigint,2) from t_rule;
-- select "id",if("type" = 'SQL',1,2::bigint) from t_rule;
select "id",if("type" = 'SQL',1::bigint,'B') from t_rule;
select "id",if("type" = 'SQL','A',2::bigint) from t_rule;
select "id",if("type" = 'SQL',1::integer,'B') from t_rule;
select "id",if("type" = 'SQL','A',2::integer) from t_rule;
-- select "id",if("type" = 'SQL',0.1,2) from t_rule;
-- select "id",if("type" = 'SQL',1,0.2) from t_rule;