I have a function written in PL/Python. It is a database function that runs in Python, which is permitted because of a procedural language installed via:
CREATE PROCEDURAL LANGUAGE 'plpythonu' HANDLER plpython_call_handler
(I found a nice trick, to allow non-admin users permission to run, by using a unique name, though it has not much to do with my question, I'm sure some of you will wonder how I am doing this, so below is the answer)
CREATE TRUSTED PROCEDURAL LANGUAGE 'plpythonu2' HANDLER plpython_call_handler
GRANT USAGE ON LANGUAGE plpythonu2 TO admin;
Now to the question at hand, my "hack" above works for me, but if I want to use Amazon's RDS service, I cannot install languages, and PL/Python is not available. SQL however, is.
Therefore, I need help translating the following function, written in Python into pure SQL.
CREATE OR REPLACE FUNCTION "public"."human_readable_bits" (
"b" bigint = 0
)
RETURNS varchar AS
$body$
import math
if b:
exponent = math.floor(math.log(b)/math.log(1024))
val = b/pow(1024, math.floor(exponent))
val = round(val*2)/2 -- This rounds to the nearest HALF (X.5) B, Kb, Mb, Gb, etc.
return "%.2f %s" % (val, ('B','Kb','Mb','Gb','Tb','Pb','Eb','Zb','Yb')[int(exponent)])
else:
return "0 Gb"
$body$
LANGUAGE 'plpythonu2'
VOLATILE
RETURNS NULL ON NULL INPUT
SECURITY INVOKER
COST 100;
This function allows me to perform queries such as:
=> SELECT human_readable_bits(3285824466906);
human_readable_bits
---------------------
3.00 Tb
(1 row)
OR
=> SELECT human_readable_bits(5920466906);
human_readable_bits
---------------------
5.50 Gb
(1 row)
Also, as a side-note/secondary question, after I created the function, when I look at the DDL, it has a line in it that says "SECURITY INVOKER," does anyone know what that means/does?
A conversion for a plain PLPGSQL function would be:
CREATE OR REPLACE FUNCTION public.human_readable_bits(b NUMERIC)
RETURNS VARCHAR AS
$BODY$
declare
exponent integer;
val float;
arr varchar[];
sz VARCHAR(10);
result varchar(20);
BEGIN
if b is null or b = 0 then
return '0 B';
end if;
if b < 1024 then
return b::varchar || ' Bits';
end if;
arr := ARRAY['B','Kb','Mb','Gb','Tb','Pb','Eb','Zb','Yb'];
exponent := floor( log(b) / log(1024));
val := b/power(1024,exponent);
val := round(val*2)/2;
sz := arr[trunc(floor(log(b) / log(1024)))];
if strpos(val::varchar,'.') > 0 then
result := substr(val::varchar, 1, strpos(val::varchar,'.')-1);
result := result || '.' || rpad( substr(val::varchar, strpos(val::varchar,'.')+1), 2, '0' ) || ' ' || sz;
else
result := val::varchar || '.00 ' || sz;
end if;
return result;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;
As a result of this function:
select human_readable_bits(328582446690656456434534453) hrb0,
human_readable_bits(3285824466906) hrb1,
human_readable_bits(5920466906) hrb2,
human_readable_bits(1024) hrb3,
human_readable_bits(512) hrb4,
human_readable_bits(null) hrb5;
Would result in:
hrb0 hrb1 hrb2 hrb3 hrb4 hrb5
272.00 Zb 3.00 Gb 5.50 Mb 1.00 B 512 Bits 0 B
As per your side question the answer can easily be found at the Create Function Documentation
SECURITY INVOKER indicates that the function is to be executed with the privileges of the user that calls it. That is the default. SECURITY DEFINER specifies that the function is to be executed with the privileges of the user that created it.