先上SQL吧!
WITH tables as (
SELECT tablename
FROM pg_tables a,
pg_class b
WHERE a.tablename = b.relname
AND a.tablename NOT LIKE 'pg%'
AND a.tablename NOT LIKE 'sql_%'
)
SELECT DISTINCT a.attnum,
tt.tablename,
a.attname,
concat_ws('', t.typname, SUBSTRING(format_type(a.atttypid, a.atttypmod) FROM '\(.*\)')) AS type,
d.description
FROM pg_class c,
pg_attribute a,
pg_type t,
pg_description d,
tables tt
WHERE
a.attname LIKE '%some_str%'
AND a.attnum > 0
AND a.attrelid = c.oid
AND a.atttypid = t.oid
AND d.objoid = a.attrelid
AND d.objsubid = a.attnum
AND c.relname = tt.tablename
ORDER BY tablename
上面的SQL可以自己运行看看效果!
- 其中with一个临时表tables中,tablename就是表名称,c.relname也是表名称
- 下面a.attname是表的字段名称,就像写SQL一样的。