Postgres 仿oracle的nvl函数
Postgres 仿oracle的nvl函数
------jview@
调用方式select nvl(null,'aaa'),nvl(null,now())
以下代码只提供以上几个函数,如果需要更多类型,可按此方法增加其他数据类型的nvl函数
自定义函数
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(timestamp with time