PostgreSQL 查表注释，字段注释和库中是否有某一个数据

表信息描述

with tmp_tab as (
    select pc.oid as ooid,pn.nspname,pc.*
    from pg_class pc
    left outer join pg_namespace pn on pc.relnamespace = pn.oid
    where 1=1
        and pc.relkind in ('r','v','m','f','p')
        and pn.nspname not in ('pg_catalog','information_schema') -- select pn.oid, pn.* from pg_namespace pn where 1=1
        and pn.nspname not like 'pg_toast%'
        -- and relname like '%temp%'
       /*and pc.oid not in (
          select inhrelid
            from pg_inherits
       )*/
),tmp_table_desc as (
    select pd.*
    from pg_description pd
    where 1=1
        and pd.objsubid = 0 --objsubid 对于一个表列上的一个注释，这里是列号（objoid和classoid指表本身）。对所有其他对象类型，此列为0。
      --and pd.objoid=168605
)
select t0.*,
    case when t0.relkind in ('r','p')
             then 'comment on table '||t0.nspname||'.'||t0.relname||' is '''||coalesce(t0.description,'')||''';' 
         when t0.relkind='v' 
             then 'comment on view '||t0.nspname||'.'||t0.relname||' is '''||coalesce(t0.description,'')||''';' 
         end as table_description2
from(
    select 
        tab.nspname,
        tab.relname,
        tab.relkind,
        de.description 
    from tmp_tab tab
    left outer join tmp_table_desc de on tab.ooid = de.objoid 
    where 1=1    
) t0
where 1=1 
-- and t0.description like '%temp%'
order by t0.nspname,t0.relname   
;

字段信息描述

with tmp_tab as (
    select pc.oid as ooid,pn.nspname,pc.*
    from pg_class pc
    left outer join pg_namespace pn on pc.relnamespace = pn.oid
    where 1=1
        and pc.relkind in ('r','v','m','f','p')
        and pn.nspname not in ('pg_catalog','information_schema') -- select pn.oid, pn.* from pg_namespace pn where 1=1
        and pn.nspname not like 'pg_toast%'
        -- and relname like '%temp%'
       /*
       and pc.oid not in (
          select inhrelid
            from pg_inherits
       )*/
    order by pc.relname
),tmp_col as (
    select pa.*
    from pg_attribute pa
    where 1=1
      --and pa.attrelid = 168605
        and pa.attisdropped = false
        and pa.attname not in ('tableoid', 'cmax', 'xmax', 'cmin', 'xmin', 'ctid' )
),tmp_table_field_desc as (
    select pd.*
    from pg_description pd
    where 1=1
        and pd.objsubid <> 0 --objsubid 对于一个表列上的一个注释，这里是列号（objoid和classoid指表本身）。对所有其他对象类型，此列为0。
        --and pd.objoid=168605
)
select t0.*,
       'comment on COLUMN '||t0.nspname||'.'||t0.relname||'.'||t0.attname||' is '''||coalesce(t0.description,'')||''';' as column_description
from (
    select 
        tab.nspname,
        tab.relname,
        tc.attname,
        tc.attnum,
        de.description
    from tmp_tab tab
    left outer join tmp_col tc  on tab.ooid = tc.attrelid
    left outer join tmp_table_field_desc de on tc.attrelid = de.objoid and tc.attnum = de.objsubid
) t0
where 1=1
   --and t0.description like '%教师%'
order by t0.nspname,t0.relname, t0.attnum

同时包含表信息和字段信息描述

with tmp_tab as (
    select pc.oid as ooid,pn.nspname,pc.*
    from pg_class pc
    left outer join pg_namespace pn on pc.relnamespace = pn.oid
    where 1=1
        and pc.relkind in ('r','v','m','f','p')
        and pn.nspname not in ('pg_catalog','information_schema') 
        and pn.nspname not like 'pg_toast%'
        -- and relname like '%temp%'
    order by pc.relname
),tmp_col as (
    select pa.*
    from pg_attribute pa
    where 1=1
        and pa.attisdropped = false
        and pa.attname not in ('tableoid', 'cmax', 'xmax', 'cmin', 'xmin', 'ctid' )
),tmp_table_desc as (
    select pd.*
    from pg_description pd
    where 1=1
        and pd.objsubid <> 0 
),tmp_table_filed_desc as (
    select pd.*
    from pg_description pd
    where 1=1
        and pd.objsubid = 0 
)
select t0.*
from (
    select 
        tab.nspname,
        tab.relname,
        tc.attname,
        tc.attnum,
        de.description as table_field_description,
        de2.description as table_description
    from tmp_tab tab
    left outer join tmp_col tc  on tab.ooid = tc.attrelid
    left outer join tmp_table_desc de on tc.attrelid = de.objoid and tc.attnum = de.objsubid
    left outer join tmp_table_filed_desc de2 on tab.ooid = de2.objoid
) t0
where 1=1 and nspname = 'public' and relname like 'md%'
   --and t0.description like '%教师%'
order by t0.nspname,t0.relname, t0.attnum

查询数据库中是否有某一个数据

DO $$
DECLARE
    pattern text := '%扣分%';
    value int := 0;
    sql text := '';
    rec1 record;
    rec2 record;
BEGIN
    DROP TABLE IF EXISTS _x;
    CREATE TEMPORARY TABLE _x (
      schema_name text, 
      table_name text, 
      column_name text,
      found text
    );
    FOR rec1 IN 
        SELECT table_schema, table_name, column_name
        FROM information_schema.columns 
        WHERE table_name <> '_x'
            AND UPPER(column_name) LIKE UPPER('%%')                  
            AND table_schema <> 'pg_catalog'
            AND table_schema <> 'information_schema'
            AND data_type IN ('character varying', 'text', 'character', 'char', 'varchar')
    LOOP
        sql := concat('SELECT ', rec1."column_name", ' AS "found" FROM ',rec1."table_schema" , '.',rec1."table_name" , ' WHERE UPPER(',rec1."column_name" , ') LIKE UPPER(''', pattern , ''')');
        RAISE NOTICE '%', sql;
        BEGIN
            FOR rec2 IN EXECUTE sql LOOP
                RAISE NOTICE '%', sql;
                INSERT INTO _x VALUES (rec1."table_schema", rec1."table_name", rec1."column_name", rec2."found");
            END LOOP;
        EXCEPTION WHEN OTHERS THEN
        END;
    END LOOP;
END; $$;

SELECT * FROM _x;