1

I am trying to make this function work. It's purpose is to loop through my query and extract merchants who transacted during different months, thus I need a sliding window. Ultimately, I would like to insert those in a different table but for now I am trying to understand how to use this function:

CREATE OR REPLACE FUNCTION for_loop_through_query()
RETURNS TABLE (merchant_id varchar) AS $$
declare
yyyymmddtrbeg varchar :='';
yyyymmddtrend varchar :='';
sql1 varchar := '';
sql3 varchar := '';
sql2 varchar :=

'SELECT m.id
FROM merchants AS m
WHERE t.server_time_created_at >= ''yyyymmddtrbeg'' and t.server_time_created_at <''<yyyymmddtrend>''))';

year int := 2019;
counter_mon int := 7;
counter_ttl int := 2;

begin 
while counter_ttl > 0 loop 
    while counter_mon <=12 and counter_ttl > 0 loop

         yyyymmddtrbeg  := (select cast(year as varchar(4)) + '-' + right('0' + cast (counter_mon as varchar(2)),2) + '-01');

         yyyymmddtrend := (select case when counter_mon in (11,12) then cast(year+1 as varchar(4)) else cast(year as varchar(4)) end
            + '-' + right('0' + case when counter_mon = 12 then '02' 
                                     when counter_mon = 11 then '01' else 
            right('0' + cast (counter_mon+2 as varchar(2)),2) end + '-01'));
         sql3 := sql2;
         sql3 := replace(sql3,'<yyyymmddtrbeg>',  yyyymmddtrbeg );
         sql3 := replace(sql3,'<yyyymmddtrend>',  yyyymmddtrend );
         sql1 := sql1 + sql3;
         counter_mon  := counter_mon + 1;
         counter_ttl := counter_ttl - 1;
         if counter_ttl <> 0 
            then sql1 := sql1 + ' union all '; end if;
    end loop;
    counter_mon := 1; year  := year + 1;
end loop; 
--exec 'CREATE TABLE analytics_sandbox.frd_nofr_merchant_list AS' + sql1;
end
$$ LANGUAGE plpgsql;

select * from for_loop_through_query()

but it throws this error:

ERROR:  operator does not exist: character varying + unknown
LINE 1: SELECT (select cast(year as varchar(4)) + '-' + right('0' + ...
                                                ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.
QUERY:  SELECT (select cast(year as varchar(4)) + '-' + right('0' + cast (counter_mon as varchar(2)),2) + '-01')
CONTEXT:  PL/pgSQL function for_loop_through_query1() line 37 at assignment
SQL state: 42883

even though it creates it successfully. Any idea what I am doing wrong?

Thanks

Krissy
  • 11
  • 2

0 Answers0