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@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
