greenplum基础查询

– 查询表结构

select table_schema||'.'||table_name as table_name,column_name,
	case when data_type='integer' then data_type 
		when data_type='numeric' then data_type||'(18,2)'
		when data_type='date' then data_type
		when data_type='timestamp without time zone' then 'timestamp'
		when data_type='character varying' then 'varchar'||'('||coalesce(character_maximum_length,'0')||')'
		else data_type||'('||coalesce(character_maximum_length,'0')||')' end as column_type
    from information_schema.columns
    where table_schema||'.'||table_name='schemaname.tablename';

– 查询视图信息(schemaname, viewname, viewowner, definition)

definition：查询哪些视图使用了某字段或某表名

select * from pg_views 
	where schemaname||'.'||viewname = ''
	and viewowner = '' and definition like '%%';

– 查询表或视图权限(拼接赋权语句),表的owner

select 'alter table '||a.schemaname||'.'||a.tablename||' owner to '||a.tableowner||';'
        from (
                select tableowner, schemaname, tablename
                        from pg_tables
                union all
                select viewowner, schemaname, viewname
                        from pg_views
        ) a
        where schemaname||'.'||tablename = ''
union all
select 'grant '||p_type||' on table '||b.schemaname||'.'||b.tablename||' to ' ||b.grantee||';'
        from (
                select t.tableowner, t.schemaname, t.tablename, r.grantee, lower(string_agg(privilege_type,',')),
                -- insert,delete,update,select,truncate,references,trigger
                case when count(privilege_type) >= 7 then 'all' else lower(string_agg(privilege_type,',')) end p_type
                        from (
                                select tableowner, schemaname, tablename
                                        from pg_tables
                                union all
                                select viewowner, schemaname, viewname
                                        from pg_views
                        ) t
                        join information_schema.role_table_grants r 
                                on t.tablename = r.table_name and t.schemaname = r.table_schema
                        where t.schemaname||'.'||t.tablename = ''
                        group by t.tableowner, t.schemaname, t.tablename,r.grantor,r.grantee
                        order by p_type
        ) b;