Reply-To: <phillips@weatherbeeta.com.au>
From: "Phillip Smith" <phillips@weatherbeeta.com.au>
To: <pgsql-sql@postgresql.org>
Subject: Re: COPY to table with array columns (Longish)
Date: Tue, 13 Jun 2006 15:29:35 +1000
Organization: Weatherbeeta P/L
Message-ID: <009801c68eaa$5bf73530$9b0014ac@ITPhil>
MIME-Version: 1.0
Content-Type: text/plain;charset="US-ASCII"
Content-Transfer-Encoding: quoted-printable
Importance: Normal
In-Reply-To: <bf05e51c0606122111m78e56216p3f0515cb5885fe1f@mail.gmail.com>

Not quite... ZKCOST and ZPRECMPL are two completely different things. =
They
have no relation except they're both stored in the SYS table in D3.

If we put it in a tree:
	SYS
	 |
	 +- ZKCOST
	 |     \- <value>
	 |
	 +- ZPRECMPL
	 |     +- <value>
	 |     +- <value>
	 |     +- <value>
	 |     \- <value>

or table:
	SYS
	+-----------+---------+---------+---------+---------+
	| ZKCOST    | <value> |         |         |         |
	| ZPRECMPL  | <value> | <value> | <value> | <value> |
	+-----------+---------+---------+---------+---------+

So other than a variable-element array, the only other way would be to
create a table with a column count equal to or greater than the maximum
amount of values (call that value 'Y') that any sys item holds then if a
particular record (eg, ZKCOST) has less values than Y, fill the rest of =
the
columns with blanks (as above).

That's what I've done at the moment, but only for 9 columns, so anything
over 9 fields will be truncated past and including field 10:
	wbau=3D# \d sys
	    Table "public.sys"
	 Column | Type | Modifiers
	--------+------+-----------
	 a0     | text | not null
	 a1     | text |
	 a2     | text |
	 a3     | text |
	 a4     | text |
	 a5     | text |
	 a6     | text |
	 a7     | text |
	 a8     | text |
	 a9     | text |
	Indexes:
	    "id" PRIMARY KEY, btree (a0)

a0 =3D primary key - eg, ZPRECMPL or ZKCOST

-----Original Message-----
From: pgsql-sql-owner@postgresql.org =
[mailto:pgsql-sql-owner@postgresql.org]
On Behalf Of Aaron Bono
Sent: Tuesday, 13 June 2006 2:12 PM
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] COPY to table with array columns (Longish)

I think two tables should suffice: ZKCOST and ZPRECMPL.

So you would have

ZKCOST
    zkcost_id,
    zkcost_value

and

ZPRECMPL
    zkcost_id,
    zprecmpl_id,
    zprecmpl_value

where zkcost_id is the primary key for ZKCOST and zkcost_id,
zprecmpl_id together are the primary key for ZPRECMPL and zkcost_id is
a foreign key from ZPRECMPL to ZKCOST.

That will work won't it?

-Aaron

On 6/12/06, Phillip Smith <phillips@weatherbeeta.com.au> wrote:

> So you're suggesting creating a child table for each SYS record? Ie, a
table called "ZPRECMPL" etc?

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq


*******************Confidentiality and Privilege Notice*******************

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments