嗯,多谢,楼上 得出的结果20.38,想分别截取小数点的前后的数值,保留到 DECLARE val int4[]; 怎么写?
SELECT SUBSTR(CAST(ROUND(1223.00/60.00,2) AS VARCHAR(10)),1,strpos(CAST(ROUND(1223.00/60.00,2) AS VARCHAR(10)),'.')-1), SUBSTR(CAST(ROUND(1223.00/60.00,2) AS VARCHAR(10)),strpos(CAST(ROUND(1223.00/60.00,2) AS VARCHAR(10)),'.')+1,2),
怎么是text类型,可否是int4类型,或是varchar
iihero=# select round(1223*1.0/60) as a, round(100*(round(1223*1.0/60, 2) - (1223/60)), 0) as b; a | b ----+---- 20 | 38 (1 row)
CREATE OR REPLACE FUNCTION proc_temp_playbill() --(begin_date ,end_date ,playlist_id) RETURNS int4 AS $BODY$ DECLARE iRes INT4; DECLARE var varchar; DECLARE bg varchar; DECLARE eg varchar; DECLARE ag varchar; BEGIN var := round(1223.0/60,2)::varchar; bg := '2010-05-25 9:53'; eg := '2010-05-25 12:10'; raise notice '----------bg=% eg=%',bg,eg; --ag:= age('2010-05-25 9:53', timestamp '2010-05-25 12:53')::varchar; ag:= age(bg, timestamp eg)::varchar; --为何用varchar变量代替语句就不执行了??? raise notice '----------ag=%',ag; if substr(ag,1,1)::varchar='-' then raise notice '-'; end if; ires := 99999; RETURN ires; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; ALTER FUNCTION proc_temp_playbill() OWNER TO mposi;select * from proc_temp_playbill();
?column?
---------------------
20.3833333333333333
(1 row)
csdn=#
round
-------
20.38
(1 row)
csdn=#
得出的结果20.38,想分别截取小数点的前后的数值,保留到 DECLARE val int4[];
怎么写?
SUBSTR(CAST(ROUND(1223.00/60.00,2) AS VARCHAR(10)),1,strpos(CAST(ROUND(1223.00/60.00,2) AS VARCHAR(10)),'.')-1),
SUBSTR(CAST(ROUND(1223.00/60.00,2) AS VARCHAR(10)),strpos(CAST(ROUND(1223.00/60.00,2) AS VARCHAR(10)),'.')+1,2),
a | b
----+----
20 | 38
(1 row)
RETURNS int4 AS
$BODY$
DECLARE iRes INT4;
DECLARE var varchar;
DECLARE bg varchar;
DECLARE eg varchar;
DECLARE ag varchar;
BEGIN
var := round(1223.0/60,2)::varchar;
bg := '2010-05-25 9:53';
eg := '2010-05-25 12:10';
raise notice '----------bg=% eg=%',bg,eg;
--ag:= age('2010-05-25 9:53', timestamp '2010-05-25 12:53')::varchar;
ag:= age(bg, timestamp eg)::varchar; --为何用varchar变量代替语句就不执行了???
raise notice '----------ag=%',ag;
if substr(ag,1,1)::varchar='-' then
raise notice '-';
end if; ires := 99999;
RETURN ires;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION proc_temp_playbill() OWNER TO mposi;select * from proc_temp_playbill();