if else postgres procedure with parameters

Question

i have query like this

CREATE  FUNCTION getVisitChartByClient(date_from DATE, date_to DATE, statusname TEXT, club_uuid TEXT) 

RETURNS TABLE(date date, entries bigint) AS $$
DECLARE
   ids UUID[];
BEGIN
ids = string_to_array(club_uuid,',');

        RETURN QUERY  SELECT d.date, count(v.id) AS entries
            FROM (SELECT  i::date AS date
                  FROM generate_series(date_from, date_to, '1 day'::interval) i
                 ) d 
                 LEFT  JOIN (
                     SELECT v.created_at, v.id FROM visit AS v 
                     LEFT JOIN club AS c ON v.club_id= c.id
                     AND status = statusname
                     AND c.uuid = ANY(ids)
                     GROUP BY v.id
                     ) AS v

                 ON d.date = v.created_at::date
            GROUP BY d.date
            Order By d.date ASC;


END;
$$ LANGUAGE plpgsql;

But sometimes I don't want to pass statusname or club id's, how can I use IF ELSE statement inside the query, or how I can to create the query and then execute?

Vao Tsun · Accepted Answer · 2017-05-19 10:48:44Z

2

try:

AND status = coalesce(statusname,status)

and

AND case when club_uuid is null then then true else c.uuid = ANY(ids) end

so:

    RETURN QUERY  SELECT d.date, count(v.id) AS entries
        FROM (SELECT  i::date AS date
              FROM generate_series(date_from, date_to, '1 day'::interval) i
             ) d 
             LEFT  JOIN (
                 SELECT v.created_at, v.id FROM visit AS v 
                 LEFT JOIN club AS c ON v.club_id= c.id
                 AND status = coalesce(statusname,status)
                 AND case when club_uuid is null then then true else c.uuid = ANY(ids) end
                 GROUP BY v.id
                 ) AS v

             ON d.date = v.created_at::date
        GROUP BY d.date
        Order By d.date ASC;

of course you will need to pass NULLs to function instead of "normal" values

answered May 19, 2017 at 10:48

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

if else postgres procedure with parameters

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related