黑龙江做网站的公司,湖南长沙特产,美工做网站是怎么做,wordpress文章标签只调用一个【openGauss︱PostgreSQL】openGauss或PostgreSQL查表、索引、序列、权限、函数 一、openGauss查表二、openGauss查索引三、openGauss查序列四、openGauss查权限五、openGauss或PostgreSQL查函数六、PostgreSQL查表七、PostgreSQL查索引八、PostgreSQL查序列九、PostgreSQL查权… 【openGauss︱PostgreSQL】openGauss或PostgreSQL查表、索引、序列、权限、函数 一、openGauss查表二、openGauss查索引三、openGauss查序列四、openGauss查权限五、openGauss或PostgreSQL查函数六、PostgreSQL查表七、PostgreSQL查索引八、PostgreSQL查序列九、PostgreSQL查权限 一、openGauss查表
/* openGauss查表 */
select (casewhen substr(version(), 0, 1) (then substring(substr(version(), 2, length(version())) from ^[^ ])else substring(version() from ^[^ ])end)::text as database_type,ic.table_schema::text as schema,o.owner::text,ic.table_name::text,tab_com.description::text as table_comment,ic.ordinal_position::text as column_num,ic.column_name::text,(casewhen ic.data_type bigintthen INT8when ic.data_type integerthen INT4when ic.data_type smallintthen INT2when ic.data_type in (nvarchar2) AND ic.character_maximum_length IS NOT NULLthen upper(ic.data_type) || ( || ic.character_maximum_length || )when ic.data_type in (nvarchar2) AND ic.character_maximum_length IS NULLthen NVARCHAR2when ic.data_type in (character varying)then VARCHAR( || ic.character_maximum_length || )when ic.data_type in (character)then CHAR( || ic.character_maximum_length || )when ic.data_type timestamp without time zonethen TIMESTAMP( || ic.datetime_precision || )when ic.data_type time without time zonethen TIME( || ic.datetime_precision || )when ic.data_type numeric and ic.numeric_precision is not null and ic.numeric_scale is not nullthen NUMERIC( || ic.numeric_precision || , || ic.numeric_scale || )when ic.data_type numeric and ic.numeric_precision is null and ic.numeric_scale is nullthen NUMERICwhen ic.data_type textthen TEXTwhen ic.data_type datethen DATEelse upper(ic.data_type)end)::text as data_type,col_com.description::text as column_comment,(CASEwhen (POSITION(lower(::regclass) IN lower(ic.column_default)) 0)then REPLACE(ic.column_default, ::regclass, )when (POSITION(lower(::integer) IN lower(ic.column_default)) 0)then REPLACE(REPLACE(ic.column_default, ::integer, ), , )else ic.column_defaultend)::text as column_default,ic.is_nullable::text,(casewhen pc.conname is null then null::textelse Yend)::text as pkeyfrom pg_class c-- join db_objects o join adm_objects o on o.object_id c.oid and o.object_type like %table%and c.relkind in (r,p)-- and c.relname ~ [0-9]join pg_namespace non c.relnamespace n.oidjoin information_schema.columns icon ic.table_name c.relnameand n.nspname ic.table_schemaleft join pg_description tab_comon tab_com.objoid c.oidand tab_com.objsubid 0left join pg_description col_comon col_com.objoid c.oidand col_com.objsubid ic.ordinal_positionleft join (SELECT conname, conrelid , unnest(conkey) as column_numFROM pg_constraintwhere contype p) as pcon pc.conrelid c.oidand pc.column_num ic.ordinal_positionwhere 11order by ic.table_schema, ic.table_name, ic.ordinal_position
;二、openGauss查索引
/* openGauss查索引 */
with t as (select (case when t1.indexdef ~ ^.* WHERE .*$then REGEXP_REPLACE(t1.indexdef, ^.*\((.*)\).* WHERE .*$, \1)else REGEXP_REPLACE(t1.indexdef, ^.*\((.*)\).*$, \1)end)::varchar as index_columns,t1.* from (select gs_et_sit::varchar as source,o.owner::varchar,ist.table_schema::varchar as schemaname,c.relname::varchar as tablename,c1.relname::varchar as indexname,(case i.indisuniquewhen t then Ywhen f then Nend)::varchar as index_is_unique,(case i.indisprimarywhen t then Ywhen f then Nend)::varchar as index_is_primary,(pg_get_indexdef(i.indexrelid))::varchar as indexdef_old,(case when pg_get_indexdef(i.indexrelid) ~ ^.*WHERE.*$then replace(replace(replace(REGEXP_REPLACE(pg_get_indexdef(i.indexrelid),^.*WHERE \((.*)\).*.*$, \1),::text, ),(, ),), )else NULLend)::varchar as where_condition,(casewhen i.indisprimary tthen ALTER TABLE || c.relname || ADD CONSTRAINT || c1.relname || PRIMARY KEY ( || (case when pg_get_indexdef(i.indexrelid) ~ ^.*\((.*)\).*\((.*)\).*$then REGEXP_REPLACE(pg_get_indexdef(i.indexrelid), ^.*\((.*)\).*\((.*)\).*$, \1)else REGEXP_REPLACE(pg_get_indexdef(i.indexrelid), ^.*\((.*)\).*$, \1)end) || ); else replace(replace(replace(replace(replace(replace(pg_get_indexdef(i.indexrelid), USING btree, ), TABLESPACE pg_default, ),::text, ), WITH (storage_typeUSTORE), ),WITH (storage_typeustore), ), USING ubtree, )||;end)::varchar as indexdef from pg_index i,pg_class c1,pg_class c,information_schema.tables ist,db_objects o,pg_namespace nwhere i.indexrelid c1.oidand i.indrelid c.oidand c.relnamespace n.oidand c.relname ist.table_nameand n.nspname ist.table_schemaand o.object_id c.oidand ist.table_schema not in (pg_catalog,db4ai)and ist.table_schema not like %\_test%and o.owner not in (rdsAdmin,hisAdmin)and o.owner not like %test%and c.relname !~* ^pg\_toast\_.*$) t1)
select t.source,t.owner,t.schemaname as schema,t.tablename,t.indexname,t.indexdef,t.index_is_primary,t.index_is_unique,t.index_columns,t.where_conditionfrom torder by t.schemaname, t.tablename, t.indexname
;三、openGauss查序列
/* openGauss查序列 */
with c as (
select r.rolname::varchar as owner,n.nspname::varchar as schema,c.relname::varchar as sequence_namefrom pg_class c, pg_roles r, pg_namespace nwhere c.relowner r.oidand c.relnamespace n.oidand c.relkind S)
,s as (
select sequence_owner::varchar as owner,sequence_name::varchar,min_value::varchar,max_value::varchar,increment_by::varchar,cache_size::varchar,cycle_flag::varchar,last_number::varcharfrom db_sequences
)
select c.owner::varchar,c.schema::varchar,c.sequence_name::varchar,s.min_value::varchar,s.max_value::varchar,s.increment_by::varchar,s.cache_size::varchar,s.cycle_flag::varchar,s.last_number::varcharfrom Cinner join s on c.owner s.owner and c.sequence_name s.sequence_nameorder by schema, sequence_name;四、openGauss查权限
/* openGauss查权限 */
WITH pg_rel_privs AS(SELECT xx.schemaname,xx.owner,xx.relname,xx.relkind,xx.relacls[1] AS grantee,xx.relacls[2] AS privilege,xx.relacls[3] AS grantorFROM (SELECT (SELECT n.nspnameFROM pg_namespace nWHERE n.oid tt.relnamespace) AS schemaname,o.owner,tt.relname,tt.relkind,regexp_split_to_array(unnest(tt.relacl)::character varying::text, |/::text) AS relaclsFROM pg_class ttLEFT JOIN db_objects oON tt.oid o.object_idWHERE tt.relacl IS NOT NULL) xx)
SELECT t.schemaname::varchar as schema,t.owner::varchar,t.relname::varchar as object_name,(CASEWHEN t.relkind r ::char THEN table ::textWHEN t.relkind S ::char THEN sequence ::textWHEN t.relkind v ::char THEN view ::textWHEN t.relkind p ::char THEN patition table ::textELSE t.relkind ::textEND)::varchar AS type,t.grantor::varchar,t.grantee::varchar,(TRIM(CASE WHEN instr(t.privilege, a) 0 THEN insert, END || CASE WHEN instr(t.privilege, r) 0 THEN select, END || CASE WHEN instr(t.privilege, w) 0 THEN update, END || CASE WHEN instr(t.privilege, d) 0 THEN delete, END || CASE WHEN instr(t.privilege, D) 0 THEN truncate, END || CASE WHEN instr(t.privilege, U) 0 THEN usage, END,,))::varchar AS privilegeFROM pg_rel_privs tWHERE t.grantor ! rdsAdminAND t.grantee ! t.grantororder by schema,object_name,type,grantor,grantee,privilege;五、openGauss或PostgreSQL查函数
/* openGauss或PostgreSQL查函数 */
select (CASE schemawhen tzq then f_g_tzqelse schemaEND)::varchar as schema,(CASE ownerwhen tzq then f_g_tzqelse ownerEND)::varchar as owner,pro_name,pro_arg,pro_arg_type,pro_ret_type,pro_content from (
select * from (select pn.nspname::varchar AS schema,r.rolname::varchar as owner,pc.proname::varchar as pro_name,pc.proargnames::varchar as pro_arg,(SELECT array(SELECT format_type(val, NULL)FROM unnest(proargtypes) as val))::varchar as pro_arg_type,format_type(pc.prorettype, NULL)::varchar as pro_ret_type,REGEXP_REPLACE(pg_get_functiondef(pc.oid)::text, ^\(4,(.*)\)$, \1)::textas pro_contentfrom pg_proc pcinner join pg_roles ron pc.proowner r.oidinner join pg_namespace pnon pc.pronamespace pn.oidwhere 11and pn.nspname not in (pg_catalog,dbe_pldebugger)and r.rolname not in (rdsAdmin) and pc.proargnames is not nullorder by r.rolname asc
) aa
union all select pn.nspname::varchar AS schema,r.rolname::varchar as owner,pc.proname::varchar as pro_name,pc.proargnames::varchar as pro_arg,proargtypes::varchar AS pro_arg_type,format_type(pc.prorettype, NULL)::varchar as pro_ret_type-- ,pc.oid,REGEXP_REPLACE(pg_get_functiondef(pc.oid)::text, ^\(4,(.*)\)$, \1)::text as pro_contentfrom pg_proc pcinner join pg_roles ron pc.proowner r.oidinner join pg_namespace pnon pc.pronamespace pn.oidwhere 11and pn.nspname not in (pg_catalog,dbe_pldebugger)and r.rolname not in (rdsAdmin) and pc.proargnames is null
) bb
order by schema, pro_name;六、PostgreSQL查表
/* PostgreSQL查表 */
/* 更新日志由于 information_schema.columns 有权限控制不是该用户能查的权限就查不到故下掉。采用 pg_attrdef 来获取列的默认值
*/
with tab_info_v as (SELECT substring(version() FROM (\S)) as database_type,n.nspname AS schema,r.rolname as owner,c.relname AS table_name,(col_description(c.oid, 0))::character varying AS table_comment,a.attnum,a.attname AS column_name,concat_ws(, t.typname) AS data_type,(CASEWHEN a.attlen 0 THEN a.attlenWHEN t.typname bit THEN a.atttypmodELSE a.atttypmod - 4END) AS data_length,CASE-- 为了和 高斯比对WHEN (((format_type(a.atttypid, a.atttypmod))::character varying)::text date::text) THEN TIMESTAMP(0)::character varyingWHEN (((format_type(a.atttypid, a.atttypmod))::character varying)::text bigint::text) THEN INT8::character varyingWHEN (((format_type(a.atttypid, a.atttypmod))::character varying)::text smallint::text) THEN INT2::character varyingWHEN (((format_type(a.atttypid, a.atttypmod))::character varying)::text integer::text) THEN INT4::character varyingWHEN (left(((format_type(a.atttypid, a.atttypmod))::character varying)::text, 17) character varying::text) THEN (replace(((format_type(a.atttypid, a.atttypmod))::character varying)::text, character varying::text, VARCHAR::text))::character varyingWHEN (left(((format_type(a.atttypid, a.atttypmod))::character varying)::text, 9) character::text) THEN (replace(((format_type(a.atttypid, a.atttypmod))::character varying)::text, character::text, char::text))::character varyingWHEN (((format_type(a.atttypid, a.atttypmod))::text)::text timestamp without time zone::text) THEN TIMESTAMP::character varyingWHEN (((format_type(a.atttypid, a.atttypmod))::text)::text timestamp(0) without time zone::text) THEN TIMESTAMP(0)::character varyingWHEN (((format_type(a.atttypid, a.atttypmod))::text)::text timestamp(6) without time zone::text) THEN TIMESTAMP(6)::character varyingELSE (format_type(a.atttypid, a.atttypmod))::character varyingEND AS typelen,(CASEWHEN a.attnotnull TRUE THEN NOELSE YESEND) AS is_nullable -- 可为空,(CASEWHEN (SELECT COUNT(pg_constraint.*)FROM pg_constraintINNER JOIN pg_classON pg_constraint.conrelid pg_class.oidINNER JOIN pg_attributeON pg_attribute.attrelid pg_class.oidAND pg_attribute.attnum ANY(pg_constraint.conkey)INNER JOIN pg_typeON pg_type.oid pg_attribute.atttypidWHERE pg_class.relname c.relnameAND pg_constraint.contype pAND pg_attribute.attname a.attnameand pg_class.relowner c.relowner) 0 THEN YELSE NULLEND) AS pkey,pg_get_expr(ad.adbin, ad.adrelid) as data_default,(SELECT descriptionFROM pg_descriptionWHERE objoid a.attrelidAND objsubid a.attnum) AS column_commentFROM pg_class cinner join pg_roles ron c.relowner r.oidand c.relkind in (r,p)and c.relpartbound is null-- and ((relkind p) or (relkind r and relpartbound is null))inner join pg_attribute aon a.attnum 0AND a.attrelid c.oidinner join pg_namespace non n.oid c.relnamespaceinner join pg_type ton a.atttypid t.oidleft join pg_attrdef ad -- 列的默认值on ad.adrelid c.oidand ad.adnum a.attnumWHERE 1 1and n.nspname not in (information_schema)ORDER BY c.relname ASC,a.attnum ASC,a.attname ASC
)
select database_type-- ,schema,CASE schemawhen tzq then f_g_tzqelse schemaEND as schema,CASE ownerwhen tzq then f_g_tzqelse schemaEND as owner,table_name, table_comment,attnum,column_name,replace(upper(typelen),VARCHAR,NVARCHAR2) as data_type,column_comment,data_default as column_default,is_nullable,pkeyfrom tab_info_v vwhere 11-- and table_name ets_risk_premium_check_tmp-- and schema ownerand table_name not like adms_ogg_checkpoint%-- and table_name !~ [p][0-9]{2}$-- and table_name !~ [p][0-9]{1}$-- and table_name ~ [p][0-9]{1}$
order by schema, table_name, attnum, column_name;七、PostgreSQL查索引
/* PostgreSQL查索引 */
-- drop view if exists pg_index_info_v;
-- create view pg_index_info_v as
with t as (
select u.usename ,c.relname as tablename,c1.relname as indexname,case i.indisuniquewhen t then Ywhen f then Nend as index_is_unique -- 是否为唯一索引,case i.indisprimarywhen t then Ywhen f then Nend as index_is_primary -- 是否为主键,case when pg_get_indexdef(i.indexrelid) ~ ^.* WHERE .*$then REGEXP_REPLACE(pg_get_indexdef(i.indexrelid), ^.*\((.*)\).* WHERE .*$, \1)else REGEXP_REPLACE(pg_get_indexdef(i.indexrelid), ^.*\((.*)\).*$, \1)end as index_columns,replace(pg_get_indexdef(i.indexrelid), USING btree , )||;as indexdef1,casewhen i.indisprimary tthen ALTER TABLE || c.relname || ADD CONSTRAINT || c1.relname || PRIMARY KEY ( || (case when pg_get_indexdef(i.indexrelid) ~ ^.*\((.*)\).*\((.*)\).*$then REGEXP_REPLACE(pg_get_indexdef(i.indexrelid), ^.*\((.*)\).*\((.*)\).*$, \1)else REGEXP_REPLACE(pg_get_indexdef(i.indexrelid), ^.*\((.*)\).*$, \1)end) || ); else replace(replace(replace(pg_get_indexdef(i.indexrelid), USING btree, ), TABLESPACE pg_default, ) ,::text, ) ||;end as indexdef,case when pg_get_indexdef(i.indexrelid) ~ ^.*WHERE.*$then replace(replace(replace(REGEXP_REPLACE(pg_get_indexdef(i.indexrelid),^.*WHERE \((.*)\).*.*$, \1),::text, ),(, ),), )else NULLend as where_condition--,i.* from pg_index i,pg_class c1,pg_class c,pg_user uwhere i.indexrelid c1.oidand i.indrelid c.oidand c.relowner u.usesysidand c.relkind in (r,p)and c.relpartbound is null-- and u.usename CURRENT_USERand c.relname !~* ^pg\_toast\_.*$)
select CASE t.usenamewhen tzq then f_g_tzqelse t.usenameEND as schema,t.tablename,t.indexname,casewhen index_is_primary Ythen ALTER TABLE || t.tablename || ADD CONSTRAINT || t.indexname || PRIMARY KEY ( || t.index_columns || ); else t.indexdef end as indexdef,t.index_is_unique,t.index_is_primary,t.index_columns,t.where_conditionfrom twhere t.tablename not like adms_ogg_checkpoint%order by t.usename, t.tablename, t.indexname;八、PostgreSQL查序列
/* PostgreSQL查序列 */
select (CASE sequenceownerwhen tzq then f_g_tzqelse sequenceownerEND)::varchar as owner,(CASE schemanamewhen tzq then f_g_tzqelse schemanameEND)::varchar as schema, sequencename::varchar as sequence_name, min_value::varchar, max_value::varchar, increment_by::varchar, cache_size::varchar,(case cycle when f then n else y end)::varchar as cycle_flag,(case when last_value is null then 1 else last_value end)::varchar as last_numberfrom pg_sequenceswhere 11-- and schemaname in (tzq);order by schema,sequence_name;九、PostgreSQL查权限
/* PostgreSQL查权限 */
WITH pg_rel_privs AS (SELECT xx.relowner,xx.schemaname,xx.relname,xx.relkind,xx.relacls[1] AS grantee,xx.relacls[2] AS privilege,xx.relacls[3] AS grantorFROM ( SELECT ( SELECT u.usenameFROM pg_user uWHERE u.usesysid tt.relowner) AS relowner,( SELECT n.nspnameFROM pg_namespace nWHERE n.oid tt.relnamespace) AS schemaname,tt.relname,tt.relkind,regexp_split_to_array(unnest(tt.relacl)::character varying::text, |/::text) AS relaclsFROM pg_class ttWHERE tt.relacl IS NOT NULL) xx),
dba_tab_privs as (SELECT x.relowner AS owner_,x.schemaname AS schema_name,x.relname AS table_name,CASEWHEN x.relkind r::char THEN table::textWHEN x.relkind S::char THEN sequence::textWHEN x.relkind v::char THEN view::textWHEN x.relkind p::char THEN patition table::textELSE x.relkind::textEND AS type_,x.grantee,CASEWHEN x.privilege r::text THEN select::textWHEN x.privilege a::text THEN insert::textWHEN x.privilege d::text THEN delete::textWHEN x.privilege w::text THEN update::textWHEN x.privilege D::text THEN truncate::textWHEN x.privilege X::text THEN execute::textWHEN x.privilege U::text THEN usage::textWHEN x.privilege x::text THEN references::textWHEN x.privilege t::text THEN trigger::textELSE x.privilegeEND AS privilege,x.grantorFROM ( SELECT t.relowner,t.schemaname,t.relname,t.relkind,t.grantee,regexp_split_to_table(t.privilege, ::text) AS privilege,t.grantorFROM pg_rel_privs tWHERE t.relowner t.grantee) x)
select (CASE schema_namewhen tzq then f_g_tzqelse schema_nameEND)::varchar as schema,(CASE owner_when tzq then f_g_tzqelse owner_END)::varchar as owner,table_name::varchar as object_name,type_::varchar as type,(CASE lower(grantor)when tzq then f_g_tzqelse lower(grantor)END)::varchar as grantor,(CASE lower(grantee)when tzq then f_g_tzqelse lower(grantee)END)::varchar as grantee,privilege::varcharfrom dba_tab_privs twhere owner_ not in (rdsAdmin)and schema_name not in (pg_catalog)and lower(grantee) not in (appmon)-- and schema_name in (tzq)-- and (table_name like %\_t OR table_name like %\_ti OR table_name like %\_tmp)
-- and type_ ~* tableand table_name not like adms_ogg_checkpoint%
order by schema_name,table_name;
