Geekly articles weekly

记录PostgreSQL数据库的功能。 第一部分

在使用PostgreSQL的过程中，已经积累了一百多个用于处理系统目录的函数：pg_class，pg_attribute，pg_constraints等。


该怎么办？ 它们相对很少使用。 包括在任何项目中吗？ 克拉斯诺亚尔斯克客户不会为这种“废话”付款。 但是，如果它们对作者以外的其他人有用，该怎么办？ 然后，他决定将它们像看书一样放置在公共橱柜中，以备不时之需。

有人想在他们的工作中使用它们。 和对系统目录的经验感兴趣的人。

但是为了不使出版物变成无聊的枚举，不知道为什么要创建这些功能，我决定着重讨论那些可以实现共同目标的功能。 因此，选择用于显示任意数据库表特征的扩展列表的功能。

数据库表特征的扩展列表由admtf_Table_ComplexFeatures函数返回，该函数在本文中称为head函数。 因此，本文将限于考虑在执行head函数的过程中调用的函数。

本文的前半部分提供了有关功能实现的评论。 第二个是函数的源代码。 对于只对源文本感兴趣的读者，我们建议立即继续阅读附录 。

另请参阅
记录PostgreSQL数据库的功能。 第二部分 ;
记录PostgreSQL数据库的功能。 第三部分 。
记录PostgreSQL数据库的功能。 最后（第四部分） 。




我们在说什么高级功能？


为了了解数据库表的扩展特征的含义，我们首先考虑以下特征列表。 该列表包含admtf_Table_ComplexFeatures（ 'public' ， 'street' ）返回的Street数据库表的特征。

下表包含Street表功能的缩写列表。 附录2的补充材料中提供了此表的完整特性。

表1. Street表的高级功能。

图中表格的文本版本
类别分类不行职称评注类型基本类型？ 不为NULL
tbl0街定居点的街道清单
att1个wcrccode国家区号wcrccodeSmallintŤ
att2地区性社区ID地区性整数Ť
att3街头小贩村ID街街头小贩SmallintŤ
att4streettypeacrm街头缩写streettypeacrm人物（8）˚F
att5街道名街道名称streettypeacrmvarchar（150）Ť
pk0xpkstreet街道表主键
pkatt1个wcrccode国家区号wcrccodeSmallintŤ
fk011个fk_street_locality表外键
fk022fk_street_streettype表外键
idx011个xie1street按居民点类型和街道名称编制的索引
idx022xie2street邮递区号
idx033xie3street所有定居点的街道名称索引
idx044xpkstreet街道表的唯一（主键）索引


列举对这组字母和数字的敌意，您可以看到我们正在谈论数据库表的通常特征：


  • 表名；
  • 表属性及其类型的列表；
  • 表的主键和外键列表，以及组成它们的表的属性；
  • 表索引列表。

特性列表中每个条目的唯一性由“类别”字段的值和特性的序列号（“编号”）确保。



表2.表特征的类别。

图中表格的文本版本
首字母缩写预约时间
待定表特征
att表属性特征
序列序列特征
pk主要关键特征
pkAtt主键属性特征
fk99外键功能
fk99att外键属性特征
fk99rtbl外键引用表的特征
fk99ratt外键引用的表的属性特征
编号99指标特征
Idx99att索引属性特征
Inhtbl99生成表的特征


类别值是区分彼此不同的特征组所必需的。 序列号，以区分组内的特征。


可以在数据库表中声明几个外键（FOREIGN KEY）和索引。 因此，这些特征及其后代的类别值包含一个序列号。 例如，键为“ Category” = idx02att和“ No.” = 1的条目表示第二个索引的第一个属性。


在上面的类别列表中，序列号的位置表示为“ 99”。


备注1



图中操作员的源代码
SELECT * FROM admtf_Table_ComplexFeatures('pg_catalog','pg_class');



本文提供了表特性的示例，这些示例在专门为演示功能而创建的辅助方案中进行了简要描述。 但是，已经在数据库中创建了该功能的读者可以将其方案和表的名称用作参数。 而且，例如，尽管在这种情况下发出了有限数量的特征，但是pg_class目录可以用作参数。

备注的结尾。



头功能结构



图 1.引起头部功能的功能。

表3.功能分配。

图中表格的文本版本
不行职称预约时间
1个admtf_Table_Features该函数返回数据库表特征的列表
2admtf_Table_Attributes该函数返回数据库表属性及其特征的列表。
3admtf_Table_Constraintes该函数返回数据库表限制及其特征的列表。
4admtf_Table_Indexes该函数返回数据库表索引及其特征的列表。
5admtf_Table_InheritanceChildrens该函数返回由（IHERITS）从源数据库表生成的表的列表。
6admtf_Table_Sequences该函数返回该表所依赖的序列列表（SEQUENCE）。
7admtf_PrimaryKey_ComplexFeatures该函数返回数据库表的主键特征（PRIMARY KEY）的完整（扩展）列表。
8admtf_ForeignKey_ComplexFeatures该函数返回数据库表的外键特征（FOREIGN KEY）的完整（扩展）列表。
9admtf_Index_ComplexFeatures该函数返回数据库表索引特征的完整（扩展）列表。
10admtf_Table_ComplexFeatures该函数返回数据库表特征的完整（扩展）列表。


备注2。


功能描述将按上面显示的顺序排列。 原因是该文章将必须分为几个部分。 并且可以使用按此顺序排列的功能，而不管其中的某些功能仅在出版物的以下部分中进行描述的事实。


备注的结尾。



admtf_Table_Features函数列表数据库的表特征




admtf_Table_Features函数返回数据库表本身的特征列表。 可以在此处查看和下载源代码。


作为参数，此函数采用源表的名称（a_TableName ）和在其中创建表的方案的名称（ a_SchemaName ）。


该函数从pg_class目录条目中提取主要数据，除表条目外，该条目还包含有关序列，视图，实例化视图和组合类型的记录。 因此，要选择表，使用条件relkind = 'r' 。



 SELECT tbl.relname,dsc.description,tbl.relnatts::INTEGER,tbl.relchecks::INTEGER, tbl.relhaspkey,tbl.relhasindex,tbl.relhassubclass,tbl.reltuples::INTEGER FROM pg_class tbl INNER JOIN pg_namespace nspc ON tbl.relnamespace = nspc.oid LEFT OUTER JOIN pg_description dsc ON tbl.oid=dsc.objoid AND dsc.objsubid=0 WHERE LOWER( nspc.nspname)=LOWER(a_SchemaName) AND tbl.relkind='r' AND LOWER(tbl.relname) =LOWER(a_TableName);

另外，该函数访问目录数据pg_namespace和pg_description 。 第一个包含数据库模式的名称，第二个包含对所有数据库对象的注释。


重要的是要注意条件objsubid = 0 。 它定义了表的注释，因为表及其属性的objoid字段的值都相同。 关于表的属性的注释包含在objsubid与该属性的编号匹配的条目中。



表4.执行admtf_Table_Features函数（“ public”，“ Street”）的结果。

职称评注属性数量检查约束数？ 有主键吗？ 是声明的索引？ 有后裔吗表中的条目数
街定居点的街道清单220ŤŤ˚F20150

备注3


注意街道表属性的数量。 它与辅助模式中指定的属性数量明显不同。


表5. Street表的其他属性。


姓名ttyAttnum注意事项
in29日-4系统属性
最小28-3系统属性
ctid27-1系统属性
wcrccode7953691个有效属性
地区性7953522有效属性
街头小贩7953643有效属性
streettypeacrm19191684有效属性
街道名10435有效属性
..... pg.dropped.6 ........06远程属性
..... pg.dropped.7 ........07远程属性

事实是，除了主要属性外，PostgreSQL还考虑了几个系统属性，甚至删除了属性。


结束语



数据库表属性及其特征的函数admtf_Table_Attributes列表


admtf_Table_Attributes函数返回数据库表属性的列表。 可以在此处查看和下载源代码。


该函数将源表的名称（ a_TableName ）和在其中创建表的方案的名称（ a_SchemaName ）作为参数。


该函数从pg_attribute和pg_type目录条目中检索主要数据。 第一个记录包含有关表，视图，实例化视图，组合类型甚至函数的属性的数据记录。 第二点是关于属性类型的特征。


可能需要通过在函数中定义用户和基本类型的方式进行一些说明。


如果在相应的pg_type目录条目中的typbasetype字段大于 0，则使用自定义类型声明表属性，否则，该属性为基本类型。 因此，在FROM子句中两次涉及pg_type目录。 在第一个目录条目中，确定用户类型的存在，如果未定义（ typebasetype = 0 ），则从该条目中生成基本类型值。 否则，从btyp.OID = typ.typbasetype的记录中确定基本类型。


使用系统目录FORMAT_TYPE（type_oid，typemod）的功能直接形成基本类型的行。 其第一个参数是基本类型OID记录。 第二个参数是包含大小的类型的修饰符值。 例如，VARCHAR（100）或NUMERIC（4,2），DECIMAL（4,2）。 如果属性是用户类型，则从typ.typtypmod中获取typemod参数的值，否则从attr.atttypmod中获取 ，即 直接从属性记录。



图中操作员的源代码
 SELECT attr.attnum, attr.attname::VARCHAR(100), CASE WHEN COALESCE(typ.typbasetype,0)>0 THEN typ.typname::VARCHAR(100) ELSE '' END, FORMAT_TYPE(COALESCE(NULLIF(typ.typbasetype,0),typ.oid), COALESCE(NULLIF(typ.typtypmod,-1),attr.atttypmod))::VARCHAR(256), attr.attnotnull, dsc.description FROM pg_attribute attr INNER JOIN pg_class tbl ON tbl.oid = attr.attrelid INNER JOIN pg_namespace nspc ON tbl.relnamespace = nspc.oid INNER JOIN pg_type typ ON attr.atttypid=typ.oid LEFT OUTER JOIN pg_description dsc ON dsc.objoid=attr.attrelid AND dsc.objsubid=attr.attnum WHERE LOWER( nspc.nspname)=LOWER(a_SchemaName) AND LOWER(tbl.relname) =LOWER(a_TableName) AND tbl.relkind='r' AND attr.attnum>0 AND attr.atttypID>0 ORDER BY tbl.relname,attr.attnum;


另外，该函数访问目录数据pg_class ， pg_namespace和pg_description 。 第一和第二目录用于通过模式和数据库表的名称搜索属性。

第三个目录用于检索表属性的注释。


关于表属性的注释在条目中，其中dsc.objoid包含源表的OID，而dsc.objsubid是表中属性的序列号，即 attr.attnum 。


为了防止该函数返回系统和远程属性，WHERE子句将条件设置为attr.attnum> 0和attr.atttypID> 0 。



表6.执行admtf_Table_Attributes函数（“ public”，“ Street”）的结果。

不行职称自订类型基本类型？ 不为空评注
1个wcrccodewcrccodeSmallintŤ国家区号
2地区性地区性整数Ť社区ID
3街头小贩街头小贩SmallintŤ村ID街
4streettypeacrmstreettypeacrm人物（8）˚F街头缩写
5街道名varchar（150）Ť定居点的街道名称

使用regclass别名作为oid类型的函数版本


PostgreSQL对象标识符（OID）与OID的类型相同，OID当前被实现为无符号的四字节整数。 但是由于存在这种别名，因此可以将整数表示为对象的名称。 反之亦然-将对象名称转换为OID类型的整数。


作为示例，请看下面的SELECT语句 。 它会异常地检索属性表的名称及其类型的名称-而不是使用这些特征的名称来访问相应的目录字段，而是使用它们：

  • attrelid :: regclass（attrelid :: regclass：NAME） ，
  • atttypid :: regtype（atttypid :: regtype：NAME）
  • typbasetype :: regtype（typbasetype :: regtype：NAME） 。


 SELECT attr.attname,attr.attrelid::regclass, attr.atttypid::regtype, typ.typbasetype::regtype,attr.attrelid::regclass::name, attr.atttypid::regtype::name,typ.typbasetype::regtype::name FROM pg_attribute attr INNER JOIN pg_type typ ON attr.atttypid=typ.oid WHERE attr.attrelid=('public'||'.'||'Street')::regclass AND attr.attnum>0 AND attr.atttypID>0 ORDER BY attr.attnum;

以下是执行此查询的结果。



在使用OID类型的别名进行转换之前的SELECT运算符的输出值列表中，除属性名称之外的所有值都是数字，因此，将显示表名称和属性类型。 可以在表标题的第二行中考虑输出值的类型。


另外，在该语句的WHERE子句中，条件attr.attrelid =（'public'||'。'||'Street'）:: regclass位于 ，其左侧是一个数字值，而右侧是一个字符串，该字符串被转换为带有使用别名regclass 。



图中操作员的源代码
 SELECT attr.attnum, attr.attname::VARCHAR(100), CASE WHEN COALESCE(typ.typbasetype,0)>0 THEN typ.typname::VARCHAR(100) ELSE '' END, FORMAT_TYPE(COALESCE(NULLIF(typ.typbasetype,0),typ.oid), COALESCE(NULLIF(typ.typtypmod,-1),attr.atttypmod))::VARCHAR(256), attr.attnotnull, dsc.description FROM pg_attribute attr INNER JOIN pg_type typ ON attr.atttypid=typ.oid LEFT OUTER JOIN pg_description dsc ON dsc.objoid=attr.attrelid AND dsc.objsubid=attr.attnum WHERE attr.attrelid=( a_SchemaName ||'.'|| a_TableName)::regclass AND attr.attnum>0 AND attr.atttypID>0 ORDER BY attr.attnum;


使用main语句中的别名regclass，可以删除与两个目录的连接。 但是这种改进几乎对功能的性能没有影响-在两个版本中，该功能都在11毫秒内执行。 可能由于测试表几乎没有属性的事实。
备注4

当数据库中存在具有异常名称的模式和/或表时，就会表现出attr.attrelid =（a_SchemaName ||'。'|| a_TableName）:: regclass形式的条件中的严重缺陷。 例如， “我的模式”和/或“我的表” 。 此类值必须使用双引号括起来或使用QUOTE_IDENT函数进行传输，否则该函数将因系统错误而失败。



图中操作员的源代码
 /*       «   »*/ SELECT attr.attname,attr.attrelid::regclass, attr.atttypid::regtype,typ.typbasetype::regtype, attr.attrelid::regclass::name, attr.atttypid::regtype::name,typ.typbasetype::regtype::name FROM pg_attribute attr INNER JOIN pg_type typ ON attr.atttypid=typ.oid WHERE attr.attrelid=(' '||'.'||' ')::regclass AND attr.attnum>0 AND attr.atttypID>0 ORDER BY attr.attnum; /*       */ SELECT attr.attname,attr.attrelid::regclass, attr.atttypid::regtype,typ.typbasetype::regtype, attr.attrelid::regclass::name, attr.atttypid::regtype::name,typ.typbasetype::regtype::name FROM pg_attribute attr INNER JOIN pg_type typ ON attr.atttypid=typ.oid WHERE attr.attrelid=('" "'||'.'||'" "')::regclass AND attr.attnum>0 AND attr.atttypID>0 ORDER BY attr.attnum;


因此，我更喜欢使用LOWER（nspc.nspname）= LOWER（a_SchemaName）和LOWER（tbl.relname）= LOWER（a_TableName）形式的条件 ，这不会导致系统错误。

结束语

附录1.脚本


创建admtf_Table_Features函数


有关功能源代码的注释，请参见此处。
函数源代码
 BEGIN TRANSACTION; DROP FUNCTION IF EXISTS admtf_Table_Features (a_SchemaName NAME,a_TableName NAME); /********************************************************************************************************/ /*     ,   */ /********************************************************************************************************/ CREATE OR REPLACE FUNCTION admtf_Table_Features (a_SchemaName NAME default 'public', /*     */ a_TableName NAME default NULL /*   */ ) RETURNS TABLE (rs_TableName NAME,rs_TableDescription TEXT,rs_NumberOfAttribute INTEGER,rs_NumberOfChecks INTEGER,rs_hasPKey BOOLEAN,rs_hasIndex BOOLEAN,rs_hasSubClass BOOLEAN,rs_NumberOfRow INTEGER) AS $BODY$ DECLARE c_TableKind CONSTANT CHAR:='r'; v_TableOID OID; /*   */ v_TableName NAME; /*   */ v_TableDescription TEXT; /*   */ v_TableNumberOfRowCalc INTEGER; /*     */ --****************************************************************************************************** BEGIN SELECT INTO rs_TableName,rs_TableDescription,rs_NumberOfAttribute, rs_NumberOfChecks,rs_hasPKey,rs_hasIndex,rs_hasSubClass, rs_NumberOfRow tbl.relname,dsc.description,tbl.relnatts::INTEGER,tbl.relchecks::INTEGER, tbl.relhaspkey,tbl.relhasindex,tbl.relhassubclass,tbl.reltuples::INTEGER FROM pg_class tbl INNER JOIN pg_namespace nspc ON tbl.relnamespace = nspc.oid LEFT OUTER JOIN pg_Description dsc ON tbl.oid=dsc.objoid AND dsc.objsubid=0 WHERE nspc.nspname=LOWER(a_SchemaName) AND tbl.relkind=c_TableKind AND tbl.relname =LOWER(a_TableName); EXECUTE 'SELECT count(*) FROM ' ||LOWER(a_SchemaName) ||'.'||quote_ident(LOWER(a_TableName)) INTO v_TableNumberOfRowCalc; RETURN QUERY SELECT rs_TableName,rs_TableDescription,rs_NumberOfAttribute, rs_NumberOfChecks,rs_hasPKey,rs_hasIndex, rs_hasSubClass,v_TableNumberOfRowCalc AS rs_NumberOfRow; END $BODY$ LANGUAGE plpgsql; COMMENT ON FUNCTION admtf_Table_Features(a_SchemaName NAME,a_TableName NAME) IS '   ,  '; --ROLLBACK TRANSACTION; COMMIT TRANSACTION; BEGIN TRANSACTION; DROP FUNCTION IF EXISTS admtf_Table_Features (a_SchemaName VARCHAR(256),a_TableName VARCHAR(256)); /********************************************************************************************************/ /*     ,   */ /********************************************************************************************************/ CREATE OR REPLACE FUNCTION admtf_Table_Features (a_SchemaName VARCHAR(256) default 'public', /*     */ a_TableName VARCHAR(256) default NULL /*   */ ) RETURNS TABLE (rs_TableName VARCHAR(256),rs_TableDescription TEXT,rs_NumberOfAttribute INTEGER,rs_NumberOfChecks INTEGER,rs_hasPKey BOOLEAN,rs_hasIndex BOOLEAN,rs_hasSubClass BOOLEAN,rs_NumberOfRow INTEGER) AS $BODY$ DECLARE c_TableKind CONSTANT CHAR:='r'; v_TableOID OID; /*   */ v_TableName VARCHAR(256); /*   */ v_TableDescription TEXT; /*   */ v_TableNumberOfRowCalc INTEGER; /*     */ --****************************************************************************************************** BEGIN RETURN QUERY SELECT tf.rs_TableName::VARCHAR(256), tf.rs_TableDescription::TEXT, tf.rs_NumberOfAttribute::INTEGER, tf.rs_NumberOfChecks::INTEGER, tf.rs_hasPKey::BOOLEAN, tf.rs_hasIndex::BOOLEAN, tf.rs_hasSubClass::BOOLEAN, tf.rs_NumberOfRow::INTEGER FROM admtf_Table_Features(a_SchemaName::NAME,a_TableName::NAME) tf; END $BODY$ LANGUAGE plpgsql; COMMENT ON FUNCTION admtf_Table_Features(a_SchemaName VARCHAR(256),a_TableName VARCHAR(256)) IS '   ,  '; --ROLLBACK TRANSACTION; COMMIT TRANSACTION; SELECt * FROM admtf_Table_Features('public'::VARCHAR(256),'Street'::VARCHAR(256)); SELECt * FROM admtf_Table_Features('public':: NAME,'Street'::NAME);



创建admtf_Table_Attributes函数


有关功能源代码的注释，请参见此处。
函数源代码
 BEGIN TRANSACTION; DROP FUNCTION IF EXISTS admtf_Table_Attributes (a_SchemaName NAME,a_TableName NAME); /********************************************************************************************************/ /*      */ /********************************************************************************************************/ CREATE OR REPLACE FUNCTION admtf_Table_Attributes (a_SchemaName NAME default 'public', /*     */ a_TableName NAME default NULL /*   */ ) RETURNS TABLE (r_AttributeNumber SMALLINT,r_AttributeName NAME,r_UserTypeName NAME,r_TypeName NAME,r_isNotNULL BOOLEAN, r_Description Text) AS $BODY$ DECLARE c_TableKind CONSTANT CHAR:='r'; v_Scale INTEGER; /*   */ --****************************************************************************************************** BEGIN RETURN QUERY SELECT attr.attnum AS r_AttributeNumber, attr.attname::NAME AS r_AttributeName, CASE WHEN COALESCE(typ.typbasetype,0)>0 THEN typ.typname::NAME ELSE ''::NAME END AS r_UserTypeName, FORMAT_TYPE(COALESCE(NULLIF(typ.typbasetype,0),typ.oid), COALESCE(NULLIF(typ.typtypmod,-1),attr.atttypmod))::NAME AS r_TypeName, attr.attnotnull AS r_isNotNULL, dsc.description AS r_Description FROM pg_attribute attr INNER JOIN pg_class tbl ON tbl.oid = attr.attrelid INNER JOIN pg_namespace nsp ON tbl.relnamespace=nsp.oid LEFT OUTER JOIN pg_type typ ON attr.atttypid=typ.oid LEFT OUTER JOIN pg_type btyp ON typ.typbasetype=btyp.oid LEFT OUTER JOIN pg_description dsc ON dsc.objoid=attr.attrelid AND dsc.objsubid=attr.attnum WHERE LOWER(nsp.nspname)=LOWER(a_SchemaName) AND LOWER(tbl.relname)=LOWER(a_TableName) AND tbl.relkind=c_TableKind AND attr.attnum>0 AND attr.atttypID>0 ORDER BY tbl.relname,attr.attnum; RETURN; END $BODY$ LANGUAGE plpgsql; COMMENT ON FUNCTION admtf_Table_Attributes(a_SchemaName NAME,a_TableName NAME) IS '   '; --ROLLBACK TRANSACTION; COMMIT TRANSACTION; BEGIN TRANSACTION; DROP FUNCTION IF EXISTS admtf_Table_Attributes (a_SchemaName VARCHAR(256),a_TableName VARCHAR(256)); /********************************************************************************************************/ /*      */ /********************************************************************************************************/ CREATE OR REPLACE FUNCTION admtf_Table_Attributes (a_SchemaName VARCHAR(256) default 'public', /*     */ a_TableName VARCHAR(256) default NULL /*   */ ) RETURNS TABLE (r_AttributeNumber SMALLINT,r_AttributeName VARCHAR(256),r_UserTypeName VARCHAR(256),r_TypeName VARCHAR(256),r_isNotNULL BOOLEAN, r_Description Text) AS $BODY$ DECLARE c_TableKind CONSTANT CHAR:='r'; v_Scale INTEGER; /*   */ --****************************************************************************************************** BEGIN RETURN QUERY SELECT ta.r_AttributeNumber::SMALLINT, ta.r_AttributeName::VARCHAR(256), ta.r_UserTypeName::VARCHAR(256), ta.r_TypeName::VARCHAR(256), ta.r_isNotNULL::BOOLEAN, ta.r_Description::TEXT FROM admtf_Table_Attributes(a_SchemaName::NAME,a_TableName::NAME) ta; RETURN; END $BODY$ LANGUAGE plpgsql; COMMENT ON FUNCTION admtf_Table_Attributes(a_SchemaName VARCHAR(256),a_TableName VARCHAR(256)) IS '   '; --ROLLBACK TRANSACTION; COMMIT TRANSACTION; SELECT * FROM admtf_Table_Attributes('public'::VARCHAR(256),'Street'::VARCHAR(256)); SELECT * FROM admtf_Table_Attributes('public'::NAME,'Street'::NAME);


使用Regclass别名创建admtf_Table_Attributes函数


有关功能源代码的注释，请参见此处。
函数源代码
 BEGIN TRANSACTION; DROP FUNCTION IF EXISTS admtf_Table_Attributes (a_SchemaName NAME,a_TableName NAME); /********************************************************************************************************/ /*      */ /********************************************************************************************************/ CREATE OR REPLACE FUNCTION admtf_Table_Attributes (a_SchemaName NAME default 'public', /*     */ a_TableName NAME default NULL /*   */ ) RETURNS TABLE (r_AttributeNumber SMALLINT,r_AttributeName NAME,r_UserTypeName NAME,r_TypeName NAME,r_isNotNULL BOOLEAN, r_Description Text) AS $BODY$ DECLARE c_TableKind CONSTANT CHAR:='r'; v_Scale INTEGER; /*   */ --****************************************************************************************************** BEGIN RETURN QUERY SELECT attr.attnum AS r_AttributeNumber, attr.attname::NAME AS r_AttributeName, CASE WHEN COALESCE(typ.typbasetype,0)>0 THEN typ.typname::NAME ELSE ''::NAME END AS r_UserTypeName, FORMAT_TYPE(COALESCE(NULLIF(typ.typbasetype,0),typ.oid), COALESCE(NULLIF(typ.typtypmod,-1),attr.atttypmod))::NAME AS r_TypeName, attr.attnotnull AS r_isNotNULL, dsc.description AS r_Description FROM pg_attribute attr INNER JOIN pg_type typ ON attr.atttypid=typ.oid LEFT OUTER JOIN pg_type btyp ON typ.typbasetype=btyp.oid LEFT OUTER JOIN pg_description dsc ON dsc.objoid=attr.attrelid AND dsc.objsubid=attr.attnum WHERE attr.attrelid=(LOWER(a_SchemaName)||'.'|| LOWER(a_TableName))::regclass AND attr.attnum>0 AND attr.atttypID>0 ORDER BY attr.attnum; RETURN; END $BODY$ LANGUAGE plpgsql; COMMENT ON FUNCTION admtf_Table_Attributes(a_SchemaName NAME,a_TableName NAME) IS '   '; --ROLLBACK TRANSACTION; COMMIT TRANSACTION; BEGIN TRANSACTION; DROP FUNCTION IF EXISTS admtf_Table_Attributes (a_SchemaName VARCHAR(256),a_TableName VARCHAR(256)); /********************************************************************************************************/ /*      */ /********************************************************************************************************/ CREATE OR REPLACE FUNCTION admtf_Table_Attributes (a_SchemaName VARCHAR(256) default 'public', /*     */ a_TableName VARCHAR(256) default NULL /*   */ ) RETURNS TABLE (r_AttributeNumber SMALLINT,r_AttributeName VARCHAR(256),r_UserTypeName VARCHAR(256),r_TypeName VARCHAR(256),r_isNotNULL BOOLEAN, r_Description Text) AS $BODY$ DECLARE c_TableKind CONSTANT CHAR:='r'; v_Scale INTEGER; /*   */ --****************************************************************************************************** BEGIN RETURN QUERY SELECT ta.r_AttributeNumber::SMALLINT, ta.r_AttributeName::VARCHAR(256), ta.r_UserTypeName::VARCHAR(256), ta.r_TypeName::VARCHAR(256), ta.r_isNotNULL::BOOLEAN, ta.r_Description::TEXT FROM admtf_Table_Attributes(a_SchemaName::NAME,a_TableName::NAME) ta; RETURN; END $BODY$ LANGUAGE plpgsql; COMMENT ON FUNCTION admtf_Table_Attributes(a_SchemaName VARCHAR(256),a_TableName VARCHAR(256)) IS '   '; --ROLLBACK TRANSACTION; COMMIT TRANSACTION; SELECT * FROM admtf_Table_Attributes('public'::VARCHAR(256),'Street'::VARCHAR(256)); SELECT * FROM admtf_Table_Attributes('public'::NAME,'Street'::NAME);



附录2.其他材料


辅助数据库架构





  • 国家 -世界国家分类-OKSM（世界国家的全俄分类）；
  • HOUSEADDR-定居点街道上的门牌号码列表；
  • LCLTYTYPE-结算类型目录；
  • 地点 -和解清单；
  • STREET — ;
  • STREETTYPE — ;
  • TERRITORY — (, , , ..);
  • TERRITORYTYPE — .



Street ()


.
1. Street ().

类别分类不行职称评注类型基本类型？ 不为空
tbl0街定居点的街道清单
att1个wcrccode国家区号wcrccodeSmallintŤ
att2地区性社区ID地区性整数Ť
att3街头小贩村ID街街头小贩SmallintŤ
att4streettypeacrm街头缩写streettypeacrm人物（8）˚F
att5街道名街道名称streettypeacrmvarchar（150）Ť
pk0xpkstreet街道表主键
pkatt1个wcrccode国家区号wcrccodeSmallintŤ
pkatt2地区性社区ID地区性整数Ť
pkatt3街头小贩村ID街街头小贩SmallintŤ
fk011个fk_street_locality表外键
fk01att1个wcrccode国家区号wcrccodeSmallintŤ
fk01att2地区性社区ID地区性整数Ť
fk01rtbl0地区和解清单
fk01ratt1个wcrccode国家区号wcrccodeSmallintŤ
fk01ratt2地区性社区ID地区性整数Ť
fk022fk_street_streettype表外键
fk02att1个streettypeacrm街头缩写streettypeacrm人物（8）˚F
fk02rtbl0街道型街道类型参考
fk02ratt1个streettypeacrm街头缩写streettypeacrm人物（8）Ť
idx011个xie1street
idx01att1个wcrccodewcrccodesmallintŤ
idx01att2localityidlocalityidintegerŤ
idx01att3streettypeacrmstreettypeacrmcharacter(8)˚F
idx01att4streetnamevarchar(150)Ť
idx022xie2street
idx02att1个wcrccodewcrccodesmallinŤ
idx02att2localityidlocalityidintegerŤ
idx02att3streetnamevarchar(150)Ť
idx033xie3street
idx03att1个streetnamevarchar(150)Ť
idx044xpkstreet( ) street
idx04att1个wcrccodewcrccodesmallintŤ
idx04att2localityidlocalityidintegerŤ
idx04att3streetidstreetidsmallintŤ


另请参阅


记录PostgreSQL数据库的功能。 ;
记录PostgreSQL数据库的功能。 第三部分 。
记录PostgreSQL数据库的功能。 最后（第四部分） 。

Source: https://habr.com/ru/post/zh-CN415575/

More articles:


All Articles