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