Passing two parameter values of the same type
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 and numeric types
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;
Date parameters Parameters with time zone
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;
date function
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 type and integer type (compared to the above, repeated)
-- 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 type and text type
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 type and text type
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 type and numeric type
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;
test
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;