Thread: Converting BYTEA from/to BIGINT
Hi all. I'd like to convert an 8-bytes BYTEA into a BIGINT and possibly vice versa. Is there any way to do it? -- Vincenzo Romano NotOrAnd Information Technologies NON QVIETIS MARIBVS NAVTA PERITVS
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 :(
Pavel
2010/7/26 Vincenzo Romano <vincenzo.romano@notorand.it>:
> Hi all.
>
> I'd like to convert an 8-bytes BYTEA into a BIGINT and possibly vice versa.
> Is there any way to do it?
>
> --
> Vincenzo Romano
> NotOrAnd Information Technologies
> NON QVIETIS MARIBVS NAVTA PERITVS
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
2010/7/26 Vincenzo Romano <vincenzo.romano@notorand.it>:
> Hi all.
>
> I'd like to convert an 8-bytes BYTEA into a BIGINT and possibly vice versa.
> Is there any way to do it?
Something like:
CREATE OR REPLACE FUNCTION bytea_to_int8( ba BYTEA, OUT res INT8 )
LANGUAGE plpgsql STRICT
AS $BODY$
DECLARE
i INT;
BEGIN
res := 0;
FOR i IN 0 .. 7 LOOP
res := 256*res + get_byte( ba,i );
END LOOP;
END;
$BODY$;
gives me back errors (ERROR: bigint out of range) because of overflow
at step no.7
--
Vincenzo Romano
NotOrAnd Information Technologies
NON QVIETIS MARIBVS NAVTA PERITVS
2010/7/26 Pavel Stehule <pavel.stehule@gmail.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? -- Vincenzo Romano NotOrAnd Information Technologies NON QVIETIS MARIBVS NAVTA PERITVS
2010/7/26 Vincenzo Romano <vincenzo.romano@notorand.it>:
> 2010/7/26 Pavel Stehule <pavel.stehule@gmail.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
Vincenzo Romano <vincenzo.romano@notorand.it> writes: > 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. I think you'd be interested into the following: http://wiki.postgresql.org/wiki/Pseudo_encrypt Regards, -- dim
2010/7/27 Dimitri Fontaine <dfontaine@hi-media.com>: > Vincenzo Romano <vincenzo.romano@notorand.it> writes: >> 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. > > I think you'd be interested into the following: > > http://wiki.postgresql.org/wiki/Pseudo_encrypt That solution has a limit I knew about: it only generates 31-bit values. I could "easily" trick it to handle 62-bit. Then I decided to play that game ... -- Vincenzo Romano NotOrAnd Information Technologies NON QVIETIS MARIBVS NAVTA PERITVS