Re: Using function returning multiple values in a select

On 10/24/2015 12:37 PM, Lele Gaifax wrote:
> Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> 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(dot)klaver(at)aklaver(dot)com