5

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;

2 Answers 2

7

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;
1
  • Wouldn't unnest() work just the same as generate_subscript() in this case?
    – user1822
    Dec 31, 2012 at 10:09
6

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.