a9EFpUBA

1CREATE OR REPLACE FUNCTION public.drop_pgbench() RETURNS void LANGUAGE PLPGSQL AS $function$
2DECLARE
3BEGIN
4drop table if exists pgbench_history, pgbench_tellers, pgbench_accounts, pgbench_branches;
5END;
6$function$;
7
8
9CREATE  OR REPLACE FUNCTION public.load_pgbench(scale_factor int) RETURNS void LANGUAGE PLPGSQL AS $function$
10DECLARE
11BEGIN
12insert into pgbench_branches select bid, 0 from generate_series(1,scale_factor) bid;
13insert into pgbench_tellers select (bid-1)*10+t, bid, 0 from generate_series(1,scale_factor) bid, generate_series(1,10) t;
14insert into pgbench_accounts select ((bid::bigint-1)*100000::bigint+a::bigint)::bigint, bid, 0 from generate_series(1,scale_factor) bid, generate_series(1,100000) a;
15
16alter table pgbench_branches add constraint pgbench_branches_pkey primary key (bid);
17alter table pgbench_tellers add constraint pgbench_tellers_pkey primary key (tid);
18alter table pgbench_accounts add constraint pgbench_accounts_pkey primary key (aid);
19END;
20$function$;
21
22
23CREATE OR REPLACE FUNCTION public.reload_pgbench(scale_factor int) RETURNS void LANGUAGE PLPGSQL AS $function$
24DECLARE
25BEGIN
26alter table pgbench_branches drop constraint pgbench_branches_pkey;
27alter table pgbench_tellers drop constraint pgbench_tellers_pkey;
28alter table pgbench_accounts drop constraint pgbench_accounts_pkey;
29
30truncate pgbench_branches, pgbench_tellers, pgbench_accounts, pgbench_history;
31
32perform load_pgbench(scale_factor);
33END;
34$function$;
35
36
37CREATE OR REPLACE FUNCTION public.create_pgbench() RETURNS void LANGUAGE PLPGSQL AS $function$
38DECLARE
39BEGIN
40create table pgbench_history (
41  tid int,bid int,aid bigint,delta int,mtime timestamp,filler char(22)
42);
43
44create table pgbench_tellers (
45  tid int not null,bid int,tbalance int,filler char(84)
46);
47
48create table pgbench_accounts (
49  aid bigint not null,bid int,abalance int,filler char(84)
50);
51
52create table pgbench_branches (
53  bid int not null,bbalance int,filler char(88)
54);
55
56alter table pgbench_accounts alter column filler set default '                                                                                    ';
57
58perform create_distributed_table('pgbench_history', 'aid');
59perform create_distributed_table('pgbench_tellers', 'tid');
60perform create_distributed_table('pgbench_branches', 'bid');
61perform create_distributed_table('pgbench_accounts', 'aid');
62END;
63$function$;