DWS(PG)自定义函数查询表注释 ，表结构

create or replace function show_table(tableName varchar2)
returns table(table_name varchar2, column_name varchar2, column_type varchar2, attnotnull varchar2, column_comment varchar2) as $$
begin
	return query
SELECT
    c.relname ::varchar2 as table_name, 
    a.attname ::varchar2 as column_name,
    format_type(a.atttypid,a.atttypmod) ::varchar2 as column_type, 
    a.attnotnull ::varchar2 as attnotnull, 
    col_description(a.attrelid,a.attnum) ::varchar2 as column_comment   
FROM 
    pg_class as c,pg_attribute as a 
where 
    a.attrelid = c.oid 
    and 
    a.attnum>0 
    and 
    c.relname = tableName; 	
end;
$$
language plpgsql;

select * from show_table('employees');

create or replace function query_table(tableName varchar2, tableDes varchar2)
returns table(table_name varchar2, table_des varchar2) as $$
begin
    if tableName is not null and tableDes is null then
    return query
        SELECT tb.table_name :: varchar2, d.description :: varchar2 FROM information_schema.tables tb
        JOIN pg_class c ON c.relname = tb.table_name
        LEFT JOIN pg_description d ON d.objoid = c.oid AND d.objsubid = '0' where table_name like '%' || tableName || '%'
        and table_schema not in('pg_catalog','information_schema','dbe_pldeveloper','dbe_perf','db4ai');
    elsif tableName is null and tableDes is not null then
    return query
        SELECT tb.table_name :: varchar2, d.description :: varchar2 FROM information_schema.tables tb
        JOIN pg_class c ON c.relname = tb.table_name
        LEFT JOIN pg_description d ON d.objoid = c.oid AND d.objsubid = '0' where description like '%' || tableDes || '%'
        and table_schema not in('pg_catalog','information_schema','dbe_pldeveloper','dbe_perf','db4ai');
    elsif tableName is not null and tableDes is not null then
    return query
        SELECT tb.table_name :: varchar2, d.description :: varchar2 FROM information_schema.tables tb
        JOIN pg_class c ON c.relname = tb.table_name
        LEFT JOIN pg_description d ON d.objoid = c.oid AND d.objsubid = '0' where description like '%' || tableDes || '%'
        and table_name like '%' || tableName || '%' and table_schema not in('pg_catalog','information_schema','dbe_pldeveloper','dbe_perf','db4ai');
    elsif tableName is null and tableDes is null then
    return query
        SELECT tb.table_name :: varchar2, d.description :: varchar2 FROM information_schema.tables tb
        JOIN pg_class c ON c.relname = tb.table_name
        LEFT JOIN pg_description d ON d.objoid = c.oid AND d.objsubid = '0' where table_schema not in('pg_catalog','information_schema','dbe_pldeveloper','dbe_perf','db4ai');
    end if;
    
end;
$$
language plpgsql;

select * from QUERY_TABLE('employees', '员工');