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(at)123india(dot)com #
> # Bioinformatics Unit (EMBNET node), ¡ URL: http://scorpius.iwarp.com #
> # Centre for DNA Fingerprinting and Diagnostics, #
> # Hyderabad, India - 500076 #
> `************************************************************************/