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

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

调用方法：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;