How to translate this PostgreSQL function from PL/Py to PL/SQL?

Question

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?

That second query would be Mb not Gb I'm right? – Jorge Campos Feb 05 '17 at 14:05 — Jorge Campos, Feb 05 '17 at 14:05

Jorge Campos · Accepted Answer · 2017-02-05T22:21:31.807

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.

How to translate this PostgreSQL function from PL/Py to PL/SQL?

1 Answers1