pg 存储过程

2019独角兽企业重金招聘Python工程师标准>>>

函数中计算某些代码允许时间

CREATE
    OR REPLACE FUNCTION public.test_time() RETURNS text LANGUAGE plpgsql AS $function$ DECLARE starttime DOUBLE PRECISION;

endtime  DOUBLE PRECISION;
bb DOUBLE PRECISION;
stime VARCHAR;

etime VARCHAR;

consttime VARCHAR;

aa INTEGER;

BEGIN 
	select extract(epoch from CURRENT_TIMESTAMP) into starttime;
	select count(1) from public.table_name into aa;
	select extract(epoch from now()) into endtime;
stime = cast(starttime as VARCHAR);
etime = cast(etime as VARCHAR);

consttime = cast((endtime-starttime) as TEXT);
RETURN consttime;
END;

$function$

LOOP

CREATE
    OR REPLACE FUNCTION public.loopIns() RETURNS TEXT LANGUAGE plpgsql AS $function$ DECLARE sum_count INTEGER;

commit_count INTEGER;

commit_has INTEGER;

BEGIN sum_count = 10000000;

commit_count = 10000;

commit_has = 0;

WHILE commit_has < sum_count LOOP 
执行一些代码
commit_has = commit_has + commit_count;
END loop;

RETURN '';
END;

$function$

生成随机数

SELECT to_char(RANDOM() * 1000000000000000000,'9999999999999999999');
to_char(小于1的随机值 ,'格式');

IF/ELSE

IF condition THEN
 
blablabla;
ELSIF condition THEN
blablabla;
ELSE 
blablabla;
END IF;

存储过程中赋值

select count(1) from tableName where a = xxx and b = xxx into pa_card_count;

调用函数和执行字符串类型拼接的sql语句

perform function();

转载于:https://my.oschina.net/u/1777956/blog/727996