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