to_number() (psql)
使用sql输入并执行创建
CREATE OR REPLACE FUNCTION public.to_number(input character varying)
RETURNS numeric
LANGUAGE plpgsql
AS $function$
BEGIN
RETURN CAST(input as NUMERIC);
END
$function$
;
nvl() (psql)
类似于java 方法重载,注意参数的不同类型以及返回类型
CREATE OR REPLACE FUNCTION nvl(CHARACTER VARYING, CHARACTER VARYING)
RETURNS CHARACTER VARYING AS
$BODY$
DECLARE
BEGIN
IF $1 IS NOT NULL THEN
RETURN $1;
ELSE
RETURN $2;
END IF;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
------------------------
CREATE OR REPLACE FUNCTION nvl(BOOLEAN, BOOLEAN)
RETURNS BOOLEAN AS
$BODY$
DECLARE
BEGIN
IF $1 IS NOT NULL THEN
RETURN $1;
ELSE
RETURN $2;
END IF;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
------------------------
CREATE OR REPLACE FUNCTION nvl(INTERVAL, INTERVAL)
RETURNS INTERVAL AS
$BODY$
DECLARE
BEGIN
IF $1 IS NOT NULL THEN
RETURN $1;
ELSE
RETURN $2;
END IF;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
------------------------
CREATE OR REPLACE FUNCTION nvl(TIME WITHOUT TIME ZONE, TIME WITHOUT TIME ZONE)
RETURNS TIME WITHOUT TIME ZONE AS
$BODY$
DECLARE
BEGIN
IF $1 IS NOT NULL THEN
RETURN $1;
ELSE
RETURN $2;
END IF;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
------------------------
CREATE OR REPLACE FUNCTION nvl(DATE, DATE)
RETURNS DATE AS
$BODY$
DECLARE
BEGIN
IF $1 IS NOT NULL THEN
RETURN $1;
ELSE
RETURN $2;
END IF;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
------------------------
CREATE OR REPLACE FUNCTION nvl(NUMERIC, NUMERIC)
RETURNS NUMERIC AS
$BODY$
DECLARE
BEGIN
IF $1 IS NOT NULL THEN
RETURN $1;
ELSE
RETURN $2;
END IF;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
------------------------
CREATE OR REPLACE FUNCTION nvl(TIMESTAMP WITHOUT TIME ZONE, TIMESTAMP WITHOUT TIME ZONE)
RETURNS TIMESTAMP WITHOUT TIME ZONE AS
$BODY$
DECLARE
BEGIN
IF $1 IS NOT NULL THEN
RETURN $1;
ELSE
RETURN $2;
END IF;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
------------------------
CREATE OR REPLACE FUNCTION nvl(TEXT, TEXT)
RETURNS TEXT AS
$BODY$
DECLARE
BEGIN
IF $1 IS NOT NULL THEN
RETURN $1;
ELSE
RETURN $2;
END IF;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;