Horton

Thread

  1. convert int to bytea

    ohp@pyrenet.fr — 2007-11-29T13:44:58Z

    Hi all,

I'm trying to write a trigger that converts integer to bytea.
My schema is like this:

Create table xx (
id int,
....
...
data bytea);

the first 3 bytes of data are the binary representation of id
(id  is extracted from data by  the application)

I can occur that id change in that case data has to change too!

select 124::bytea doesn't work

Is there an other way? (preferabily simple :)

Regards
-- 
Olivier PRENANT        	        Tel: +33-5-61-50-97-00 (Work)
15, Chemin des Monges                +33-5-61-50-97-01 (Fax)
31190 AUTERIVE                       +33-6-07-63-80-64 (GSM)
FRANCE                          Email: ohp@pyrenet.fr
------------------------------------------------------------------------------
Make your life a dream, make your dream a reality. (St Exupery)

  2. Re: convert int to bytea

    Greg Stark <stark@enterprisedb.com> — 2007-11-29T14:34:57Z

    <ohp@pyrenet.fr> writes:

> select 124::bytea doesn't work
>
> Is there an other way? (preferabily simple :)

This kind of question would be more appropriate on pgsql-general.

What do you want the resulting bytea to look like?

-- 
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com
  Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training!

  3. Re: convert int to bytea

    ohp@pyrenet.fr — 2007-11-29T14:45:10Z

    Hi Gregory
On Thu, 29 Nov 2007, Gregory Stark wrote:

> Date: Thu, 29 Nov 2007 14:34:57 +0000
> From: Gregory Stark <stark@enterprisedb.com>
> To: ohp@pyrenet.fr
> Cc: pgsql-hackers list <pgsql-hackers@postgresql.org>
> Subject: Re: convert int to bytea
>
> <ohp@pyrenet.fr> writes:
>
> > select 124::bytea doesn't work
> >
> > Is there an other way? (preferabily simple :)
>
> This kind of question would be more appropriate on pgsql-general.
>
I know, I should subscribe :)

> What do you want the resulting bytea to look like?
>
example : id = 9 , bytea = '\000\000\011' IIRC
>

-- 
Olivier PRENANT        	        Tel: +33-5-61-50-97-00 (Work)
15, Chemin des Monges                +33-5-61-50-97-01 (Fax)
31190 AUTERIVE                       +33-6-07-63-80-64 (GSM)
FRANCE                          Email: ohp@pyrenet.fr
------------------------------------------------------------------------------
Make your life a dream, make your dream a reality. (St Exupery)

  4. Re: convert int to bytea

    Douglas McNaught <doug@mcnaught.org> — 2007-11-29T16:00:44Z

    On 11/29/07, ohp@pyrenet.fr <ohp@pyrenet.fr> wrote:

> On Thu, 29 Nov 2007, Gregory Stark wrote:
>
> > What do you want the resulting bytea to look like?
> >
> example : id = 9 , bytea = '\000\000\011' IIRC

What do you expect to happen when server and client are differently-endian?

-Doug

  5. Re: convert int to bytea

    Usama Dar <munir.usama@gmail.com> — 2007-11-29T16:08:08Z

    Does it matter if you have written an explicit cast for int to bytea?

On Nov 29, 2007 9:00 PM, Douglas McNaught <doug@mcnaught.org> wrote:

> On 11/29/07, ohp@pyrenet.fr <ohp@pyrenet.fr> wrote:
>
> > On Thu, 29 Nov 2007, Gregory Stark wrote:
> >
> > > What do you want the resulting bytea to look like?
> > >
> > example : id = 9 , bytea = '\000\000\011' IIRC
>
> What do you expect to happen when server and client are
> differently-endian?
>
> -Doug
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>               http://www.postgresql.org/docs/faq
>



-- 
Usama Munir Dar http://linkedin.com/in/usamadar
Consultant Architect
Cell:+92 321 5020666
Skype: usamadar

  6. Re: convert int to bytea

    Boszormenyi Zoltan <zb@cybertec.at> — 2007-11-29T16:35:31Z

    Hi,

please don't top post to someone who didn't used this convention
in answering you. It's impolite. I edited the mail a bit to return sanity.

> On Nov 29, 2007 9:00 PM, Douglas McNaught <doug@mcnaught.org 
> <mailto:doug@mcnaught.org>> wrote:
>
>     On 11/29/07, ohp@pyrenet.fr <mailto:ohp@pyrenet.fr>
>     <ohp@pyrenet.fr <mailto:ohp@pyrenet.fr>> wrote:
>
>     > On Thu, 29 Nov 2007, Gregory Stark wrote:
>     >
>     > > What do you want the resulting bytea to look like?
>     > >
>     > example : id = 9 , bytea = '\000\000\011' IIRC
>
>     What do you expect to happen when server and client are
>     differently-endian?
>
>     -Doug
>

Usama Dar írta:
 > Does it matter if you have written an explicit cast for int to bytea?
 >

You don't know what't endianness is, do you?
Say, you have a number: 0x12345678.
This is stored differently depending on the endianness.

Big-endian (like Sparc, Motorola, etc):
0x12 0x34 0x56 0x78

Little-endian (Intel-compatibles, etc):
0x78 0x56 0x34 0x12

So, how do you want your number to come out as a byte array?
Since a bytea is a sequence of bytes as stored in memory,
you may have different meaning for an int->bytea conversion.

It's your homework to look up what's "network order" is. :-)
But it would give you consistent answer no matter
what CPU your server uses.

-- 
----------------------------------
Zoltán Böszörményi
Cybertec Schönig & Schönig GmbH
http://www.postgresql.at/

  7. Re: convert int to bytea

    Usama Dar <munir.usama@gmail.com> — 2007-11-29T16:41:29Z

    On Nov 29, 2007 9:35 PM, Zoltan Boszormenyi <zb@cybertec.at> wrote:

> Hi,
>
> please don't top post to someone who didn't used this convention
> in answering you. It's impolite. I edited the mail a bit to return sanity.
>
> > On Nov 29, 2007 9:00 PM, Douglas McNaught <doug@mcnaught.org
> > <mailto:doug@mcnaught.org>> wrote:
> >
> >     On 11/29/07, ohp@pyrenet.fr <mailto:ohp@pyrenet.fr>
> >     <ohp@pyrenet.fr <mailto:ohp@pyrenet.fr>> wrote:
> >
> >     > On Thu, 29 Nov 2007, Gregory Stark wrote:
> >     >
> >     > > What do you want the resulting bytea to look like?
> >     > >
> >     > example : id = 9 , bytea = '\000\000\011' IIRC
> >
> >     What do you expect to happen when server and client are
> >     differently-endian?
> >
> >     -Doug
> >
>
> Usama Dar írta:
>  > Does it matter if you have written an explicit cast for int to bytea?
>  >
>
> You don't know what't endianness is, do you?
> Say, you have a number: 0x12345678.
> This is stored differently depending on the endianness.
>
> Big-endian (like Sparc, Motorola, etc):
> 0x12 0x34 0x56 0x78
>
> Little-endian (Intel-compatibles, etc):
> 0x78 0x56 0x34 0x12
>
> So, how do you want your number to come out as a byte array?
> Since a bytea is a sequence of bytes as stored in memory,
> you may have different meaning for an int->bytea conversion.
>
> It's your homework to look up what's "network order" is. :-)
> But it would give you consistent answer no matter
> what CPU your server uses.
>

1) i wasn't aware people are sensitive  to top email reply vs inline,
apologies if it offended you

2) i know what a byte order is , i just thought your interface i.e. libpq
would convert it to the local byte  order.

>
>
> --
> ----------------------------------
> Zoltán Böszörményi
> Cybertec Schönig & Schönig GmbH
> http://www.postgresql.at/
>
>
>


-- 
Usama Munir Dar http://linkedin.com/in/usamadar
Consultant Architect
Cell:+92 321 5020666
Skype: usamadar

  8. Re: convert int to bytea

    Andrew Dunstan <andrew@dunslane.net> — 2007-11-29T16:55:50Z

    
Usama Dar wrote:
>
>
>
> 2) i know what a byte order is , i just thought your interface i.e. 
> libpq would convert it to the local byte  order.
>

You haven't thought this through. Data traveling over libpq is still 
text, not binary, in most cases, so byte order is irrelevant at that 
time. The translation to a bytea (if possible) would be done after the 
data was already on the server and the int was in its native, 
architecture dependent form.

The short answer is thus still that there is no sane consistent cast 
from int to bytea.

If you want a bytea then it's really up to you to contruct the byte array.

cheers

andrew