Re: dynamic object creation

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                                              #
> `************************************************************************/