Обсуждение: Help with Arrays and References

Hi,

   I'm starting on PostgreSQL so please be patient; =]

   I'm building a book library database in which I have a table
TBL_FORMAT which keeps basic information on various file-formats (ie 1,
PDF, Portable Document Format) and a TBL_BOOKS which I want to reference
TBL_FORMAT. But the column Format inside TBL_BOOKS is an array, as a
book may exist in one or more file types. My doubt is: If I make
something like

   CREATE TABLE "tbl_books" (
   "id_book" int4 DEFAULT nextval('TBL_Books_ID_Books_Seq'::text) NOT NULL,
   "format" _int4 NOT NULL REFERENCES TBL_Format ON_UPDATE CASCADE,
   ...

   will the CASCADE action update my TBL_Books (Format) keeping the
other values in the array or will it erase all and set the new Format value?

   Thanks,

William

--
Perl combines all of the worst aspects of BASIC, C and line noise.
                 -- Keith Packard

On Mon, 24 Jun 2002, William N. Zanatta wrote:

> Hi,
>
>    I'm starting on PostgreSQL so please be patient; =]
>
>    I'm building a book library database in which I have a table
> TBL_FORMAT which keeps basic information on various file-formats (ie 1,
> PDF, Portable Document Format) and a TBL_BOOKS which I want to reference
> TBL_FORMAT. But the column Format inside TBL_BOOKS is an array, as a
> book may exist in one or more file types. My doubt is: If I make
> something like
>
>    CREATE TABLE "tbl_books" (
>    "id_book" int4 DEFAULT nextval('TBL_Books_ID_Books_Seq'::text) NOT NULL,
>    "format" _int4 NOT NULL REFERENCES TBL_Format ON_UPDATE CASCADE,
>    ...
>
>    will the CASCADE action update my TBL_Books (Format) keeping the
> other values in the array or will it erase all and set the new Format value?

That shouldn't even be legal assuming that tbl_format's key is an int.
The two types must be comparable which isn't true of int4 and _int4.
You're probably better off with a details table with the book's id and
format's id and appropriate references.

Dear Group,
Could you please tell me how to create an object in
postgreSQL. (i.e) like in oracle, can i do with this
syntax..

Create type <object_Name> as Object (.....);

I tried the above syntax in postgreSQL, it gives
parser error at "as"

How do we define the object in postgreSQL and also
hold it an array in the table.

Awaiting for earliest response

Thanks in advance..

Suraj

=====
PIL/BMB/SDU/DK


=====
PIL/BMB/SDU/DK

__________________________________________________
Do You Yahoo!?
Yahoo! - Official partner of 2002 FIFA World Cup
http://fifaworldcup.yahoo.com

Somebody called 'Stephan Szabo' tried to say something! Take a look:
> On Mon, 24 Jun 2002, William N. Zanatta wrote:
>
>
>>Hi,
>>
>>   I'm starting on PostgreSQL so please be patient; =]
>>
>>   I'm building a book library database in which I have a table
>>TBL_FORMAT which keeps basic information on various file-formats (ie 1,
>>PDF, Portable Document Format) and a TBL_BOOKS which I want to reference
>>TBL_FORMAT. But the column Format inside TBL_BOOKS is an array, as a
>>book may exist in one or more file types. My doubt is: If I make
>>something like
>>
>>   CREATE TABLE "tbl_books" (
>>   "id_book" int4 DEFAULT nextval('TBL_Books_ID_Books_Seq'::text) NOT NULL,
>>   "format" _int4 NOT NULL REFERENCES TBL_Format ON_UPDATE CASCADE,
>>   ...
>>
>>   will the CASCADE action update my TBL_Books (Format) keeping the
>>other values in the array or will it erase all and set the new Format value?
>
>
> That shouldn't even be legal assuming that tbl_format's key is an int.
> The two types must be comparable which isn't true of int4 and _int4.
> You're probably better off with a details table with the book's id and
> format's id and appropriate references.
>

Thanks Stephan,


   I wanted to avoid repeated lines of information just because of the
'format' column. Maybe I could create a specific data type for that but
as it will be just a tiny small database, I will not spend my time.
   Anyway how would you do it? The idea is:

     - I have an electronic library.
     - I have books in more than one file type (ie. pdf and zip)
     - I want to keep it in the database, thus I'd have something like:

       -=[ table books ]=-
       bookName  |  format

       mybook    | array(1, 3)

       -=[ table format ]=-
       id_format |  format | description
         1       |  pdf    | Portable Document Format
         2       |  txt    | ASCII RAW Text
         3       |  zip    | ZIP Compressed File

    I would like to hear you as I used to work with mysql which is not
too amazing as postgresql. The more I read the documentation, the more I
get crazy, PostgreSQL is very powerful but, my poor concepts doesn't let
me bring it to my life. =]

    Thanks,

William

--
Perl combines all of the worst aspects of BASIC, C and line noise.
                 -- Keith Packard

On Tue, 25 Jun 2002, William N. Zanatta wrote:

> Somebody called 'Stephan Szabo' tried to say something! Take a look:
> > On Mon, 24 Jun 2002, William N. Zanatta wrote:
> >>   I'm building a book library database in which I have a table
> >>TBL_FORMAT which keeps basic information on various file-formats (ie 1,
> >>PDF, Portable Document Format) and a TBL_BOOKS which I want to reference
> >>TBL_FORMAT. But the column Format inside TBL_BOOKS is an array, as a
> >>book may exist in one or more file types. My doubt is: If I make
> >>something like
> >>
> >>   CREATE TABLE "tbl_books" (
> >>   "id_book" int4 DEFAULT nextval('TBL_Books_ID_Books_Seq'::text) NOT NULL,
> >>   "format" _int4 NOT NULL REFERENCES TBL_Format ON_UPDATE CASCADE,
> >>   ...
> >>
> >>   will the CASCADE action update my TBL_Books (Format) keeping the
> >>other values in the array or will it erase all and set the new Format value?
> >
> >
> > That shouldn't even be legal assuming that tbl_format's key is an int.
> > The two types must be comparable which isn't true of int4 and _int4.
> > You're probably better off with a details table with the book's id and
> > format's id and appropriate references.
> >
>
> Thanks Stephan,
>
>
>    I wanted to avoid repeated lines of information just because of the
> 'format' column. Maybe I could create a specific data type for that but
> as it will be just a tiny small database, I will not spend my time.
>    Anyway how would you do it? The idea is:
>
>      - I have an electronic library.
>      - I have books in more than one file type (ie. pdf and zip)
>      - I want to keep it in the database, thus I'd have something like:
>
>        -=[ table books ]=-
>        bookName  |  format
>
>        mybook    | array(1, 3)
>
>        -=[ table format ]=-
>        id_format |  format | description
>          1       |  pdf    | Portable Document Format
>          2       |  txt    | ASCII RAW Text
>          3       |  zip    | ZIP Compressed File
>

    Generally speaking, I suggest a new table like:
create table book_format(
 id_book int4 references tbl_books on update cascade
  on delete cascade
 id_format int4 references tbl_format on update cascade
  on delete cascade
);

That might have data like:
id_book | id_format
 1      | 1
 1      | 3
to say that book 1 comes in pdf and zip.