Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2016
    Posts
    8

    Unanswered: error for array parameter in function declaration

    Hello,
    In postgresql version 9.4.10 I write a function with p_category_list integer[] DEFAULT NULL parameters :

    Code:
    CREATE OR REPLACE FUNCTION public.pd_get_product_prices(p_limit integer, p_offset integer, p_status type_productstatus DEFAULT NULL, p_sku character varying DEFAULT NULL, p_title character varying DEFAULT NULL,
    p_sale_price_from type_money DEFAULT NULL, p_sale_price_till type_money DEFAULT NULL, p_category_list integer[] DEFAULT NULL )
    
     RETURNS TABLE(id integer, title character varying, status type_productstatus, sku character varying, user_id smallint, regular_price type_money, sale_price type_money, in_stock boolean, short_description character varying, virtual boolean, rating_count integer, rating_summary integer, created_at timestamp without time zone, product_image character varying, product_categories character varying[], product_categories_id smallint[], product_tags character varying[], product_tags_id smallint[], product_attributes jsonb)
     LANGUAGE sql
    AS $function$
    SELECT p.id, p.title, p.status, p.sku, p.user_id, p.regular_price, p.sale_price, p.in_stock, p.short_description, p.virtual, p.rating_count, p.rating_summary, p.created_at,
    (select pi.image from pd_product_image as pi where pi.product_id = p.id and pi.is_main = true ) as product_image,
    ( select array_agg(c.name) from pd_category as c, pd_product_category as pd where c.id= pd.category_id and pd.product_id = p.id) as product_categories,
    ( select array_agg(c.id) from pd_category as c, pd_product_category as pd where c.id= pd.category_id and pd.product_id = p.id) as product_categories_id,
    
    ( select array_agg(t.name) from pd_tag as t, pd_product_tag as pt where t.id= pt.tag_id and pt.product_id = p.id) as product_tags,
    ( select array_agg(t.id) from pd_tag as t, pd_product_tag as pt where t.id= pt.tag_id and pt.product_id = p.id) as product_tags_id,
    
    ( select pa.attributes_data from pd_product_attribute as pa where pa.product_id = p.id) as product_attributes
    
    FROM pd_product AS p LEFT JOIN  pd_product_category AS pc ON pc.product_id = p.id
           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 ) AND
               ( p.title like p_title OR p_title IS NULL ) AND
               ( ( p.sale_price >= p_sale_price_from AND p.sale_price <= p_sale_price_till ) OR ( p_sale_price_from IS NULL OR p_sale_price_till IS null ) ) AND
               ( pc.category_id in p_category_list OR p_category_list IS NULL )
           ORDER BY p.sale_price asc
           LIMIT p_limit  OFFSET p_offset;
    $function$
    and I got error at 4th row from blow :

    Code:
    ERROR: syntax error at or near "p_category_list" LINE 24: ( pc.category_id IN p_category_list OR p_category...
    Googling I do not see why error ? If for array parameter syntax is different ?

    Which is correct correct ?

    Thanks!

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    It's not the parameter declaration, is the way you use the parameter in the query.

    You can't use IN for an array. You need to use
    Code:
    pc.category_id = any(p_category_list)
    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

  3. #3
    Join Date
    Oct 2016
    Posts
    8
    Thank you, let me to ask more 2 questions as for this function:
    1) I need to make filter on calculated fields and for this I made wrapping select for all statement and set condition on rows.rating calculated field, like :
    Code:
    CREATE OR REPLACE FUNCTION public.pd_get_product_prices(
        p_limit integer,
        p_offset integer,
        p_title character varying DEFAULT NULL,
        p_status type_productstatus DEFAULT NULL,
        p_in_stock boolean DEFAULT NULL,
        p_sku character varying DEFAULT NULL,
        p_sale_price_from type_money DEFAULT NULL,
        p_sale_price_till type_money DEFAULT NULL,
        p_rating_from integer DEFAULT NULL,
        p_rating_till integer DEFAULT NULL,
        p_category_list integer[] DEFAULT NULL
    )
    
     RETURNS TABLE(id integer, title character varying, status type_productstatus, sku character varying, user_id smallint, regular_price type_money, sale_price type_money, in_stock boolean, short_description character varying, virtual boolean, rating_count integer, rating_summary integer,
    
     rating integer,
    
     created_at timestamp without time zone, product_image character varying, product_categories character varying[], product_categories_id smallint[], product_tags character varying[], product_tags_id smallint[], product_attributes jsonb)
     LANGUAGE sql
    AS $function$
    
    select * from ( SELECT p.id, p.title, p.status, p.sku, p.user_id, p.regular_price, p.sale_price, p.in_stock, p.short_description, p.virtual, p.rating_count, p.rating_summary,
    
    CAST( floor( p.rating_summary / NULLIF(p.rating_count,0)  ) AS INTEGER ) as rating,
    
    p.created_at,
    (select pi.image from pd_product_image as pi where pi.product_id = p.id and pi.is_main = true ) as product_image,
    ( select array_agg(c.name) from pd_category as c, pd_product_category as pd where c.id= pd.category_id and pd.product_id = p.id) as product_categories,
    ( select array_agg(c.id) from pd_category as c, pd_product_category as pd where c.id= pd.category_id and pd.product_id = p.id) as product_categories_id,
    ( select array_agg(t.name) from pd_tag as t, pd_product_tag as pt where t.id= pt.tag_id and pt.product_id = p.id) as product_tags,
    ( select array_agg(t.id) from pd_tag as t, pd_product_tag as pt where t.id= pt.tag_id and pt.product_id = p.id) as product_tags_id,
    ( select pa.attributes_data from pd_product_attribute as pa where pa.product_id = p.id) as product_attributes
    
    
    FROM pd_product AS p LEFT JOIN  pd_product_category AS pc ON pc.product_id = p.id
           WHERE
               ( p.status= p_status OR p_status IS NULL ) AND
               ( p.in_stock = p_in_stock OR p_in_stock IS NULL )  AND
               ( p.sku like p_sku OR p_sku IS NULL )  AND
               ( p.title like p_title OR p_title IS NULL ) AND
               ( ( p.sale_price >= p_sale_price_from AND p.sale_price <= p_sale_price_till ) OR ( p_sale_price_from IS NULL OR p_sale_price_till IS null ) ) AND
              ( pc.category_id = ANY (p_category_list) OR p_category_list IS NULL )
    ) as rows
    
           WHERE( (   rows.rating >= p_rating_from AND rows.rating <= p_rating_till ) OR ( p_rating_from IS NULL OR p_rating_till IS null ) )
    
           ORDER BY rows.sale_price asc
           LIMIT p_limit  OFFSET p_offset;
    
    $function$
    But Calling the function with parameters p_rating_from=2 and p_rating_till=2 I get the resulting set. Calling the dunction with parameters p_rating_from=3 and p_rating_till=3 I get other resulting rows. But calling the function with parameters p_rating_from=2 and p_rating_till=3 I get result rows as in 1ts case p_rating_from=2 and p_rating_till=2.
    I do not see why wrong results? Which is the right way ?

    2) If there is possibility to set filter for the request as parameter and change it if I need?

  4. #4
    Join Date
    Oct 2016
    Posts
    8
    I fixed issue with rating field comparing, but I still search how to set in sql function order by by parameter

    I googled and find this link http://stackoverflow.com/questions/8...table-function

    I tried to make :

    Code:
    CREATE OR REPLACE FUNCTION public.pd_get_product_prices(p_limit integer, p_offset integer, p_sort character varying DEFAULT 'rating', p_sort_direction character varying DEFAULT 'asc', p_title character varying DEFAULT NULL::character varying, p_status type_productstatus DEFAULT NULL::type_productstatus, p_in_stock boolean DEFAULT NULL::boolean, p_sku character varying DEFAULT NULL::character varying, p_sale_price_from type_money DEFAULT NULL::numeric, p_sale_price_till type_money DEFAULT NULL::numeric, p_rating_from integer DEFAULT NULL::integer, p_rating_till integer DEFAULT NULL::integer, p_category_list integer[] DEFAULT NULL::integer[])
     RETURNS TABLE(id integer, title character varying, status type_productstatus, sku character varying, user_id smallint, regular_price type_money, sale_price type_money, in_stock boolean, short_description character varying, virtual boolean, rating_count integer, rating_summary integer, rating integer, created_at timestamp without time zone, main_image character varying, product_categories character varying[], product_categories_id smallint[], product_tags character varying[], product_tags_id smallint[], product_attributes jsonb)
     LANGUAGE sql
    AS $function$
    
    select * from ( SELECT p.id, p.title, p.status, p.sku, p.user_id, p.regular_price, p.sale_price, p.in_stock, p.short_description, p.virtual, p.rating_count, p.rating_summary,
    
    CAST( floor( p.rating_summary / NULLIF(p.rating_count,0)  ) AS INTEGER ) as rating,
    
    p.created_at,
    (select pi.image from pd_product_image as pi where pi.product_id = p.id and pi.is_main = true ) as main_image,
    ( select array_agg(c.name) from pd_category as c, pd_product_category as pd where c.id= pd.category_id and pd.product_id = p.id) as product_categories,
    ( select array_agg(c.id) from pd_category as c, pd_product_category as pd where c.id= pd.category_id and pd.product_id = p.id) as product_categories_id,
    ( select array_agg(t.name) from pd_tag as t, pd_product_tag as pt where t.id= pt.tag_id and pt.product_id = p.id) as product_tags,
    ( select array_agg(t.id) from pd_tag as t, pd_product_tag as pt where t.id= pt.tag_id and pt.product_id = p.id) as product_tags_id,
    ( select pa.attributes_data from pd_product_attribute as pa where pa.product_id = p.id) as product_attributes
    
    
    FROM pd_product AS p LEFT JOIN  pd_product_category AS pc ON pc.product_id = p.id
           WHERE
               ( p.status= p_status OR p_status IS NULL ) AND
               ( p.in_stock = p_in_stock OR p_in_stock IS NULL )  AND
               ( p.sku like p_sku OR p_sku IS NULL )  AND
               ( p.title like p_title OR p_title IS NULL ) AND
               ( ( p.sale_price >= p_sale_price_from AND p.sale_price <= p_sale_price_till ) OR ( p_sale_price_from IS NULL OR p_sale_price_till IS null ) ) AND
              ( pc.category_id = ANY (p_category_list) OR p_category_list IS NULL )
           GROUP BY p.id
    ) as rows
    
           WHERE( (   rows.rating >= p_rating_from AND rows.rating <= p_rating_till ) OR ( p_rating_from IS NULL OR p_rating_till IS null ) )
    
           ORDER BY
    
    
          -- Simplified to NULL if not sorting in ascending order.
          CASE WHEN p_sort_direction = 'asc' THEN
              CASE p_sort
                  -- Check for each possible value of p_sort.
                  WHEN 'rating' THEN rating
                  WHEN 'sale_price' THEN sale_price
                  WHEN 'title' THEN title
                  --- etc.
                  ELSE NULL
              END
          ELSE
              NULL
          END
          ASC,
    
          -- Same as before, but for p_sort_direction = 'desc'
          CASE WHEN p_sort_direction = 'desc' THEN
              CASE p_sort
                  WHEN 'rating' THEN rating
                  WHEN 'sale_price' THEN sale_price
                  WHEN 'title' THEN title
                  ELSE NULL
              END
          ELSE
              NULL
          END
          DESC
    
    
           LIMIT p_limit  OFFSET p_offset;
    
    $function$
    But I got error :



    Code:
    ERROR:  CASE types numeric and character varying cannot be matched
    LINE 42:               WHEN 'title' THEN title
                                             ^
    Which way is correct ?

Posting Permissions

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