Is it possible in postgresql 9.4.10 to make a calculated field in a query

Question:

Is it possible in postgresql 9.4.10 to make a calculated field in a query and filter by it, as I am trying to do by the rating field in HAVING (a similar error if I have this condition in WHERE ):

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 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

           ( ( rating >= p_rating_from AND rating <= p_rating_till ) OR ( p_rating_from IS NULL AND p_rating_till IS null ) ) AND

           ( pc.category_id = ANY (p_category_list) OR p_category_list IS NULL )


           HAVING  ( ( rating >= p_rating_from AND rating <= p_rating_till ) OR ( p_rating_from IS NULL AND p_rating_till IS null ) ) 


       ORDER BY p.sale_price asc
       LIMIT p_limit  OFFSET p_offset;
$function$

And got the error:

ERROR: column "rating" does not exist LINE 47: HAVING ((rating> = p_rating_from AND rating <= …

Initially, I tried to make a filter on a calculated field:

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, 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.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

           ( ( floor( p.rating_summary / NULLIF(p.rating_count,0)  ) >= p_rating_from AND floor( p.rating_summary / NULLIF(p.rating_count,0) ) <= p_rating_till ) OR ( p_rating_from IS NULL OR p_rating_till IS null ) ) AND

           ( pc.category_id = ANY (p_category_list) OR p_category_list IS NULL )
       ORDER BY p.sale_price asc
       LIMIT p_limit  OFFSET p_offset;
$function$

But the filter didn't work even though the syntax was error-free …

How will be correct ?

Answer:

Thanks, it turned out like this:

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 2 more questions: 1) By calling the function with the parameters p_rating_from = 2 and p_rating_till = 2 I get one data set By calling the function with the parameters p_rating_from = 3 and p_rating_till = 3 I get another data set But by calling the function with the parameters p_rating_from = 2 and p_rating_till = 3 I get the dataset as in the first case p_rating_from = 2 and p_rating_till = 2 How correct?

2) Is it possible to set the sorting field with a parameter? If so, how?

Scroll to Top