Converting PL/Python function to pure PostgreSQL SQL - need help with translation

Data and Databases
1

I’m looking for assistance in changing a database function from PL/Python to plain SQL because I’d like to use it with Amazon RDS where I can’t install custom languages.

Here’s the Python function I currently have that converts byte values into a human-friendly format:

CREATE OR REPLACE FUNCTION format_byte_size(bytes_value bigint DEFAULT 0)
RETURNS text AS
$$
import math
if bytes_value > 0:
    power = math.floor(math.log(bytes_value) / math.log(1024))
    size = bytes_value / pow(1024, power)
    size = round(size * 2) / 2
    units = ['B', 'KB', 'MB', 'GB', 'TB', 'PB', 'EB', 'ZB', 'YB']
    return "%.2f %s" % (size, units[int(power)])
else:
    return "0 GB"
$$
LANGUAGE plpythonu;

This function gives me outputs like:

  • format_byte_size(2847293847593) results in 2.50 TB
  • format_byte_size(8473625891) results in 7.50 GB

The function effectively calculates the correct unit and rounds to the nearest half. However, I need guidance on replicating the logarithmic calculations and array handling in pure PostgreSQL SQL.

Additionally, could someone explain what the SECURITY INVOKER clause signifies in function definitions?

2

Yes, you can achieve this conversion using pure SQL. Utilize the log(bytes_value) / log(1024) to determine the power, then compute the size using bytes_value / pow(1024, power). For half-rounding, you can apply round(size * 2) / 2. Manage the units by implementing a CASE statement that correlates the power value to the appropriate suffix. Regarding the SECURITY INVOKER clause, it allows the function to execute with the privileges of the caller rather than the owner’s. This aspect is crucial for access control, especially in environments with multiple users. From my experience, SQL-based byte formatting functions can outperform their PL/Python counterparts due to reduced language switching overhead.

3

use floor() with your log calculation to get the unit index. Try floor(log(bytes_value)/log(1024)) then divide by 1024^power. rds handles this fine since they’re all native postgres functions.

4

Interesting challenge! Have you tried CASE statements with the log() function for unit conversion? Also, what’s behind the half-rounding requirement? that’s pretty unusual and might change which SQL approach works best.