greenplum查出表涉及到的view和创建语句

select
a.table_schema,
a.table_name,
a.drop_sql,
a.create_sql,
string_agg(‘grant ’ || rtg.privilege_type || ’ on "’ || a.table_schema || ‘"."’ || a.table_name || '" to ’ || rtg.grantee, ‘;’ ) || ‘;’ as grant_sql
from
(
SELECT
ns2.nspname as table_schema,
c2.relname as table_name,
‘drop view "’ || ns2.nspname || ‘"."’ || c2.relname || ‘";’ as drop_sql,
‘create view "’ || ns2.nspname || ‘"."’ || c2.relname || '" as ’ || pg_get_viewdef(ns2.nspname || ‘.’ || c2.relname, true) as create_sql
FROM
pg_namespace ns,
pg_class c,
pg_depend d,
pg_rewrite r,
pg_class c2,
pg_namespace ns2
WHERE
ns.nspname = ‘order’ – schema 名称
AND c.relnamespace = ns.oid
AND c.relname = ‘sales_order_items_detail_all’ – 表名称
AND d.refclassid = 1259
AND d.refobjid = c.oid
AND d.classid = 2618
AND r.oid = d.objid
AND c2.oid = r.ev_class
AND ns2.oid = c2.relnamespace
GROUP BY
ns.nspname,
c.relname,
ns2.nspname,
c2.relname
) a,
information_schema.role_table_grants rtg
where a.table_schema = rtg.table_schema
and a.table_name = rtg.table_name
group by a.table_schema, a.table_name, a.drop_sql, a.create_sql

修改步骤：
– 先删除财务和其他依赖该试图的对象
drop view “dw_agg”.“v_agg_ord_dim_order_item_dimension”;
drop view “dw_fin”.“v_order”;
– 再删除该试图
drop view “dw_agg”.“v_agg_ord_dim_order_item_dimension”;
– 修改字段长度
alter table table_name alter column table_column type varchar(200);
alter table table_name_bak alter column table_column type varchar(200);
– 创建该试图
– 试图付权限
– 创建 财务和其他需要的试图（注意schema）
– 给财务和其他需要的地方赋权限