I wrote this function that is supposed to iterate through a text array and convert each element (which is a typecasted integer) to hex, append to a text variable, then return the hex. However, I get nothing back.
Does anybody see any issues with the function that would cause it not to return anything?
CREATE OR REPLACE FUNCTION array_convert_to_hex(text[])
RETURNS text
AS
$$
DECLARE
arrInts ALIAS FOR $1;
retVal text;
BEGIN
FOR I IN array_lower(arrInts, 1)..array_upper(arrInts, 1) LOOP
retVal := retVal || lpad(cast(to_hex(arrInts[I]::integer) as varchar), 2, '0');
END LOOP;
RETURN retVal;
END;
$$
LANGUAGE plpgsql
STABLE
RETURNS NULL ON NULL INPUT;
Best Answer
Peter's quite right about the problem, of course. However, this entire function is unnecessarily slow and complex - that repeated string concatenation will be horrible for performance, and PL/PgSQL loops are best avoided when you can anyway.
You can do the same job with an ordinary SQL function using
generate_subscripts
:The above version expects an integer array, but you can accept text arrays with an overload:
Note that no sanity check is done to make sure the hex string is two digits or less. You should really add a check where you use a CASE statement to execute a PL/PgSQL function that raises an error if there's bad input. Here's a complete example: