Thread: Using function returning multiple values in a select
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?
Thanks in advance,
ciao, lele.
--
nickname: Lele Gaifax | Quando vivrò di quello che ho pensato ieri
real: Emanuele Gaifas | comincerò ad aver paura di chi mi copia.
lele@metapensiero.it | -- Fortunato Depero, 1929.
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.klaver@aklaver.com
Adrian Klaver <adrian.klaver@aklaver.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. I hope this is clearer now, thank you, ciao, lele. -- nickname: Lele Gaifax | Quando vivrò di quello che ho pensato ieri real: Emanuele Gaifas | comincerò ad aver paura di chi mi copia. lele@metapensiero.it | -- Fortunato Depero, 1929.
On 10/24/2015 12:37 PM, Lele Gaifax wrote: > Adrian Klaver <adrian.klaver@aklaver.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.klaver@aklaver.com