自定义函数 pgsql

1：经纬度计算距离

DECLARE

   dx float8;

   dy float8;

   b float8;

   Lx float8;

   Ly float8;

begin

  dx := lng1 - lng2;

  dy := lat1 - lat2;

  b := (lat1 + lat2) * 0.5; 

  Lx := RADIANS(dx) * 6367000.0 * COS(RADIANS(b));

  Ly := 6367000.0 * RADIANS(dy);

  RETURN SQRT(Lx * Lx + Ly * Ly);

  

END

 

2，随机生成字符串

 

declare

 chars text[] := '{0,1,2,3,4,5,6,7,8,9,A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z}';

 result text := '';

 i integer := 0;

begin

 if length < 0 then

 raise exception 'Given length cannot be less than 0';

 end if;

 for i in 1..length loop

 result := result || chars[1+random()*(array_length(chars, 1)-1)];

 end loop;

 return result;

end;

 

3：时间转换

DECLARE  

    result BIGINT;  

BEGIN  

    result := extract(epoch FROM date_trunc('minute', to_timestamp(datestr, 'YYYY-MM-DD HH24:MI:SS')));  

    RETURN result * 1000;  

END;