integer to little endian conversion

On Tue, 2010-06-15 at 17:54 +0200, maarten wrote:

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;