Обсуждение: 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