Received: from nts1.rtp.epa.gov (nts1.rtp.epa.gov [134.67.208.208]) by hub.org (8.10.1/8.10.1) with ESMTP id e9CENKr25450 for ; Thu, 12 Oct 2000 10:23:20 -0400 (EDT) Received: from epamail.epa.gov (IDENT:mvs@wolfpack.rtpnc.epa.gov [134.67.139.59]) by nts1.rtp.epa.gov (8.9.3/8.9.3) with ESMTP id KAA17034; Thu, 12 Oct 2000 10:26:27 -0400 (EDT) Message-ID: <39E5C914.F764BA47@epamail.epa.gov> Date: Thu, 12 Oct 2000 10:22:12 -0400 From: Mark Volpe X-Mailer: Mozilla 4.75 [en] (X11; U; Linux 2.2.14-15mdk i586) X-Accept-Language: en MIME-Version: 1.0 To: Indraneel Majumdar CC: pgsql-sql@postgresql.org Subject: Re: dynamic object creation References: Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-Archive-Number: 200010/114 X-Sequence-Number: 1776 You may want to think about creating your table like this (for example): CREATE TABLE data ( key text, field_type char, value text ); CREATE UNIQUE INDEX data_key ON data(key, field_type, value); So this way each "record" takes up several rows in the table, and each "field" can take up as many rows as you need. A table like this, with two columns being arrays: key | field1 | field2 ------------------------- a | [x,y,z] | [a,d,f] b | [m,n] | (NULL) Can be represented like this instead: key | field_type | value ------------------------- a | 1 | x a | 1 | y a | 1 | z a | 2 | a a | 2 | d a | 2 | f b | 1 | m b | 1 | n I'm not sure what your data looks like, but I hope this helps. Mark Indraneel Majumdar wrote: > > Hi, > > I'm not sure if the subject line has been proper. I have this following > problem which I hope PostgreSQL can handle. > > I'm converting a complex flatfile where records are arranged serially. > some fields are as 'n' times repeating blocks of multiple lines. Some > subfields within these are also 'n' time repeating blocks of multiple > lines. So in my main table I do not know (until at run time) how many > fields to create (same for any sub tables). How can I do this dynamically? > > I tried using arrays, but retrieval from that is causing some problems. I > have already checked the array utilities in the contrib section and have > extended the operator list for other types (I'll send the file to it's > original author so that he may include it if he wishes). > > I think there must be some object-oriented way of doing this without > creating too many keys. or are keys the only and best method? Using this > is causing a performance hit. If it's any help, what I'm trying to convert > are biological databases distributed in 'SRS' flatfile format from > ftp.ebi.ac.uk/pub/databases/ > > Thank you, > Indraneel > > /************************************************************************. > # Indraneel Majumdar ¡ E-mail: indraneel@123india.com # > # Bioinformatics Unit (EMBNET node), ¡ URL: http://scorpius.iwarp.com # > # Centre for DNA Fingerprinting and Diagnostics, # > # Hyderabad, India - 500076 # > `************************************************************************/