From: Mark Volpe <volpe.mark@epamail.epa.gov>
To: Indraneel Majumdar <indraneel@www.cdfd.org.in>
Cc: pgsql-sql@postgresql.org
Subject: Re: dynamic object creation
Date: Thu, 12 Oct 2000 10:22:12 -0400
Message-ID: <39E5C914.F764BA47@epamail.epa.gov> (raw)
References: <Pine.SGI.3.96.1001012124609.90724C-100000@www.cdfd.org.in>
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 #
> `************************************************************************/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: volpe.mark@epamail.epa.gov, indraneel@www.cdfd.org.in
Subject: Re: dynamic object creation
In-Reply-To: <39E5C914.F764BA47@epamail.epa.gov>
References: <Pine.SGI.3.96.1001012124609.90724C-100000@www.cdfd.org.in> <39E5C914.F764BA47@epamail.epa.gov>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox