
--
-- PLPGSQL
--
-- Testing various scenarios where plans will not be cached. 
-- MPP-16204

--
-- ************************************************************
-- * Repro with drop table inside a function
-- *    - Multiple executions should not raise an error 
-- ************************************************************
--
drop table if exists cache_tab cascade;

drop function if exists cache_test();

create function cache_test() returns void as
$$
begin
	drop table if exists cache_tab;
	create table cache_tab (id int) distributed randomly;
	insert into cache_tab values (1);
end;
$$ language plpgsql;

select cache_test();

-- following should not fail. 
select cache_test();

drop table cache_tab;

drop function cache_test();

--
-- ************************************************************
-- * Repro with SELECT .. INTO inside a function
-- *    - Multiple executions should not raise an error 
-- ************************************************************
--

create table cache_tab
(
	c1 int, 
	c2 text
) partition by range(c1)
(default partition def,
partition part1 start (int '1') end (int '10')); 

insert into cache_tab values(1, 2);
insert into cache_tab values(2, 3);
insert into cache_tab values(3, 4);

create function cache_test(id int) returns int as $$
declare 
	v_int int;
begin 
	select c1 from cache_tab where c2 = id INTO v_int; 
	return v_int;
end;
$$ language plpgsql;

select * from cache_test(1);

alter table cache_tab split default partition 
start (11) inclusive
end (20) exclusive 
into (partition part2, partition def);

-- following should not fail. 
select * from cache_test(2);

drop table cache_tab cascade;

drop function cache_test(int);

--
-- ************************************************************
-- * Repro with drop table between executions 
-- *    - Multiple executions should not raise an error 
-- ************************************************************
--

create table cache_tab (
	id int, 
	name varchar(50)
) distributed randomly;

insert into cache_tab values(1, 'abc');
insert into cache_tab values(2, 'def');

drop function if exists cache_test(var int);

create function cache_test(var int) returns varchar as $$
declare 
	v_name varchar(20) DEFAULT 'zzzz';
begin 
	select name from cache_tab into v_name where id = var; 
	return v_name;
end;
$$ language plpgsql;

select * from cache_test(1);

drop table  if exists cache_tab;

create table cache_tab (
	id int, 
	name varchar(50)
) distributed randomly;

-- following should not fail. 
select * from cache_test(2);

drop table cache_tab;

drop function cache_test(int);

--
-- ************************************************************
-- * Repro with return cursor 
-- *    - Multiple executions should not raise an error 
-- ************************************************************
--

create table cache_tab (
	c1 int, 
	c2 int, 
	c3 int
) distributed randomly;
 
insert into cache_tab values(1, 2, 100);

insert into cache_tab values(2, 3, 200);

insert into cache_tab values(3, 4, 300);

create function cache_test(refcursor) returns refcursor as $$
begin 
	open $1 for select * from cache_tab;
	return $1;
end;
$$
language plpgsql;

begin;
select cache_test('refcursor');
fetch all in refcursor;
commit;

drop table if exists  cache_tab; 

create table cache_tab (
	c1 int, 
	c2 int, 
	c3 int
) distributed randomly;


begin;
select cache_test('refcursor');
fetch all in refcursor;
commit;

drop table cache_tab; 

drop function cache_test(refcursor);

--
-- ************************************************************
-- * Repro with fetch cursor
-- *    - Multiple executions should not raise an error 
-- ************************************************************
--

create table cache_tab(
	c1 int, 
	c2 int, 
	c3 int
) distributed randomly; 

insert into cache_tab values(1, 2, 100);

insert into cache_tab values(1, 2, 100);

create function cache_test(var int) returns int as $$
declare 
	cur refcursor;
	res int;
	total_res int default 0;
begin
	open cur for select c2 from cache_tab where c1 = var; 
	fetch cur into res;
	while res is not null 
	loop
  		total_res := total_res + res;
  		fetch cur into res;
	end loop;
	return total_res;
end;
$$ language plpgsql;

select cache_test(1); 

drop table  if exists cache_tab; 

create table cache_tab(
	c1 int, 
	c2 int, 
	c3 int
) distributed randomly; 

insert into cache_tab values(1, 2, 100);

-- following should not fail
select cache_test(1);

drop table cache_tab; 

drop function cache_test(int);

--
-- ************************************************************
-- * Repro with function planned on segments
-- *    - plan should be cached
-- ************************************************************
--

create table cache_tab
(
	c1 int, 
	c2 text
) partition by range(c1)
(default partition def,
partition part1 start (int '1') end (int '10')); 

insert into cache_tab values(1, 'foo1');

create function cache_test() returns int as $$
declare 
	v_temp varchar(10); 
begin 
	select into v_temp hastriggers from pg_tables;
	if v_temp is not null
	then
		return 1;
	else
		return 0;
	end if;
end;
$$ language plpgsql;
   
select * from cache_tab where c1 = cache_test(); 

select * from cache_tab where c1 = cache_test();

drop table cache_tab;

drop function cache_test();

--
-- ************************************************************
-- * Block statement execution
-- *    - Multiple executions should not raise an error 
-- ************************************************************
--

create table cache_tab
(
	c1 int, 
	c2 int
) partition by range(c2)
(default partition def,
partition part1 start (int '1') end (int '10')); 

insert into cache_tab values(0, 100);

insert into cache_tab values(1, 100);

insert into cache_tab values(2, 100);

create function cache_test(key int) returns int as $$ 
declare 
	v_int int;
	v_res int default 0;
begin 
loop
	select c1 from cache_tab into v_int where c2 = key;
	if found then
		return v_res;
	end if;
	if v_int != 0 then
		v_res := v_res + v_int;
	end if;
end loop;
end;
$$ language plpgsql; 

select cache_test(100); 

alter table cache_tab split default partition 
start (11) inclusive
end (20) exclusive 
into (partition part2, partition def);

select cache_test(100); 

drop table cache_tab cascade; 

drop function cache_test(int);

--
-- ************************************************************
-- * Repro with PERFORM 
-- *    - Multiple executions should not raise an error 
-- ************************************************************
--

create table cache_tab
(
	c1 int, 
	c2 int
) partition by range(c2)
(default partition def,
partition part1 start (int '1') end (int '10')); 

insert into cache_tab values(1, 100);

insert into cache_tab values(2, 100);

insert into cache_tab values(3, 100);

create function cache_test() returns void AS $$
begin
	perform c1 from cache_tab;
end;
$$ language plpgsql;

select cache_test();

drop table if exists  cache_tab; 

create table cache_tab
(
	c1 int, 
	c2 int
) partition by range(c2)
(default partition def,
partition part1 start (int '1') end (int '10')); 

insert into cache_tab values(1, 100);

select cache_test();

drop table cache_tab; 

drop function cache_test();

--
-- ************************************************************
-- * example with table functions
-- *
-- ************************************************************
--

create table cache_tab
(
	a int, 
	b int
) distributed randomly;

insert into cache_tab values(1, 100);
insert into cache_tab values(2, 200);

drop function if exists get_cache_tab();

create function get_cache_tab() returns setof cache_tab as $$
	select * from cache_tab where a = 1;
$$ language sql;

create function cache_test() returns setof integer as
$$
declare 
	r integer;
begin
	for r IN select a from get_cache_tab() 
	loop
		return next r;
	end loop;
	return;
end
$$ language plpgsql;

select cache_test();

drop function if exists get_cache_tab();

create function get_cache_tab() returns setof cache_tab as $$
  select * from cache_tab where a = 2;
$$ language sql;


-- plan should not be cached, returns different results
select cache_test();

drop table cache_tab cascade;

drop function if exists get_cache_tab() ;

drop function if exists cache_test();

-- ************************************************************
-- * an example with CTAS
-- * 	multiple executions should not raise an error
-- ************************************************************
create table cache_tab(id int, data text);

insert into cache_tab values(1, 'abc');

insert into cache_tab values(2, 'abc');

insert into cache_tab values(3, 'abcd');

create or replace function cache_test() returns void as 
$$
begin
	drop table if exists cache_temp;
	create table cache_temp as select * from cache_tab distributed randomly;
end
$$ language plpgsql; 

select cache_test();

select * from cache_temp;

drop table cache_tab;

create table cache_tab(id int, data text);

insert into cache_tab values(1, 'abcde');

-- should not raise an error 
select cache_test();

-- should return 1 row 
select * from cache_temp;

drop table cache_tab cascade; 

drop function cache_test();

drop table cache_temp;

--
-- ************************************************************
-- * recursive functions
-- ************************************************************
--

-- start_matchsubs
--
-- m|ERROR:\s+relation with OID \d+ does not exist|
-- s|ERROR:\s+relation with OID \d+ does not exist|ERROR: relation with OID DUMMY does not exist|
--
-- end_matchsubs


create table cache_tab(c1 int, c2 int) distributed randomly; 

drop function if exists cache_test(count int); 
create function cache_test(count int) returns int as $$
begin 
	if $1 <= 0 then 
		return $1;
  	else 
		insert into cache_tab values($1, $1);
  	end if; 
  	return cache_test($1-1);
end; 
$$ language plpgsql;

select cache_test(5);

drop table if exists cache_tab;

create table cache_tab(c1 int, c2 int) distributed randomly; 

select cache_test(5);

drop function cache_test(count int); 

--- another example with recursive functions 
create function cache_test(count int) returns int as $$
begin 
	if $1 <= 0 then 
		return $1;
  	else 
		drop table if exists cache_tab;
		create table cache_tab(c1 int, c2 int) distributed randomly; 
		insert into cache_tab values($1, $1);
  	end if; 
  	return cache_test($1-1);
end; 
$$ language plpgsql;

-- this will fail
select cache_test(5);

set gp_plpgsql_clear_cache_always = on;

-- this will pass
select cache_test(5);

drop table if exists cache_tab;

drop function cache_test(count int) cascade;

--
-- ************************************************************
-- * testing guc
-- ************************************************************
--

drop table if exists cache_tab cascade;

drop function if exists cache_test();

create function cache_test() returns void as
$$
begin
	drop table if exists cache_tab;
	create table cache_tab (id int) distributed randomly;
	insert into cache_tab values (1);
end;
$$ language plpgsql;


BEGIN;

select cache_test();

-- this will fail
select cache_test();

COMMIT; 

set gp_plpgsql_clear_cache_always = on;

select cache_test();


drop table cache_tab;

drop function cache_test();

--
-- ************************************************************
-- * testing guc
-- ************************************************************
--
drop table if exists cache_tab cascade;

drop function if exists cache_test(); 

set gp_plpgsql_clear_cache_always = off;
create function cache_test() returns void as
$$
declare count int; 
begin
	count := 3;
	while count > 0 
	loop
		drop table if exists cache_tab;
		create table cache_tab (id int) distributed randomly;
		insert into cache_tab values (1);
		count := count - 1;
	end loop;
end;
$$ language plpgsql;

-- this will fail
select cache_test();

set gp_plpgsql_clear_cache_always = on;

-- this will pass
select cache_test();

set gp_plpgsql_clear_cache_always = off;

drop function cache_test();

create function cache_test() returns void as
$$
declare count int; 
begin
	count := 3;
	while count > 0 
	loop
		set gp_plpgsql_clear_cache_always = on;
		drop table if exists cache_tab;
		create table cache_tab (id int) distributed randomly;
		insert into cache_tab values (1);
		count := count - 1;
	end loop;
end;
$$ language plpgsql;

select cache_test();

drop table cache_tab;

drop function cache_test();