Search Postgresql Archives

integer to little endian conversion

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux