I've never used arrays in Postgres (or any other RDBMS for that matter),
but it would seem that the best way to do it would be to create a function
that would take the quantity as an argument, and loop through all the
elements of the array and return the value that most closely matches
the specified quantity. So your query would look something like this:
select product_id, DiscountPrice(quantity), FROM ....
But, here is where you run into a small problem: Arrays are not hashes,
they have elements 1,2,3,4,5... So you have to use a multi-dimensioned
array, which is going to be even more complicated. I'd recommend the
multiple entries in a separate pricing table, it seems much simpler (yeah,
I know, it's not as cool... :-)
At 03:45 AM 1/23/00, Jeff Davis wrote:
>I am making a database to store product information. I
>am going to need to use quantity pricing breaks
>(different prices for buying a lot).
>
>I was first thinking that I should use a seperate
>table and, for each product have several rows, which
>is how many people do that. But that method seems
>somewhat awkward, so I thought an array type would be
>a better solution.
>
>However, when I thought about it some more and read
>more documentation, I found that the select statements
>I would need would be nearly impossible. For example:
>
>I want to get the price for quantity X so i need to
>select the greatest value in the array that is less
>than X as the quantity break value that applies to
>them.
>
>Could someone help me find a solution involving
>arrays?
>
>Thanks,
>Jeff Davis
>__________________________________________________
>Do You Yahoo!?
>Talk to your friends online with Yahoo! Messenger.
>http://im.yahoo.com
>
>************