On 10/24/2015 12:37 PM, Lele Gaifax wrote:
Adrian Klaver <adrian.klaver@xxxxxxxxxxx> writes:
An actual working example that shows exactly what you want to achieve would
help. Mainly where does the base price originate?
A product has a base price (and maybe a base discount), but that may be
overridden by particular rules based on the customer, on the product type and
on the period of the year (I omitted the latter condition from my example, to
simplify).
There is a table "discounts" that for particular product, or its type, or for
a particular customer, or for a particular period, or a combination of these,
may specify either a new fixed price or a special discount.
For example, consider a product P, of type T, with a base price of 100$: I
need to be able to specify that for customer C1 the price is fixed at 90$,
while for customer C2 its price is 92$; moreover, all products of type T, in
the period from December 1st to December 24th, enjoy a discount of 10% for
everybody. It is obviously impractical to "explode" all these cases into a
flat table.
The function I mentioned is already taking all these details into account and
producing the expected results.
In the meantime, if there is no direct relation between a product and
customer discount I am not sure how you can avoid the above in the case you
show above. That being generating a discount table for all products for a
particular customer. For an order I could see the order being the relation
that connects the customer(and their discount) to the particular products on
the order.
The function is used to produce the listing of the products a customer *may*
buy in an online e-commerce, so the actual order has yet to come.
My doubt was about the better way to use that function from within the query
that produces the listing.
Well, if I am following the above correctly you have a table 'discounts'
that relates customers(and their discounts) to products. Given that then
it should be possible to do explicit joins between products and a
customer provided your function returns the product id with the
associated discounted price. The question being is '*may*' for a single
item at time or the entire list of products associated with a customer?
If it is for the entire list then SETOF might come in handy:
http://www.postgresql.org/docs/9.3/interactive/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING
40.6.1.2. RETURN NEXT and RETURN QUERY
I hope this is clearer now,
thank you,
ciao, lele.
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
