Using function returning multiple values in a select

[Lele Gaifax <lele@xxxxxxxxxxxxxxx>]

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@xxxxxxxxxxxxxxx  |                 -- Fortunato Depero, 1929.



-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general