Pl/PgsSQL array

Ben-Nes Yonatan wrote:

Hi all,

Can anyone tell me if Pl/PgSQL can support a multi dimensional array (of
up to 5 levels top I guess) with about 100,000 values?
and does it stress the system too much?

I can't imagine it being wonderful - you probably want a different
procedural language. Or a different approach come to think of it - can I
ask what you are using 5D arrays with 100,000 values for?

--
Richard Huxton
Archonet Ltd

Richard Huxton wrote:

Ben-Nes Yonatan wrote:

Hi all,

Can anyone tell me if Pl/PgSQL can support a multi dimensional array
(of up to 5 levels top I guess) with about 100,000 values?
and does it stress the system too much?

I can't imagine it being wonderful - you probably want a different
procedural language. Or a different approach come to think of it - can I
ask what you are using 5D arrays with 100,000 values for?

Indeed you can my good sir :)

Im going to insert into one table a list of about 3.5 million products
each day, thouse products also contain data about their own categories.

Now I receive all of this data every day and I need to delete all of the
previous data each time and insert the new one.
In order to create the category table (tree table) I want to create an
array out of the categories information at the products and then insert
it into the the categories table (the tree :)).

I also thought about retrieving the data from the products table and
inserting it first into a temporary table and from that information
create the categories table (tree :)), but I prefer to save the next
selects from that table and just create an array.

Maybe you or anyone else got a better idea?

Thanks again!

Ben-Nes Yonatan wrote:

Richard Huxton wrote:

Can anyone tell me if Pl/PgSQL can support a multi dimensional array
(of up to 5 levels top I guess) with about 100,000 values?
and does it stress the system too much?

I can't imagine it being wonderful - you probably want a different
procedural language. Or a different approach come to think of it - can
I ask what you are using 5D arrays with 100,000 values for?

Indeed you can my good sir :)

Im going to insert into one table a list of about 3.5 million products
each day, thouse products also contain data about their own categories.

Now I receive all of this data every day and I need to delete all of the
previous data each time and insert the new one.
In order to create the category table (tree table) I want to create an
array out of the categories information at the products and then insert
it into the the categories table (the tree :)).

I would be tempted use an external perl script to receive the data and
insert it into the database, building the categories tree as it did so.

--
Richard Huxton
Archonet Ltd

Richard Huxton wrote:

Ben-Nes Yonatan wrote:

Richard Huxton wrote:

Can anyone tell me if Pl/PgSQL can support a multi dimensional array
(of up to 5 levels top I guess) with about 100,000 values?
and does it stress the system too much?

I can't imagine it being wonderful - you probably want a different
procedural language. Or a different approach come to think of it -
can I ask what you are using 5D arrays with 100,000 values for?

Indeed you can my good sir :)

Im going to insert into one table a list of about 3.5 million products
each day, thouse products also contain data about their own categories.

Now I receive all of this data every day and I need to delete all of
the previous data each time and insert the new one.
In order to create the category table (tree table) I want to create an
array out of the categories information at the products and then
insert it into the the categories table (the tree :)).

I would be tempted use an external perl script to receive the data and
insert it into the database, building the categories tree as it did so.

--
Richard Huxton
Archonet Ltd

Sadly im not familiar with Perl...

On Thu, 2005-07-07 at 08:30 +0100, Richard Huxton wrote:

Ben-Nes Yonatan wrote:

Richard Huxton wrote:

Can anyone tell me if Pl/PgSQL can support a multi dimensional array
(of up to 5 levels top I guess) with about 100,000 values?
and does it stress the system too much?

I can't imagine it being wonderful - you probably want a different
procedural language. Or a different approach come to think of it - can
I ask what you are using 5D arrays with 100,000 values for?

Indeed you can my good sir :)

Im going to insert into one table a list of about 3.5 million products
each day, thouse products also contain data about their own categories.

Now I receive all of this data every day and I need to delete all of the
previous data each time and insert the new one.
In order to create the category table (tree table) I want to create an
array out of the categories information at the products and then insert
it into the the categories table (the tree :)).

I would be tempted use an external perl script to receive the data and
insert it into the database, building the categories tree as it did so.

"Perl and multidimensional arrays" eq "Yikes!"

I just beat down a three dimensional hash yesterday, and it was a
nasty blood fest. I almost considered rewriting the application in
PHP but figured I had enough invested to just push through. I
think my Programming perl by O'Reilly is getting too old in the
tooth, and I don't write in perl often enough to know the CPAN
modules very well.

If this is a fresh project, I personally would use PHP it dead
easy to manipulate arrays. As a mater of fact almost everything
I do in PHP uses arrays, it's the main reason I use it. The main
problem I have run into is the 8MB data limit, but that can be
changed and if run as a serial processor, it may not be a problem.