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; > > > > -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
