Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2016
    Posts
    18
    Provided Answers: 1

    Answered: function with 3 different parameters

    Hello,
    In PostgreSQL 9.3 I wrote function to get data with 3 different parameters
    Code:
    CREATE OR REPLACE FUNCTION pd_getproductprices(p_status type_productstatus, p_sku character varying, p_title character varying) RETURNS SETOF pd_product
    
        AS $$
        SELECT * FROM pd_product AS p
           WHERE
               ( p.status= p_status OR p_status = '-' ) AND
               ( p.sku like p_sku OR p_sku = '-' )  AND
               ( p.title like p_title OR p_title = '-' )
           ORDER BY p.sale_price asc;
    $$
    LANGUAGE 'sql';
    and calling it I want to set 0 of these parameters or all 3 like:

    Code:
    select * from pd_getproductprices('A', '%pad%', '%za%')
    or only 1
    Code:
    select * from pd_getproductprices('A', '-', '-')
    or without parameters at all
    Code:
    select * from pd_getproductprices('-', '-', '-')
    The reason is that I dislike idea having several noumerous functions for all parameters combinations(actually there would be more parameters and more complex sql syntax).
    I added my type :
    Code:
    CREATE TYPE type_productstatus AS ENUM (
        'A',
        'I',
        'D',
        'P',
        '-'
    );
    It seems strange that this enum type has '-' value, but I need to make it to set value for my function.

    Questions :
    1) Is there is a better way with enum type '-' value ?

    2) This table has index on fields in this function:
    Code:
    CREATE INDEX idx_product_status_sku_title ON public.pd_product (status,sku,title);
    Would index scanning work the same(not worse) with parameter like
    Code:
               ( p.status= p_status OR p_status = '-' ) AND...
    ?

    3) Is there is better way for this ?


    Thanks!

  2. Best Answer
    Posted by shammat

    "You could specify a default value for each parameter, typically NULL. Then you can call the function using named parameters and leave out those you don't want:

    Code:
    CREATE OR REPLACE FUNCTION pd_getproductprices(
           p_status type_productstatus default null, 
           p_sku varchar default null, 
           p_title varchar default null)
       RETURNS SETOF pd_product
    AS 
    $$
      SELECT * 
      FROM pd_product AS p
      WHERE (p.status = p_status OR p_status is null)  
        AND (p.sku like p_sku OR p_sku is null)  
        AND (p.title like p_title OR p_title is null)
      ORDER BY p.sale_price asc;
    $$
    LANGUAGE sql;
    You can then do:

    Code:
    select *
    from pd_getproductprices (p_status => 'A');
    
    select *
    from pd_getproductprices (p_sku => 'AB%');
    
    select *
    from pd_getproductprices (p_title => 'Foo%', p_sku => 'AB%');
    
    select * 
    from pd_getproductprices('A', '%pad%', '%za%');
    "


  3. #2
    Join Date
    Nov 2003
    Posts
    2,988
    Provided Answers: 23
    You could specify a default value for each parameter, typically NULL. Then you can call the function using named parameters and leave out those you don't want:

    Code:
    CREATE OR REPLACE FUNCTION pd_getproductprices(
           p_status type_productstatus default null, 
           p_sku varchar default null, 
           p_title varchar default null)
       RETURNS SETOF pd_product
    AS 
    $$
      SELECT * 
      FROM pd_product AS p
      WHERE (p.status = p_status OR p_status is null)  
        AND (p.sku like p_sku OR p_sku is null)  
        AND (p.title like p_title OR p_title is null)
      ORDER BY p.sale_price asc;
    $$
    LANGUAGE sql;
    You can then do:

    Code:
    select *
    from pd_getproductprices (p_status => 'A');
    
    select *
    from pd_getproductprices (p_sku => 'AB%');
    
    select *
    from pd_getproductprices (p_title => 'Foo%', p_sku => 'AB%');
    
    select * 
    from pd_getproductprices('A', '%pad%', '%za%');
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •