create table t (i int, j int);
insert into t values (generate_series(1, 1000), generate_series( 200, 2200));

set force_parallel_mode= off;
set parallel_tuple_cost = 0;
set parallel_setup_cost = 0;
set min_parallel_table_scan_size = 0;
set min_parallel_index_scan_size = 0;
load 'auto_explain';
SET auto_explain.log_min_duration = 0;
SET auto_explain.log_nested_statements = ON;
SET auto_explain.log_analyze = true;

--simple select query from sql function
create or replace function parallel_test_sql() returns bigint as
$$ 
select count(*) from t;
$$ 
language sql PARALLEL SAFE STABLE;

select from parallel_test_sql();

--simple select query from plpgsql function
create or replace function parallel_test_plpgsql() returns int as
$$
declare
  cnt int:=0;
 begin
    select count(*) into cnt from t where j <= 10 ;
    return cnt;
 end;
 $$ language plpgsql PARALLEL SAFE  STABLE;

select from parallel_test_plpgsql();

--plpgsql function with select and insert/update/delete statements
create or replace function mixed_plpgsql() returns integer as
$$
declare cnt int:= 0;
begin
	select count(*) into cnt from t;
	update t set i = 1 where i < 1 ;
	create table t5(i int, k int);
	insert into t5 values ( 1, 0);	
	drop table t5;
	return cnt;
end;
$$ language plpgsql;

select from mixed_plpgsql();

--plpgsql function with EXECUTE statement
CREATE OR REPLACE FUNCTION execute_plpgsql(j integer)
RETURNS SETOF integer AS $$
BEGIN
  RETURN QUERY
     EXECUTE 'SELECT distinct i FROM t WHERE j = $1'
    USING j;
  RETURN;
END;
$$ LANGUAGE plpgsql;

select from execute_plpgsql(2);

--plpgsql function with using statement
CREATE OR REPLACE FUNCTION using_plpgsql() 
RETURNS integer AS $$
declare m integer;
	c integer;
        BEGIN
		select count (distinct i) into m from t where t.j = 6;            
		EXECUTE 'SELECT count(*) FROM t WHERE i = $1' INTO c USING m;
	RETURN c;        
	END;
$$ LANGUAGE plpgsql PARALLEL SAFE STABLE;

select from using_plpgsql();

--prepare statement
PREPARE prepare_parallel (int, int) AS
    select * from t where i = $1 and j < $2;
execute prepare_parallel(0,20); 

--tcl function with select statement
CREATE OR REPLACE FUNCTION parallel_tcl() RETURNS integer AS $$
spi_exec "SELECT count(*) AS cnt FROM t"
return $cnt;
END;
$$ LANGUAGE pltcl PARALLEL SAFE STABLE;
select * from parallel_tcl();

--python function with select statement
CREATE OR REPLACE FUNCTION parallel_py() RETURNS integer AS $$
rv = plpy.execute("SELECT count(*) FROM t");
return rv.nrows(); END;
$$ LANGUAGE plpython3u PARALLEL SAFE STABLE;
select * from parallel_py();

--perl function with select statement
CREATE OR REPLACE FUNCTION parallel_perl() RETURNS integer AS $$
$rv = spi_exec_query('select count(*) from t ');
return $rv->{processed} ;
END;
$$ LANGUAGE plperl PARALLEL SAFE STABLE;
select * from parallel_perl();

--drop all functions and table
drop table t cascade;
drop function parallel_perl();
drop function parallel_py();
drop function parallel_tcl();
drop function using_plpgsql();
drop function execute_plpgsql(integer);
drop function mixed_plpgsql();
drop function parallel_test_plpgsql();
drop function parallel_test_sql();
