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', '员工');
标签:DWS,name,自定义,varchar2,PG,table,schema,tb,pg From: https://www.cnblogs.com/shengkai126126/p/18393513