Re: integer to little endian conversion

Hello again,

I've found what I'm looking for.  I overlooked the binary string
functions which I stumbled upon just now.

The set_byte() function for binary strings is just what I needed.  The
function for those interested now becomes:

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 = 0;
BEGIN
    v_int = v_number;
    v_textresult = '1234';
    WHILE(v_i < 4) LOOP
        raise notice 'loop %',v_int;
        v_temp := v_int%256;
        v_int := v_int - v_temp;
        v_int := v_int / 256;
        SELECT set_byte(v_textresult,v_i,v_temp) INTO v_textresult;
        v_i := v_i + 1;
    END LOOP;
    return v_textresult;
END;

$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;

regards,
Maarten

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;
>
>
>
>