Thread: integer to little endian conversion

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;

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