CODEWITHSUNDEEP
sqlpostgresql
avi
avi

Reputation: 1846

How to filter records with existing function?

I have function as follows (my function is more complex but assume this is the function):

CREATE OR REPLACE FUNCTION A(id integer)
  RETURNS a_type AS
$BODY$
declare
    a_type ROW;     
begin
    FOR ROW IN
                    select * from table_1 where x_id=id;
    LOOP
            return next row;
    end loop;
    return;
end;    
$BODY$
  LANGUAGE plpgsql VOLATILE

I want to change the function to recive 2 parmeters. the 2nd one is a number which tells how many months to go back from today:

I can change the function to:

CREATE OR REPLACE FUNCTION A(id integer, months integer)
  RETURNS a_type AS
$BODY$
declare
    a_type ROW;     
begin
           if months>0 then:
    FOR ROW IN
            select * from table_1 where x_id=id and date >  CURRENT_DATE - INTERVAL '3 months';
    LOOP
            return next row;
    end loop;
    return;
else:
    FOR ROW IN
            select * from table_1;
    LOOP
            return next row;
    end loop;
    return;
end;
end;    
$BODY$
  LANGUAGE plpgsql VOLATILE

However this solution forces me to write the whole query with loop twice.

Is there a way to do that without writing the same code twice?

Upvotes: 0

Views: 38

Answers (1)

user330315
user330315

Reputation:

Something like this:

create or replace function a(p_id integer, p_months integer)
  returns setof table_1
as
$$
  select *
  from table_1
  where x_id = p_id
   and (   coalesce(p_months,0) <= 0 or 
           some_date > current_date - interval '1' month * p_months );
$$
language sql;

If p_months is passed as null or 0 then the first part of the or will be true and the other condition will not be evaluated (it will probably, because SQL does no short-circuiting, but it won't matter). If p_months is passed as something different, the first part of the or will be false and thus the second part needs to be true in order to return the correct result.

Note that I also changed the function from a PL/pgSQL function to a plain SQL function. There is no need to do a slow and inefficient row-by-row processing in a loop here (unless you obfuscated your code so much that you left out the part where you actually do something in that loop)

Upvotes: 1

Related Questions