plpgsql 的 for 循环使用_pgsql for循环

DO $$
DECLARE

    v_state   TEXT;
    v_msg     TEXT;
    v_detail  TEXT;
    v_hint    TEXT;
    v_context TEXT;

    lv_table_logical_name        varchar:='t_tmp';
    lv_table_shard_seq_begin     bigint:=0;
    lv_table_shard_seq_end       bigint:=4095;
    lv_table_shard_name_rec      record;

    lv_begin_time varchar:='2023-08-08 10:30:00';
    lv_end_time   varchar:='2023-08-08 11:00:00';


    lv_sql      text;

    lv_result_1 varchar;
    lv_result_2 bigint;

BEGIN

    for lv_table_shard_name_rec in (
         select t0.*
           from (
				 select lv_table_logical_name||'_'||id as table_shard_name,
						id
				   from generate_series(lv_table_shard_seq_begin, lv_table_shard_seq_end) as id
				  order by id
				 ) t0,
				 (
				  select *
					from pg_tables pt
				   where 1=1
					 and pt.schemaname not in (
						  'information_schema',
						  'pg_catalog',
						  'pg_temp_1',
						  'pg_toast',
						  'pg_toast_temp_1'
					     )
				  ) t1
		where 1=1
		  and t0.table_shard_name = t1.tablename

    )

    loop


        lv_sql= ' select '''||lv_table_shard_name_rec.table_shard_name||''' as tablename,count(1) from '||lv_table_shard_name_rec.table_shard_name||' where create_time BETWEEN '''||lv_begin_time||''' and '''||lv_end_time||'''';


        EXECUTE lv_sql into lv_result_1,lv_result_2;

        if lv_result_2 > 0 then

            raise notice 'lv_sql=%',lv_sql;
            raise notice 'tablename= % ,count= %', lv_result_1,lv_result_2;

        end if;

    end loop;



EXCEPTION
        when OTHERS then

        get stacked diagnostics
        v_state   = returned_sqlstate,
        v_msg     = message_text,
        v_detail  = pg_exception_detail,
        v_hint    = pg_exception_hint,
        v_context = pg_exception_context;

    raise notice E'exception:
        state  : %
        message: %
        detail : %
        hint   : %
        context: %', v_state, v_msg, v_detail, v_hint, v_context;

    raise notice E'exception:SQLSTATE: % SQLERRM: %', SQLSTATE, SQLERRM;

END;

$$ LANGUAGE plpgsql;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92

DO $$
DECLARE

    v_state   TEXT;
    v_msg     TEXT;
    v_detail  TEXT;
    v_hint    TEXT;
    v_context TEXT;

    lv_table_logical_name        varchar:='t_tmp';
    lv_table_shard_seq_begin     bigint:=0;
    lv_table_shard_seq_end       bigint:=4095;
    lv_table_shard_name_rec      record;
    lv_list_rec                  record;

    lv_begin_time varchar:='2023-08-08 10:30:00';
    lv_end_time   varchar:='2023-08-08 11:00:00';


    lv_sql      text;


BEGIN


    for lv_table_shard_name_rec in (
         select t0.*
           from (
				 select lv_table_logical_name||'_'||id as table_shard_name,
						id
				   from generate_series(lv_table_shard_seq_begin, lv_table_shard_seq_end) as id
				  order by id
				 ) t0,
				 (
				  select *
					from pg_tables pt
				   where 1=1
					 and pt.schemaname not in (
						  'information_schema',
						  'pg_catalog',
						  'pg_temp_1',
						  'pg_toast',
						  'pg_toast_temp_1'
					     )
				  ) t1
		where 1=1
		  and t0.table_shard_name = t1.tablename

    )

    loop


        lv_sql= ' select '''||lv_table_shard_name_rec.table_shard_name||''' as tablename,id,name from '||lv_table_shard_name_rec.table_shard_name||' where create_time BETWEEN '''||lv_begin_time||''' and '''||lv_end_time||'''';


        for lv_list_rec in EXECUTE lv_sql
        loop

           raise notice 'lv_sql=%',lv_sql;
           raise notice 'tablename= % ,id= %, name= %', lv_list_rec.tablename,lv_list_rec.id,lv_list_rec.name;

        end loop;

    end loop;

EXCEPTION
        when OTHERS then

        get stacked diagnostics
        v_state   = returned_sqlstate,
        v_msg     = message_text,
        v_detail  = pg_exception_detail,
        v_hint    = pg_exception_hint,
        v_context = pg_exception_context;

    raise notice E'exception:
        state  : %
        message: %
        detail : %
        hint   : %
        context: %', v_state, v_msg, v_detail, v_hint, v_context;

    raise notice E'exception:SQLSTATE: % SQLERRM: %', SQLSTATE, SQLERRM;

END;

$$ LANGUAGE plpgsql;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89