Re: Converting BYTEA from/to BIGINT

2010/7/26 Vincenzo Romano <vincenzo(dot)romano(at)notorand(dot)it>:
> 2010/7/26 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:
>> Hello
>>
>> you can try
>>
>> postgres=# select int8send(256);
>>      int8send
>> --------------------
>>  \x0000000000000100
>> (1 row)
>>
>> for converting from bytea to int8 you need a custom function - probably in C :(
>
> int8send?

It seems I have the solution.

CREATE OR REPLACE FUNCTION bytea_to_int8( ba BYTEA, OUT res INT8 )
LANGUAGE plpgsql STRICT
AS $BODY$
DECLARE
i INT;
nb INT;
k CONSTANT INT8 := INT8( 2^(64-8-1) );
b8 CONSTANT INT8 := 2^8;
BEGIN
res := 0;
IF length( ba ) > 7 THEN
nb = 6; -- all but last one
ELSE
nb = length( ba ); -- all of them
END IF;
FOR i IN 0 .. nb LOOP
res := b8*res + get_byte( ba,i );
END LOOP;
IF length( ba ) < 8 THEN
RETURN;
END IF;
IF res > k-1 THEN
res := (res-k)*-b8;
ELSE
res := res*b8;
END IF;
res := res + get_byte( ba,7 );
END;
$BODY$;

This function should get at most 8 bytes from a BYTEA and pack *all
bits* into a BIGINT (aka INT8) to
be returned.
The function is somehow more "verbose" than needed in order to try to
make it clearer how it works and to make
it more general.
The first 7 bytes are packed into the "first" 7 bytes of an INT8 in a
straightforward way.
Some maths is needed to pack the 8th byte without overflowing the INT8
(unsigned INT8 are not available
at the moment).
Of course a C language functions could have been faster, cleaner and
... less interesting to me.

Now, why doing this?
I am using a plain SEQUENCE to create a (kind of) "session ID". That
is simple but predictable.
The idea is to use this function in conjunction with encrypt (from
pgcrypto) and the blowfish algorithm
to make that sequence numbers somehow unpredictable.
I'm pretty sure there are better (or at least easier) solutions out
there, but there needs to be also some fun
in here.
As usual, any hint is appreciated.
Flames can go to /dev/null :-)

--
Vincenzo Romano
NotOrAnd Information Technologies
NON QVIETIS MARIBVS NAVTA PERITVS