وظائف لتوثيق قواعد بيانات PostgreSQL. الجزء الأول

SELECT * FROM admtf_Table_ComplexFeatures('pg_catalog','pg_class'); 

 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; 

 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; 

 /*       «   »*/ 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; 

 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); 

 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); 

 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);