Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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;
share|improve this question
add comment

2 Answers

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;
share|improve this answer
    
Wouldn't unnest() work just the same as generate_subscript() in this case? –  a_horse_with_no_name Dec 31 '12 at 10:09
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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