Re: Using function returning multiple values in a select

On 10/24/2015 10:56 AM, Lele Gaifax wrote:
> Hi all,
>
> I have a function that returns multiple values, computing them from the input
> parameters, and I need to use it within an existing query.
>
> Say I have the following table:
>
> CREATE TABLE products (
> id SERIAL NOT NULL,
> description VARCHAR(64),
> PRIMARY KEY (id)
> )
>
> and the following simplified function:
>
> CREATE OR REPLACE FUNCTION price_and_discount(
> in p_idproduct integer, p_idcustomer integer,
> out price numeric(15,4), out discount numeric(7,4)
> ) AS $$
> BEGIN
> -- determine price and discount for the given customer,
> -- just compute some value to exemplify
> price := 123.456 * p_idproduct;
> discount := 12.34;
> RETURN;
> END;
> $$ LANGUAGE plpgsql
>
> I need to get the listing of products with their price and discount for a
> given customer, and the following query seems doing the right thing:
>
> SELECT p.id, p.description, pad.price, pad.discount
> FROM products AS p
> LEFT JOIN price_and_discount(p.id, 123) AS pad ON true
>
> that effectively outputs:
>
> # SELECT p.id, p.description, pad.price, pad.discount
> FROM products AS p
> LEFT JOIN price_and_discount(p.id, 123) AS pad ON true;
> id | description | price | discount
> ----+-------------+---------+----------
> 1 | Foo | 123.456 | 12.34
> 2 | Bar | 246.912 | 12.34
>
> I used this kind of statement a lot under Firebird, years ago, even if I were
> warned on its mailing list that it worked "by chance".
>
> In this particular case
>
> SELECT p.id, p.description, pad.price, pad.discount
> FROM products AS p, price_and_discount(p.id, 123) AS pad
>
> does produce the same result. However, I usually try to avoid the latter
> syntax, that suggests a cross-product between the FROM-clauses.
>
> Which alternative would you recommend?

An actual working example that shows exactly what you want to achieve
would help. Mainly where does the base price originate? 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.

>
> Thanks in advance,
> ciao, lele.
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com