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.
