使用函数查询符合条件的表，并清空表数据，或者删除表

墨蓝 2022-05-29 02:29 161阅读 0赞

下边的查询条件可根据具体需求进行修改

调用方法：select \* from 函数名()；

eg：select \* from query\_all\_table\_name();

1.查询所有符合条件的表名

create or replace function query_all_table_name() returns setof varchar as 
    $$
      declare
        select_result record;
      begin
      for select_result in
        select tablename from ux_tables where tablename like 'tbl_%' and tablename != 'tbl_user'
      loop
        return next select_result.tablename;
      end loop;
      return;
      end;
    $$
    language pluxsql volatile
    cost 100 rows 1000;

2.清除（delete）所有查询到的符合条件的表的数据

create or replace function clear_table_data() returns text as 
    $$
      declare
        select_result record;
        table_name varchar;
      begin
        for select_result in
          select tablename from ux_tables where tablename like 'tbl_%' and tablename != 'tbl_user'
        loop
          table_name = select_result.tablename;
          execute 'delete from ' || table_name;
        end loop;
        return 'success';
      end;
    $$
    language pluxsql;

3.删除（drop）所有查询到的符合条件的表

create or replace function drop_table() returns text as 
    $$
      declare
        select_result record;
        table_name varchar;
      begin
        for select_result in
          select tablename from ux_tables where tablename like 'tbl_%' and tablename != 'tbl_user'
        loop
          table_name = select_result.tablename;
          execute 'drop table ' || table_name;
        end loop;
        return 'success';
      end;
    $$
    language pluxsql;