Horton
Menu

Thread

  1. Using function returning multiple values in a select

    Lele Gaifax <lele@metapensiero.it> — 2015-10-24T17:56:47Z

    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.

  2. Re: Using function returning multiple values in a select

    Adrian Klaver <adrian.klaver@aklaver.com> — 2015-10-24T18:37:16Z

    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

  3. Re: Using function returning multiple values in a select

    Lele Gaifax <lele@metapensiero.it> — 2015-10-24T19:37:57Z

    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.

  4. Re: Using function returning multiple values in a select

    Adrian Klaver <adrian.klaver@aklaver.com> — 2015-10-24T22:34:01Z

    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