Using PLPGSQL function result in a join query

Question

I am developing an application its database is Postgres 9.5 I am having the following plpgsql function

 CREATE OR REPLACE FUNCTION public.getall_available_products(
     IN start_day_id integer,
     IN end_day_id integer)
       RETURNS TABLE(id integer) AS
  $BODY$
  SELECT product_id As id
       FROM   product_days
       WHERE  available > 0
        AND    days_id BETWEEN start_day_id AND end_day_id         
$BODY$
LANGUAGE sql VOLATILE

I need to use the result of the above function in a join query in another plpgsql function

 CREATE OR REPLACE FUNCTION public.get_available_product_details(
    IN start_day_id integer,
    IN end_day_id integer)
     RETURNS SETOF record AS
 $BODY$declare
 begin    
    SELECT pd.days_id As pd_days_id, pd.id AS p_id, pd.name AS p_name
   FROM  product p JOIN product_days pd   
     Using(id)
     WHERE  pd.id in 
     Select * from 
      //here I need to use the result of the getall_available_products 
      //function
  end;         
 $BODY$
 LANGUAGE plpgsql VOLATILE

How should I use the result of the first function in the second function? where I specify with comments.

Did not try ... WHERE pd.id in Select * from getall_available_products('arg1','arg2') ? — Vivek S.
I have tried I got ` syntax error at or near "Select" LINE 13: Select * from ` — Siavosh

clemens clemens · Accepted Answer · 2018-01-08T05:50:25

You can select from set / table returning functions like tables or views. In your case:

SELECT pd.days_id As pd_days_id, pd.id AS p_id, pd.name AS p_name
FROM  product p JOIN product_days pd USING(id)
WHERE  pd.id IN
    (SELECT a.id FROM public.getall_available_products(start_day_id, end_day_id) a);

You may even join with functions:

SELECT pd.days_id As pd_days_id, pd.id AS p_id, pd.name AS p_name
FROM  product p JOIN product_days pd USING(id) 
    JOIN public.getall_available_products(start_day_id, end_day_id) a ON pd.id = a.id;

This should give the same result.

Note: If you want pass column values as function arguments you should take a look at the relatively new keyword LATERAL.

Using PLPGSQL function result in a join query

1 Answers