Custom Postgres function not working

Question

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;

Answer 1

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:

CREATE OR REPLACE FUNCTION intarr_to_hex_string(integer[]) RETURNS text AS $$
SELECT 
 string_agg(
 lpad(to_hex(1ドル[x]),2,'0'),
 ''
 order by x
 ) 
FROM generate_subscripts(1,1ドル) x;
$$ LANGUAGE sql;

The above version expects an integer array, but you can accept text arrays with an overload:

CREATE OR REPLACE FUNCTION intarr_to_hex_string(text[]) RETURNS text AS $$
SELECT intarr_to_hex_string(1ドル::integer[])
$$ LANGUAGE sql;

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:

CREATE OR REPLACE FUNCTION raise_exception(text)
 RETURNS void LANGUAGE plpgsql AS
$BODY$ 
BEGIN 
 RAISE EXCEPTION '%', 1ドル; 
END; 
$BODY$;
CREATE OR REPLACE FUNCTION intarr_to_hex_string(integer[]) RETURNS text AS $$
SELECT 
 string_agg(lpad(
 CASE WHEN 1ドル[x] < 256 THEN
 to_hex(1ドル[x])
 ELSE
 raise_exception('Argument to intarr_to_hex_string contained value '||coalesce(1ドル[x]::text,'NULL')||', expected all values in i[] to be 0 <= i < 256. Full argument was '||quote_literal(1ドル)||'.')::text
 END,
 2, '0'),
 ''
 order by x) 
FROM generate_subscripts(1,1ドル) x;
$$ LANGUAGE sql;

• user1822: Wouldn't unnest() work just the same as generate_subscript() in this case?

Answer 2

Because retVal starts out as null,and null || something stays null. You should initialize retVal as ''.