integer to little endian conversion - Mailing list pgsql-general

Hi all,

I've been looking for a while now to solve my problem.
I'd like to store an integer (and other things) in a bytea field of a
table from a trigger function.
The integer needs to be inserted in it's binary representation:
1 -> \x01\x00\x00\x00
256 -> \x00\x01\x00\x00

(which would be E'\\001\\000\\000\\000' and E'\\000\\001\\000\\000')

Since I did not find any functions in the documentation I'm writing my
own function to do this in plpgsql. (attached below)

This works for many values and fails for many values.  The reason is
that chr(integer) works fine for me till 127 (chr(127) -> '\177') but
from 128 and onwards it returns useless results. chr(128) -> '\302\200'

If anyone has any idea on how to proceed that would be greatly
appreciated.

For the record, chr(integer) works just how it's supposed to, I realize
I'll need something along the lines of
    IF v_temp = 0 -> E'\\000'
    ELSIF v_temp < 128 -> chr(v_temp)
    ELSE ...

It's the ... I'm looking for. (or some function that can replace that
entire IF block, or even better, the entire function)

Thanks in advance,
Maarten


CREATE OR REPLACE FUNCTION utils.int_littleendian(v_number integer)
  RETURNS bytea AS
$BODY$
DECLARE
    v_textresult bytea;
    v_temp int;
    v_int int;
    v_i int = 4;
BEGIN
    v_int = v_number;
    v_textresult = '';
    WHILE(v_i > 0) LOOP
        v_temp := v_int%256;
        v_int := v_int - v_temp;
        v_int := v_int / 256;
        IF v_temp = 0 THEN
            v_textresult = v_textresult || E'\\000';
        ELSE
            v_textresult = v_textresult || chr(v_temp);
        END IF;
        v_i := v_i - 1;
    END LOOP;
    return v_textresult;
END;

$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;