Fungsi untuk mendokumentasikan database PostgreSQL. Bagian tiga

 SELECT pseq.relname AS SequenceName,snsp.nspname AS SequenceSchemaName, COALESCE(dsc.description,',    ' ||da.attname) AS SequenceDescription, d.depType AS DependcyType,da.attname AS AttributeName FROM pg_depend d INNER JOIN pg_class pseq ON d.objid = pseq.oid INNER JOIN pg_namespace snsp ON pseq.relnamespace=snsp.oid LEFT OUTER JOIN pg_Description dsc ON pseq.oid=dsc.objoid AND dsc.objsubid=0 INNER JOIN pg_class tbl ON d.refobjid = tbl.oid INNER JOIN pg_namespace nsp ON tbl.relnamespace=nsp.oid INNER JOIN pg_attribute da ON da.attrelid= d.refobjid AND da.attnum= d.refobjsubid WHERE LOWER(nsp.nspname)=LOWER(a_SchemaName) AND LOWER(tbl.relname)=LOWER(a_TableOID) AND tbl.relkind = 'r' AND pseq.relkind = 'S' ORDER BY pseq.relname; 

 SELECT RANK() OVER (PARTITION BY pseq.relname) AS SequenceNo, pseq.relname AS SequenceName,snsp.nspname AS SequenceSchemaName, COALESCE(dsc.description,',    ' ||da.attname) AS SequenceDescription, seq.seqstart AS SequenceStartValue,seq.seqincrement AS SequenceIncrementBy, seq.seqmax AS SequenceMaxValue, d.depType AS DependcyType,da.attname AS AttributeName FROM pg_depend d INNER JOIN pg_class pseq ON d.objid = pseq.oid INNER JOIN pg_sequence seq ON seq.seqrelid= pseq.oid INNER JOIN pg_namespace snsp ON pseq.relnamespace=snsp.oid LEFT OUTER JOIN pg_Description dsc ON pseq.oid=dsc.objoid AND dsc.objsubid=0 INNER JOIN pg_class tbl ON d.refobjid = tbl.oid INNER JOIN pg_namespace nsp ON tbl.relnamespace=nsp.oid INNER JOIN pg_attribute da ON da.attrelid= d.refobjid AND da.attnum= d.refobjsubid WHERE LOWER(nsp.nspname)=LOWER(a_SchemaName) AND LOWER(tbl.relname)=LOWER(a_TableOID) AND tbl.relkind = 'r' AND pseq.relkind = 'S' ORDER BY pseq.relname; 

 SELECT rtbl.relname,rnspc.nspname,rdsc.description,rtbl.relnatts::INTEGER, rtbl.relchecks::INTEGER, rtbl.relhaspkey,rtbl.relhasindex,rtbl.relhassubclass, rtbl.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 INNER JOIN pg_depend dp ON tbl.oid=dp.refobjid INNER JOIN pg_class rtbl ON rtbl.OID=dp.objid INNER JOIN pg_namespace rnspc ON rtbl.relnamespace = rnspc.oid LEFT OUTER JOIN pg_Description rdsc ON rtbl.oid=rdsc.objoid AND rdsc.objsubid=0 WHERE LOWER(nspc.nspname)=LOWER(a_SchemaName) AND LOWER(tbl.relname)=LOWER(a_TableOID) AND tbl.relkind = 'r' AND rtbl.relkind = 'r' ORDER BY rtbl.relname; 

 CASE WHEN a_Mode = 'exactly' THEN admfn_Table_RowCount(rnspc.nspname,rtbl.relname) ELSE reltuples END 

 BEGIN TRANSACTION; DROP FUNCTION IF EXISTS admtf_Sequence_Features (a_SchemaName NAME,a_SequenceName NAME); /****************************************************************************/ /*     ,   */ /****************************************************************************/ CREATE OR REPLACE FUNCTION admtf_Sequence_Features (a_SchemaName NAME default 'public', /*     */ a_SequenceName NAME default NULL /*   */ ) RETURNS TABLE (rs_SequenceName NAME,rs_SequenceDescription TEXT,rs_NumberOfAttribute INTEGER,rs_SequenceLastValue BIGINT, rs_SequenceStartValue BIGINT,rs_SequenceIncrementBy BIGINT,rs_SequenceMaxValue BIGINT) AS $BODY$ DECLARE c_SequenceKind CONSTANT CHAR:='S'; v_SequenceOID OID; /*   */ v_SequenceName NAME; /*   */ v_SequenceDescription TEXT; /*   */ v_SequenceStartValue BIGINT; /*    */ v_SequenceIncrementBy BIGINT; /*   */ v_SequenceMaxValue BIGINT; /*    */ v_SequenceLastValue BIGINT; /*    */ v_SequenceNumberOfRowCalc INTEGER; /*     */ --************************************************************************ BEGIN SELECT INTO rs_SequenceName,rs_SequenceDescription,rs_NumberOfAttribute tbl.relname, COALESCE(dsc.description,'') AS r_SequenceDescription, 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_SequenceKind AND tbl.relname =LOWER(a_SequenceName); IF FOUND THEN EXECUTE 'SELECT last_value,start_value,increment_by,max_value FROM '||LOWER(a_SchemaName)||'.'||LOWER(a_SequenceName) INTO v_SequenceLastValue,v_SequenceStartValue, v_SequenceIncrementBy,v_SequenceMaxValue ; RETURN QUERY SELECT rs_SequenceName,rs_SequenceDescription, rs_NumberOfAttribute,v_SequenceLastValue, v_SequenceStartValue,v_SequenceIncrementBy, v_SequenceMaxValue; END IF; RETURN; END $BODY$ LANGUAGE plpgsql; COMMENT ON FUNCTION admtf_Sequence_Features(a_SchemaName NAME,a_SequenceName NAME) IS '    ,  '; --ROLLBACK TRANSACTION; COMMIT TRANSACTION; BEGIN TRANSACTION; DROP FUNCTION IF EXISTS admtf_Sequence_Features (a_SchemaName VARCHAR(256),a_SequenceName VARCHAR(256)); /****************************************************************************/ /*     ,   */ /****************************************************************************/ CREATE OR REPLACE FUNCTION admtf_Sequence_Features (a_SchemaName VARCHAR(256) default 'public', /*     */ a_SequenceName VARCHAR(256) default NULL /*   */ ) RETURNS TABLE (rs_SequenceName VARCHAR(256),rs_SequenceDescription TEXT, rs_NumberOfAttribute INTEGER,rs_SequenceLastValue BIGINT, rs_SequenceStartValue BIGINT,rs_SequenceIncrementBy BIGINT, rs_SequenceMaxValue BIGINT) AS $BODY$ DECLARE c_SequenceKind CONSTANT CHAR:='S'; --******************************************************** BEGIN RETURN QUERY SELECT sf.rs_SequenceName::VARCHAR(256), sf.rs_SequenceDescription::TEXT, sf.rs_NumberOfAttribute::INTEGER, sf.rs_SequenceLastValue::BIGINT, sf.rs_SequenceStartValue::BIGINT, sf.rs_SequenceIncrementBy::BIGINT, sf.rs_SequenceMaxValue::BIGINT FROM admtf_Sequence_Features(a_SchemaName::NAME,a_SequenceName::NAME) sf; END $BODY$ LANGUAGE plpgsql; COMMENT ON FUNCTION admtf_Sequence_Features(a_SchemaName VARCHAR(256),a_SequenceName VARCHAR(256)) IS '    ,  '; --ROLLBACK TRANSACTION; COMMIT TRANSACTION; SELECT * FROM admtf_Sequence_Features('public'::VARCHAR(255),'k_dorogi_dijkstra_seq_seq'::VARCHAR(255)); SELECT * FROM admtf_Sequence_Features('public'::NAME,'kr_road_network_vertices_pgr_id_seq'::NAME); 

 BEGIN TRANSACTION; DROP FUNCTION IF EXISTS admtf_Table_Sequences (a_SchemaName NAME, a_TableName NAME); /*********************************************************************/ /*    ,     */ /*********************************************************************/ CREATE OR REPLACE FUNCTION admtf_Table_Sequences (a_SchemaName NAME default 'public', /*     */ a_TableName NAME default NULL /*   */ ) RETURNS TABLE (r_SequenceNumber SMALLINT,r_SequenceName NAME, r_SequenceSchemaName NAME,r_SequenceDescription TEXT, r_SequenceStartValue BIGINT,r_SequenceIncrementBy BIGINT, r_SequenceMaxValue BIGINT,r_DependType NAME, r_RefTableName NAME,r_RefTableSchemaName NAME, r_RefAttributeName NAME) AS $BODY$ DECLARE v_TableOID INTEGER;/* OID */ v_Sequence RECORD;/*   */ v_SequenceOID INTEGER;/* OID */ v_SequenceName NAME; /*   */ v_SequenceSchemaName NAME; /*    */ v_SequenceDescription TEXT; /*   */ v_SequenceStartValue BIGINT; /*    */ v_SequenceIncrementBy BIGINT; /*   */ v_SequenceMaxValue BIGINT; /*    */ v_DependcyType NAME; /*     */ /*    */ v_AttributeName NAME; /*  */ v_SequenceNumber SMALLINT; /*   */ c_Delimiter CONSTANT VARCHAR(2):=','; --********************************************************************* BEGIN v_SequenceNumber:=0; FOR v_Sequence IN SELECT pseq.relname AS SequenceName, snsp.nspname AS SequenceSchemaName, COALESCE(dsc.description,',    '||da.attname) AS SequenceDescription, d.depType AS DependcyType,da.attname AS AttributeName FROM pg_depend d INNER JOIN pg_class pseq ON d.objid = pseq.oid INNER JOIN pg_namespace snsp ON pseq.relnamespace=snsp.oid LEFT OUTER JOIN pg_Description dsc ON pseq.oid=dsc.objoid AND dsc.objsubid=0 INNER JOIN pg_class tbl ON d.refobjid = tbl.oid INNER JOIN pg_namespace nsp ON tbl.relnamespace=nsp.oid INNER JOIN pg_attribute da ON da.attrelid= d.refobjid AND d.refobjsubid=da.attnum WHERE tbl.relkind = 'r' AND pseq.relkind = 'S' AND LOWER(nsp.nspname)=LOWER(a_SchemaName) AND LOWER(tbl.relname)=LOWER(a_TableName) ORDER BY pseq.relname LOOP v_SequenceNumber:=v_SequenceNumber+1; v_SequenceName:=v_Sequence.SequenceName; v_SequenceSchemaName:=v_Sequence.SequenceSchemaName; v_DependcyType:=v_Sequence.DependcyType; v_AttributeName:=v_Sequence.AttributeName; v_SequenceDescription:=v_Sequence.SequenceDescription; SELECT INTO v_SequenceStartValue,v_SequenceIncrementBy, v_SequenceMaxValue rs_SequenceStartValue,rs_SequenceIncrementBy, rs_SequenceMaxValue FROM admtf_Sequence_Features(v_SequenceSchemaName,v_SequenceName); RETURN QUERY SELECT v_SequenceNumber,v_SequenceName, v_SequenceSchemaName,v_SequenceDescription, v_SequenceStartValue,v_SequenceIncrementBy, v_SequenceMaxValue,v_DependcyType, a_TableName,a_SchemaName,v_AttributeName; END LOOP; RETURN; END $BODY$ LANGUAGE plpgsql; COMMENT ON FUNCTION admtf_Table_Sequences(a_SchemaName NAME, a_TableName NAME) IS '  ,    '; --ROLLBACK TRANSACTION; COMMIT TRANSACTION; BEGIN TRANSACTION; DROP FUNCTION IF EXISTS admtf_Table_Sequences (a_SchemaName VARCHAR(256), a_TableName VARCHAR(256)); /**********************************************************************/ /*    ,     */ /**********************************************************************/ CREATE OR REPLACE FUNCTION admtf_Table_Sequences (a_SchemaName VARCHAR(256) default 'public', /*     */ a_TableName VARCHAR(256) default NULL /*   */ ) RETURNS TABLE (r_SequenceNumber SMALLINT,r_SequenceName VARCHAR(256), r_SequenceSchemaName VARCHAR(256),r_SequenceDescription TEXT, r_SequenceStartValue BIGINT,r_SequenceIncrementBy BIGINT, r_SequenceMaxValue BIGINT,r_DependType VARCHAR(256), r_RefTableName VARCHAR(256),r_RefTableSchemaName VARCHAR(256), r_RefAttributeName VARCHAR(256)) AS $BODY$ DECLARE c_Delimiter CONSTANT VARCHAR(2):=','; --****************************************************** BEGIN RETURN QUERY SELECT ts.r_SequenceNumber::SMALLINT, ts.r_SequenceName::VARCHAR(256), ts.r_SequenceSchemaName::VARCHAR(256) , ts.r_SequenceDescription::TEXT, ts.r_SequenceStartValue::BIGINT, ts.r_SequenceIncrementBy::BIGINT, ts.r_SequenceMaxValue::BIGINT, ts.r_DependType::VARCHAR(256), ts.r_RefTableName::VARCHAR(256), ts.r_RefTableSchemaName::VARCHAR(256), ts.r_RefAttributeName::VARCHAR(256) FROM admtf_Table_Sequences(a_SchemaName::NAME,a_TableName::NAME) ts; END $BODY$ LANGUAGE plpgsql; COMMENT ON FUNCTION admtf_Table_Sequences(a_SchemaName VARCHAR(256), a_TableName VARCHAR(256)) IS '  ,    '; --ROLLBACK TRANSACTION; COMMIT TRANSACTION; SELECT * FROM admtf_Table_Sequences('public'::VARCHAR(255),'kr_road_network_vertices_pgr'::VARCHAR(255)); SELECT * FROM admtf_Table_Sequences('public'::NAME,'kr_road_network_vertices_pgr'::NAME); 

 BEGIN TRANSACTION; DROP FUNCTION IF EXISTS admtf_Table_Sequences (a_SchemaName NAME, a_TableName NAME); /*********************************************************************/ /*    ,     */ /**********************************************************************/ CREATE OR REPLACE FUNCTION admtf_Table_Sequences (a_SchemaName NAME default 'public', /*     */ a_TableName NAME default NULL /*   */ ) RETURNS TABLE (r_SequenceNumber SMALLINT,r_SequenceName NAME, r_SequenceSchemaName NAME,r_SequenceDescription TEXT, r_SequenceStartValue BIGINT,r_SequenceIncrementBy BIGINT, r_SequenceMaxValue BIGINT,r_DependType NAME, r_RefTableName NAME,r_RefTableSchemaName NAME, r_RefAttributeName NAME) AS $BODY$ DECLARE v_TableOID INTEGER; /* OID */ v_Sequence RECORD; /*   */ v_SequenceOID INTEGER; /* OID */ v_SequenceName NAME; /*   */ v_SequenceSchemaName NAME; /*    */ v_SequenceDescription TEXT; /*   */ v_SequenceStartValue BIGINT; /*    */ v_SequenceIncrementBy BIGINT; /*   */ v_SequenceMaxValue BIGINT; /*    */ v_DependcyType NAME; /*        */ v_AttributeName NAME; /*  */ v_SequenceNumber SMALLINT; /*   */ c_Delimiter CONSTANT VARCHAR(2):=','; --****************************************************************** BEGIN v_SequenceNumber:=0; FOR v_Sequence IN SELECT pseq.relname AS SequenceName, snsp.nspname AS SequenceSchemaName, COALESCE(dsc.description,',    '||da.attname) AS SequenceDescription, d.depType AS DependcyType,da.attname AS AttributeName FROM pg_depend d INNER JOIN pg_class pseq ON d.objid = pseq.oid INNER JOIN pg_namespace snsp ON pseq.relnamespace=snsp.oid LEFT OUTER JOIN pg_Description dsc ON pseq.oid=dsc.objoid AND dsc.objsubid=0 INNER JOIN pg_class tbl ON d.refobjid = tbl.oid INNER JOIN pg_namespace nsp ON tbl.relnamespace=nsp.oid INNER JOIN pg_attribute da ON da.attrelid= d.refobjid ND d.refobjsubid=da.attnum WHERE tbl.relkind = 'r' AND pseq.relkind = 'S' AND LOWER(nsp.nspname)=LOWER(a_SchemaName) AND LOWER(tbl.relname)=LOWER(a_TableName) ORDER BY pseq.relname LOOP v_SequenceNumber:=v_SequenceNumber+1; v_SequenceName:=v_Sequence.SequenceName; v_SequenceSchemaName:=v_Sequence.SequenceSchemaName; v_DependcyType:=v_Sequence.DependcyType; v_AttributeName:=v_Sequence.AttributeName; v_SequenceDescription:=v_Sequence.SequenceDescription; SELECT INTO v_SequenceStartValue,v_SequenceIncrementBy,v_SequenceMaxValue rs_SequenceStartValue,rs_SequenceIncrementBy,rs_SequenceMaxValue FROM admtf_Sequence_Features(v_SequenceSchemaName,v_SequenceName); RETURN QUERY SELECT v_SequenceNumber,v_SequenceName, v_SequenceSchemaName,v_SequenceDescription, v_SequenceStartValue,v_SequenceIncrementBy, v_SequenceMaxValue,v_DependcyType, a_TableName,a_SchemaName,v_AttributeName; END LOOP; RETURN; END $BODY$ LANGUAGE plpgsql; COMMENT ON FUNCTION admtf_Table_Sequences(a_SchemaName NAME, a_TableName NAME) IS '  ,    '; --ROLLBACK TRANSACTION; COMMIT TRANSACTION; BEGIN TRANSACTION; DROP FUNCTION IF EXISTS admtf_Table_Sequences (a_SchemaName VARCHAR(256), a_TableName VARCHAR(256)); /**********************************************************************/ /*    ,     */ /**********************************************************************/ CREATE OR REPLACE FUNCTION admtf_Table_Sequences (a_SchemaName VARCHAR(256) default 'public', /*     */ a_TableName VARCHAR(256) default NULL /*   */ ) RETURNS TABLE (r_SequenceNumber SMALLINT,r_SequenceName VARCHAR(256), r_SequenceSchemaName VARCHAR(256),r_SequenceDescription TEXT, r_SequenceStartValue BIGINT,r_SequenceIncrementBy BIGINT, r_SequenceMaxValue BIGINT,r_DependType VARCHAR(256), r_RefTableName VARCHAR(256),r_RefTableSchemaName VARCHAR(256), r_RefAttributeName VARCHAR(256)) AS $BODY$ DECLARE c_Delimiter CONSTANT VARCHAR(2):=','; --******************************************************* BEGIN RETURN QUERY SELECT ts.r_SequenceNumber::SMALLINT, ts.r_SequenceName::VARCHAR(256), ts.r_SequenceSchemaName::VARCHAR(256), ts.r_SequenceDescription::TEXT, ts.r_SequenceStartValue::BIGINT, ts.r_SequenceIncrementBy::BIGINT, ts.r_SequenceMaxValue::BIGINT, ts.r_DependType::VARCHAR(256), ts.r_RefTableName::VARCHAR(256), ts.r_RefTableSchemaName::VARCHAR(256), ts.r_RefAttributeName::VARCHAR(256) FROM admtf_Table_Sequences(a_SchemaName::NAME,a_TableName::NAME) ts; END $BODY$ LANGUAGE plpgsql; COMMENT ON FUNCTION admtf_Table_Sequences(a_SchemaName VARCHAR(256), a_TableName VARCHAR(256)) IS '  ,    '; --ROLLBACK TRANSACTION; COMMIT TRANSACTION; SELECT * FROM admtf_Table_Sequences('public'::VARCHAR(255), 'kr_road_network_vertices_pgr'::VARCHAR(255)); SELECT * FROM admtf_Table_Sequences('public'::NAME, 'kr_road_network_vertices_pgr'::NAME); 

 BEGIN TRANSACTION; DROP FUNCTION IF EXISTS admfn_Table_RowCount (a_SchemaName NAME,a_TableName NAME); /******************************************************/ /*       */ /******************************************************/ CREATE OR REPLACE FUNCTION admfn_Table_RowCount (a_SchemaName NAME default 'public',/*     */ a_TableName NAME default NULL /*   */ ) RETURNS BIGINT AS $BODY$ DECLARE v_TableNumberOfRowCalc BIGINT; /*  */ v_Found BOOLEAN; --*********************************************************** BEGIN IF a_SchemaName ~ E'^[a-z_0-9]+$' AND a_TableName ~ E'^[a-z_0-9]+$' THEN EXECUTE 'SELECT count(*) FROM ' ||a_SchemaName ||'.'|| a_TableName INTO v_TableNumberOfRowCalc; ELSE SELECT INTO v_Found true FROM pg_class tbl INNER JOIN pg_namespace nspc ON tbl.relnamespace = nspc.oid WHERE tbl.relkind='r' AND tbl.relname=a_TableName AND nspc.nspname=a_SchemaName; IF FOUND THEN EXECUTE 'SELECT count(*) FROM ' || CASE WHEN a_SchemaName ~ E'^[a-z_0-9]+$' THEN a_SchemaName ELSE quote_ident(a_SchemaName) END ||'.'|| CASE WHEN a_TableName ~ E'^[a-z_0-9]+$' THEN a_TableName ELSE quote_ident(a_TableName) END INTO v_TableNumberOfRowCalc; ELSE SELECT INTO v_Found true FROM pg_class tbl INNER JOIN pg_namespace nspc ON tbl.relnamespace = nspc.oid WHERE tbl.relkind='r' AND LOWER(tbl.relname)= LOWER(a_TableName) AND nspc.nspname=LOWER(a_SchemaName); IF FOUND THEN EXECUTE 'SELECT count(*) FROM ' || a_SchemaName ||'.'||a_TableName INTO v_TableNumberOfRowCalc; END IF; END IF; END IF; RETURN v_TableNumberOfRowCalc; END $BODY$ LANGUAGE plpgsql; COMMENT ON FUNCTION admfn_Table_RowCount(a_SchemaName NAME,a_TableName NAME) IS '    '; --ROLLBACK TRANSACTION; COMMIT TRANSACTION;BEGIN TRANSACTION; DROP FUNCTION IF EXISTS admfn_Table_RowCount (a_SchemaName VARCHAR(256),a_TableName VARCHAR(256)); /********************************************************************/ /*       */ /********************************************************************/ CREATE OR REPLACE FUNCTION admfn_Table_RowCount (a_SchemaName VARCHAR(256) default 'public',/*     */ a_TableName VARCHAR(256) default NULL /*   */ ) RETURNS BIGINT AS $BODY$ DECLARE v_TableNumberOfRowCalc BIGINT; /*  */ --********************************************************* BEGIN RETURN admfn_Table_RowCount(a_SchemaName::NAME,a_TableName::NAME); END $BODY$ LANGUAGE plpgsql; COMMENT ON FUNCTION admfn_Table_RowCount(a_SchemaName VARCHAR(256),a_TableName VARCHAR(256)) IS '    '; --ROLLBACK TRANSACTION; COMMIT TRANSACTION; SELECt admfn_Table_RowCount('public'::NAME,'Street'::NAME); SELECt admfn_Table_RowCount('public'::VARCHAR(256),'Street'::VARCHAR(256)); 

 BEGIN TRANSACTION; DROP FUNCTION IF EXISTS admtf_Table_InheritanceChildrens (a_SchemaName NAME,a_TableName NAME,a_Mode VARCHAR(10)); /************************************************************/ /*       */ /************************************************************/ CREATE OR REPLACE FUNCTION admtf_Table_InheritanceChildrens (a_SchemaName NAME default 'public', /*     */ a_TableName NAME default NULL, /*   */ a_Mode VARCHAR(10) default 'estimate' /*     */ ) 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'; c_ExactlyMode CONSTANT VARCHAR(10):='exactly'; c_EstimateMode CONSTANT VARCHAR(10):='estimate'; v_TableOID OID; /*   */ v_SchemaName NAME; /*    */ v_TableName NAME; /*   */ v_TableDescription TEXT; /*   */ v_TableNumberOfRowCalc INTEGER; /*     */ v_InheritanceRECORD RECORD; /*    */ v_InheritanceOID OID; /*    */ BEGIN RETURN QUERY SELECT rtbl.relname,rdsc.description,rtbl.relnatts::INTEGER, rtbl.relchecks::INTEGER,rtbl.relhaspkey,rtbl.relhasindex, rtbl.relhassubclass, CASE WHEN a_Mode=c_ExactlyMode THEN admfn_Table_RowCount(rnspc.nspname,rtbl.relname)::INTEGER ELSE rtbl.reltuples::INTEGER END 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 INNER JOIN pg_depend dp ON tbl.oid=dp.refobjid INNER JOIN pg_class rtbl ON rtbl.OID=dp.objid INNER JOIN pg_namespace rnspc ON rtbl.relnamespace = rnspc.oid LEFT OUTER JOIN pg_Description rdsc ON rtbl.oid=rdsc.objoid AND rdsc.objsubid=0 WHERE nspc.nspname=LOWER(a_SchemaName) AND tbl.relkind=c_TableKind AND rtbl.relkind=c_TableKind AND tbl.relname =LOWER(a_TableName) ORDER BY rtbl.relname; END $BODY$ LANGUAGE plpgsql; COMMENT ON FUNCTION admtf_Table_InheritanceChildrens(a_SchemaName NAME,a_TableName NAME,a_Mode VARCHAR(10)) IS '    '; --ROLLBACK TRANSACTION; COMMIT TRANSACTION; BEGIN TRANSACTION; DROP FUNCTION IF EXISTS admtf_Table_InheritanceChildrens (a_SchemaName VARCHAR(256),a_TableName VARCHAR(256),a_TableName NAME,a_Mode VARCHAR(10)); /************************************************************************/ /*       */ /************************************************************************/ CREATE OR REPLACE FUNCTION admtf_Table_InheritanceChildrens (a_SchemaName VARCHAR(256) default 'public',/*     */ a_TableName VARCHAR(256) default NULL,/*   */ a_Mode VARCHAR(10) default 'estimate' /*     */ ) 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'; BEGIN RETURN QUERY SELECT tic.rs_TableName::VARCHAR(256),tic.rs_TableDescription::TEXT, tic.rs_NumberOfAttribute::INTEGER,tic.rs_NumberOfChecks::INTEGER, tic.rs_hasPKey::BOOLEAN,tic.rs_hasIndex::BOOLEAN, tic.rs_hasSubClass::BOOLEAN,tic.rs_NumberOfRow::INTEGER FROM admtf_Table_InheritanceChildrens(a_SchemaName::NAME, a_TableName::NAME,a_Mode::VARCHAR(10)) tic; END $BODY$ LANGUAGE plpgsql; COMMENT ON FUNCTION admtf_Table_InheritanceChildrens(a_SchemaName VARCHAR(256),a_TableName VARCHAR(256),a_Mode VARCHAR(10)) IS '    '; --ROLLBACK TRANSACTION; COMMIT TRANSACTION; SELECT * FROM admtf_Table_InheritanceChildrens('public'::NAME,'np_house'::NAME); SELECT * FROM admtf_Table_InheritanceChildrens('public'::VARCHAR(256),'np_house'::VARCHAR(256)); 

 BEGIN TRANSACTION; DROP FUNCTION IF EXISTS admtf_Attribute_PKFeatures (a_TableOID OID,a_AttributeNo SMALLINT); /***************************************************************************/ /*        . */ /*   ,          */ /*   */ /***************************************************************************/ CREATE OR REPLACE FUNCTION admtf_Attribute_PKFeatures (a_TableOID OID, /*   */ a_AttributeNo SMALLINT /*     */ ) RETURNS TABLE (rs_isAttributePK BOOLEAN,rs_PKeyName name,rs_ColumnPKNo SMALLINT) AS $BODY$ DECLARE C_PKAttributeList_NDims CONSTANT INTEGER:=1; /*     */ v_PKAttributeList SMALLINT[]; /*       */ v_PKAttributeIndx INTEGER; /*      */ v_PKAttributeLBound INTEGER; /*      */ v_PKAttributeUBound INTEGER; /*      */ --********************************************************************** BEGIN rs_isAttributePK:=false; rs_ColumnPKNo:=NULL; SELECT INTO rs_PKeyName,v_PKAttributeList,rs_isAttributePK conname,conkey,ARRAY[a_AttributeNo]<@conkey FROM pg_constraint c WHERE c.contype='p' and c.conrelid=a_TableOID; IF FOUND AND rs_isAttributePK THEN --      v_PKAttributeLBound:=array_lower(v_PKAttributeList,C_PKAttributeList_NDims); v_PKAttributeUBound:=array_upper(v_PKAttributeList,C_PKAttributeList_NDims); v_PKAttributeIndx:=v_PKAttributeLBound; WHILE v_PKAttributeIndx <= v_PKAttributeUBound AND a_AttributeNo<>v_PKAttributeList[v_PKAttributeIndx] LOOP v_PKAttributeIndx:=v_PKAttributeIndx+1; END LOOP; IF v_PKAttributeIndx<=v_PKAttributeUBound THEN rs_ColumnPKNo:=v_PKAttributeIndx; END IF; END IF; RETURN QUERY SELECT rs_isAttributePK,rs_PKeyName,rs_ColumnPKNo; END $BODY$ LANGUAGE plpgsql; COMMENT ON FUNCTION admtf_Attribute_PKFeatures(a_TableOID OID,a_AttributeNo SMALLINT) IS '              '; --ROLLBACK TRANSACTION; COMMIT TRANSACTION; SELECT * FROM admtf_Attribute_PKFeatures((SELECT OID FROM pg_class WHERE relname='street'),3::SMALLINT); 

 BEGIN TRANSACTION; DROP FUNCTION IF EXISTS admtf_Attribute_FKFeatures (a_TableOID OID,a_AttributeNo SMALLINT); /****************************************************************************/ /*        . */ /*   ,         */ /*   . */ /****************************************************************************/ /****************************************************************************/ CREATE OR REPLACE FUNCTION admtf_Attribute_FKFeatures (a_TableOID OID, /*   */ a_AttributeNo SMALLINT /*     */ ) RETURNS TABLE (rs_isAttributeFK BOOLEAN,rs_FKeyName name[],rs_ColumnFKNo SMALLINT[],rs_FKTableName name[],rs_FKTableColumnName name[]) AS $BODY$ DECLARE C_FKAttributeList_NDims CONSTANT INTEGER:=1; /*     */ v_FKAttributeList SMALLINT[]; /*       */ v_RefAttributeList SMALLINT[]; /*     , */ /*     */ v_FKAttributeIndx INTEGER; /*      */ v_RefAttributeListIndx INTEGER; /*     , */ /*     */ v_FKAttributeLBound INTEGER; /*      */ v_FKAttributeUBound INTEGER; /*      */ v_FKConstraintIndx INTEGER; /*     */ v_FKeyName name; /*   , */ /*     */ v_FKTableName name; /*  ,     */ v_FKTableColumnName name; /*    , */ /*     */ v_RefAttributeNo SMALLINT; /*     , */ /*     */ v_Constraint pg_constraint%ROWTYPE; /*     */ /*  (CONSTRANT) */ --****************************************************************************************************** BEGIN rs_isAttributeFK:=false; rs_ColumnFKNo:=NULL; v_FKConstraintIndx:=0; FOR v_Constraint IN SELECT * FROM pg_constraint c WHERE c.contype='f' and c.conrelid=a_TableOID AND ARRAY[a_AttributeNo]<@conkey ORDER BY c.oid LOOP v_FKConstraintIndx:=v_FKConstraintIndx+1; rs_isAttributeFK:=true; v_FKeyName:=v_Constraint.conname; v_FKAttributeList:=v_Constraint.conkey; v_RefAttributeList:=v_Constraint.confkey; v_FKAttributeLBound:=array_lower(v_FKAttributeList,C_FKAttributeList_NDims); v_FKAttributeUBound:=array_upper(v_FKAttributeList,C_FKAttributeList_NDims); v_FKAttributeIndx:=v_FKAttributeLBound; WHILE v_FKAttributeIndx <= v_FKAttributeUBound AND a_AttributeNo<>v_FKAttributeList[v_FKAttributeIndx] LOOP v_FKAttributeIndx:=v_FKAttributeIndx+1; END LOOP; rs_FKeyName[v_FKConstraintIndx]:=v_FKeyName; rs_ColumnFKNo[v_FKConstraintIndx]:=v_FKAttributeIndx; SELECT INTO v_FKTableName ftbl.relname FROM pg_class ftbl WHERE ftbl.oid=v_Constraint.confrelid; rs_FKTableName[v_FKConstraintIndx]:=v_FKTableName; v_RefAttributeNo:=v_RefAttributeList[v_FKAttributeIndx]; v_FKTableColumnName:=NULL; SELECT INTO v_FKTableColumnName attname FROM pg_attribute a WHERE a.attrelid=v_Constraint.confrelid AND a.attnum=v_RefAttributeNo; rs_FKTableColumnName[v_FKConstraintIndx]:=v_FKTableColumnName; END LOOP; RETURN QUERY SELECT rs_isAttributeFK,rs_FKeyName,rs_ColumnFKNo, rs_FKTableName,rs_FKTableColumnName; END $BODY$ LANGUAGE plpgsql; COMMENT ON FUNCTION admtf_Attribute_FKFeatures(a_TableOID OID,a_AttributeNo SMALLINT) IS '              '; --ROLLBACK TRANSACTION; COMMIT TRANSACTION; SELECT * FROM admtf_Attribute_FKFeatures((SELECT OID FROM pg_class WHERE relname='street'),4::SMALLINT); 

 BEGIN TRANSACTION; DROP FUNCTION IF EXISTS admtf_Attribute_Features (a_TableOID OID,a_AttributeNo SMALLINT); /****************************************************************************/ /*      */ /****************************************************************************/ CREATE OR REPLACE FUNCTION admtf_Attribute_Features (a_TableOID OID, /*   */ a_AttributeNo SMALLINT/*     */ ) RETURNS TABLE (rsa_AttributeName name,rsa_UserTypeName VARCHAR(256),rsa_TypeName VARCHAR(256),rsa_isNotNULL BOOLEAN,rsa_isAttributePK BOOLEAN, rsa_ColumnPKNo SMALLINT,rsa_Description Text,rsa_isAttributeFK BOOLEAN,rsa_FKeyName name[],rsa_ColumnFKNo SMALLINT[],rsa_FKTableName name[],rsa_FKTableColumnName name[]) AS $BODY$ DECLARE v_Return_Error Integer := 0; /*  */ --********************************************************************* BEGIN SELECT INTO rsa_AttributeName,rsa_UserTypeName,rsa_TypeName, rsa_isNotNULL,rsa_Description attr.attname, CASE WHEN COALESCE(typ.typbasetype,0)>0 THEN typ.typname::VARCHAR(100) ELSE ''END AS r_UserTypeName, FORMAT_TYPE(COALESCE(NULLIF(typ.typbasetype,0),typ.oid), COALESCE(NULLIF(typ.typtypmod,-1),attr.atttypmod))::VARCHAR(256) AS r_TypeName, attr.attnotnull AS r_isNotNULL, dsc.description AS r_Description FROM pg_attribute attr 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 attr.attrelid =a_TableOID AND attr.attnum=a_AttributeNo; SELECT INTO rsa_isAttributePK,rsa_ColumnPKNo rs_isAttributePK,rs_ColumnPKNo FROM admtf_Attribute_PKFeatures(a_TableOID,a_AttributeNo); SELECT INTO rsa_isAttributeFK,rsa_FKeyName,rsa_ColumnFKNo,rsa_FKTableName, rsa_FKTableColumnName rs_isAttributeFK,rs_FKeyName, rs_ColumnFKNo,rs_FKTableName,rs_FKTableColumnName FROM admtf_Attribute_FKFeatures(a_TableOID,a_AttributeNo); RETURN QUERY SELECT rsa_AttributeName,rsa_UserTypeName,rsa_TypeName,rsa_isNotNULL, rsa_isAttributePK,rsa_ColumnPKNo,rsa_Description,rsa_isAttributeFK, rsa_FKeyName,rsa_ColumnFKNo,rsa_FKTableName,rsa_FKTableColumnName; END $BODY$ LANGUAGE plpgsql; COMMENT ON FUNCTION admtf_Attribute_Features(a_TableOID OID,a_AttributeNo SMALLINT) IS '   '; --ROLLBACK TRANSACTION; COMMIT TRANSACTION; SELECT * FROM admtf_Attribute_Features ((SELECT OID FROM pg_class WHERE relname='street'),2::SMALLINT);