Maybe you are right.
I am currently storing these values in a separate table and was looking for
a way to optimize it.
Without trying to pretend to be an expert on this I thought that using
arrays, storing calculated values, or using materialized views in a database
all violate the rules of normalization. I thought that you do all of these
for optimization: use matviews or store calculated values when you want to
access your data very often, and use arrays when you want to access your
data within the array very rarely.
My reasoning was why to store many millions of rows in a table when I would
only need less than 1% of that frequently and the rest probably only once in
the live system (we would need to analyze that offline). The data comes in
as a single update of the array field (fast), than I put the items I will
need into a separate table with a function - admiteddly it takes time to do
that but I only need to do that once - and I will query the resulting table
many times. So it seemed logical.
Independently from my case I found a few topics about creating a rowset from
an array in the archive which suggested that its not only me who could
benefit from an easier way to manipulate this datatype.
regards,
Balázs
-----Original Message-----
From: Tino Wildenhain [mailto:tino@wildenhain.de]
Sent: 2006. január 3. 18:59
To: SunWuKung
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] generic way to retrieve array as rowset
SunWuKung schrieb:
> Thank you both, I will make good use of this.
>
> On a side note: isn't it a pity this has to be so difficult?
Well the pity is your data model - or the lack of it ;))
If redesign is possible, you probably want to change from
array to real connected table.
*wink* ;)
Tino