Функции для документирования баз данных PostgreSQL. Окончание01.09.2018 18:19

SELECT con.conname, 
              COALESCE(dsc.description,'Первичный ключ таблицы '|| tbl.relname) 
            FROM pg_constraint con 
                INNER JOIN pg_namespace nspc ON con.connamespace = nspc.oid 
                INNER JOIN pg_class tbl ON con.conrelid=tbl.oid
                LEFT OUTER JOIN pg_Description dsc ON con.oid=dsc.objoid
                                                AND dsc.objsubid=0 
           WHERE con.contype ='p' AND nspc.nspname=LOWER(a_SchemaName)
               AND con.conname =LOWER(a_PrimaryKeyName);

SELECT (rank() OVER (PARTITION BY con.conrelid ORDER BY con.No)) ::SMALLINT,
      attr.attnum,attr.attname::NAME,
      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
    FROM (SELECT c.oid, c.conrelid,c.connamespace,c.confrelid,c.conname, c.contype,
                      c.conkey::SMALLINT[],generate_subscripts(c.conkey, 1) as No 
                 FROM pg_constraint c) con
                    INNER JOIN pg_namespace nspc ON con.connamespace = nspc.oid
                     INNER JOIN pg_attribute attr ON attr.attrelid=con.conrelid
                            AND attr.attnum=con.conkey[con.No]
                     LEFT OUTER JOIN pg_type typ ON attr.atttypid=typ.oid
                     LEFT OUTER JOIN pg_type btyp ON typ.typbasetype=btyp.oid
    WHERE con.contype ='p' AND nspc.nspname=LOWER(a_SchemaName)
                           AND con.conname =LOWER(a_PrimaryKeyName)
    ORDER BY con.No;

SELECT con.conname, COALESCE(dsc.description,'Внешний ключ таблицы '|| tbl.relname)  
    FROM pg_constraint con 
               INNER JOIN pg_namespace nspc ON con.connamespace = nspc.oid 
               INNER JOIN pg_class tbl ON con.conrelid=tbl.oid
               LEFT OUTER JOIN pg_Description dsc ON con.oid=dsc.objoid AND dsc.objsubid=0
    WHERE nspc.nspname=LOWER(a_SchemaName) AND con.contype='f'
                                        AND con.conname =LOWER(a_ForeignKeyName);

SELECT (rank() OVER (PARTITION BY con.conrelid ORDER BY con.No))::SMALLINT AS r_ForeingKeyNo,
               attr.attnum AS r_AttributeNumber,attr.attname::NAME AS r_AttributeName,
              CASE WHEN COALESCE(typ.typbasetype,0)>0 THEN typ.typname::NAME ELSE '' 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,TRIM(dsc.description) AS r_Description 
    FROM (SELECT c.oid, c.conrelid,c.confrelid,c.conname,c.connamespace,c.contype,c.conkey::SMALLINT[],c.consrc,
                                 c.confkey::SMALLINT[],generate_subscripts(c.conkey, 1) as No FROM pg_constraint c) con
               INNER JOIN pg_namespace nspc ON con.connamespace = nspc.oid
               INNER JOIN pg_attribute attr ON attr.attrelid=con.conrelid AND attr.attnum=con.conkey[con.No]
               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 nspc.nspname=LOWER(a_SchemaName) AND con.contype ='f' AND con.conname =LOWER(a_ForeignKeyName)
    ORDER BY con.No;    

SELECT (rank() OVER (PARTITION BY con.confrelid ORDER BY con.No))::SMALLINT,
               attr.attnum AS r_AttributeNumber,attr.attname::NAME AS r_AttributeName,
               CASE WHEN COALESCE(typ.typbasetype,0)>0 THEN typ.typname::NAME ELSE ''END,
               FORMAT_TYPE(COALESCE(NULLIF(typ.typbasetype,0),typ.oid),
                                 COALESCE(NULLIF(typ.typtypmod,-1),attr.atttypmod))::NAME,
               attr.attnotnull AS r_isNotNULL,TRIM(dsc.description) AS r_Description 
    FROM (SELECT c.oid, c.conrelid,c.confrelid,c.conname,c.connamespace,c.contype,
                           c.conkey::SMALLINT[],c.consrc,c.confkey::SMALLINT[],
                           generate_subscripts(c.conkey, 1) as No 
                    FROM pg_constraint c) con 
          INNER JOIN pg_namespace nspc ON con.connamespace = nspc.oid
          INNER JOIN pg_attribute attr ON attr.attrelid=con.confrelid 
                           AND attr.attnum=con.confkey[con.No]
          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 nspc.nspname=LOWER(a_SchemaName) AND con.contype ='f' 
                          AND con.conname =LOWER(a_ForeignKeyName)
    ORDER BY con.No;    

SELECT inxcls.relname, 
      CASE WHEN COALESCE(TRIM(dsc.description),'')='' THEN 
          'Индекс'  || CASE WHEN inx.indisunique THEN ' уникальный' 
          || CASE WHEN inx.indisprimary THEN '(первичный ключ)' ELSE '' END ELSE '' END
          ||CASE WHEN inxam.amname='gist' THEN ' пространственный' ELSE '' END ||' таблицы '
          ||tbl.relname
                ELSE dsc.description END        
     FROM pg_index inx 
                INNER JOIN pg_class inxcls ON inx.indexrelid=inxcls.oid
                INNER JOIN pg_namespace nsp ON inxcls.relnamespace=nsp.oid 
                LEFT OUTER JOIN pg_Description dsc ON inxcls.oid=dsc.objoid
                                                AND dsc.objsubid=0 
                LEFT OUTER JOIN pg_am inxam ON inxcls.relam=inxam.oid
                LEFT OUTER JOIN pg_class tbl ON inx.indrelid=tbl.oid
     WHERE inxcls.relkind='i' AND nsp.nspname =LOWER(a_SchemaName)
               AND inxcls.relname=LOWER(a_IndexName);

SELECT (inx.No+1)::SMALLINT,attr.attnum::SMALLINT, attr.attname::NAME,
              CASE WHEN NOT inxam.amcanorder THEN NULL 
                                          ELSE 
              CASE WHEN inx.indoption[inx.No] & 1=1 THEN 'DESC'
                                           ELSE 'ASC' END END::VARCHAR(10),
              CASE WHEN COALESCE(typ.typbasetype,0)>0 THEN typ.typname::NAME ELSE '' END,
              FORMAT_TYPE(COALESCE(NULLIF(typ.typbasetype,0),typ.oid),
                               COALESCE(NULLIF(typ.typtypmod,-),attr.atttypmod))::NAME,
              attr.attnotnull,dsc.description 
    FROM (SELECT i.indrelid, i.indexrelid,i.indkey::SMALLINT[], i.indoption::SMALLINT[],
                            generate_subscripts(i.indkey, 1) as No FROM pg_index i) inx 
           INNER JOIN pg_class inxcls ON inx.indexrelid=inxcls.oid 
           INNER JOIN pg_namespace nsp ON inxcls.relnamespace=nsp.oid 
           LEFT OUTER JOIN pg_am inxam ON inxcls.relam=inxam.oid
           LEFT OUTER JOIN pg_class tbl ON inx.indrelid=tbl.oid
           INNER JOIN pg_attribute attr ON attr.attrelid=tbl.oid AND attr.attnum=inx.indkey[inx.No]
           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 nsp.nspname=LOWER(a_SchemaName) AND inxcls.relkind='i'
                                           AND inxcls.relname =LOWER(a_IndexName)
    ORDER BY nsp.nspname,inxcls.relname,inx.No;

{{{
#!plantuml
@startuml
object public.ID_DISTRICTS{
id_np : integer NOT NULL (PK1)(FK1 id_nps(id_np))
id_district : integer NOT NULL (PK2)
name_district : character varying(25) NULL 
type_district : character varying(25) NULL 
okato : character varying(11) NULL 
oktmo : character varying(11) NULL 
}
object public.ID_NPS{
id_region : integer NOT NULL (FK1 id_regions(id_region))
id_atu : integer NULL (FK1 id_rayons(id_atu))
id_selsov : integer NULL (FK1 id_selsovs(id_selsov))
id_np : integer NOT NULL (PK1)
name_np : character varying(25) NULL 
type_np : character varying(25) NULL (FK1 type_np(scname))
okato : character varying(11) NULL 
oktmo : character varying(11) NULL 
}
public.ID_DISTRICTS *-- public.ID_NPS
legend center
ТАБЛИЦЫ
 
ID_DISTRICTS- Справочник - список городских районов 
ID_NPS- Справочник - список населенных пунктов 
endlegend
@enduml
}}}
 
----

Создание функции admtf_PrimaryKey_Features

BEGIN TRANSACTION;
DROP FUNCTION IF EXISTS admtf_PrimaryKey_Features (a_SchemaName NAME,a_PrimaryKeyName NAME);
/******************************************************************************/
/*  Функция возвращает список характеристик первичного ключа таблицы, принадлежащего  */
/* схеме                                                                                                                                 */
/******************************************************************************/
CREATE OR REPLACE FUNCTION admtf_PrimaryKey_Features
   (a_SchemaName NAME default 'public',  /* название схемы базы данных */
    a_PrimaryKeyName NAME default NULL /* Название первичного ключа таблицы */
 )
RETURNS TABLE (rs_PrimaryKeyName NAME,rs_PrimaryKeyDescription TEXT) AS
$BODY$
DECLARE c_PrimaryKeyKind CONSTANT CHAR:='p';
        v_PrimaryKeyOID    OID;   /* ИД первичного ключа таблицы */
        v_PrimaryKeyName NAME; /* Название первичного ключа таблицы */
        v_PrimaryKeyDescription TEXT; /* Описание первичного ключа таблицы */
        v_MasterTableName NAME; /* Название таблицы, которой принадлежит первичный ключ */
--*******************************************************************           
BEGIN           
       SELECT INTO rs_PrimaryKeyName,rs_PrimaryKeyDescription
                   con.conname,COALESCE(dsc.description,'Первичный ключ таблицы '|| tbl.relname)  
           FROM pg_constraint con 
                      INNER JOIN pg_namespace nspc ON con.connamespace = nspc.oid 
                      INNER JOIN pg_class tbl ON con.conrelid=tbl.oid
                      LEFT OUTER JOIN pg_Description dsc ON con.oid=dsc.objoid
                                                                  AND dsc.objsubid=0
           WHERE nspc.nspname=LOWER(a_SchemaName) AND con.contype =c_PrimaryKeyKind
                      AND con.conname =LOWER(a_PrimaryKeyName);
       RETURN QUERY SELECT rs_PrimaryKeyName,rs_PrimaryKeyDescription;  
END
$BODY$
LANGUAGE plpgsql;
COMMENT ON FUNCTION admtf_PrimaryKey_Features(a_SchemaName NAME,a_PrimaryKeyName NAME) IS 'Возвращает список характеристик первичного ключа таблицы, принадлежащей схеме';
--ROLLBACK TRANSACTION;
COMMIT TRANSACTION;
BEGIN TRANSACTION;
DROP FUNCTION IF EXISTS admtf_PrimaryKey_Features (a_SchemaName VARCHAR(256),a_PrimaryKeyName VARCHAR(256));
/******************************************************************************/
/*  Функция возвращает список характеристик первичного ключа таблицы, принадлежащего */ /*  схеме                                                                                                                               */
/******************************************************************************/
CREATE OR REPLACE FUNCTION admtf_PrimaryKey_Features
     (a_SchemaName VARCHAR(256) default 'public', /* название схемы базы данных */
     a_PrimaryKeyName VARCHAR(256) default NULL   /* Название первичного ключа таблицы */
 )
RETURNS  TABLE (rs_PrimaryKeyName VARCHAR(256),rs_PrimaryKeyDescription TEXT) AS
$BODY$
DECLARE c_PrimaryKeyKind        CONSTANT CHAR:='p';
--******************************************************************            
BEGIN           
   RETURN QUERY SELECT pkf.rs_PrimaryKeyName::VARCHAR(256),
                                       pkf.rs_PrimaryKeyDescription::TEXT
        FROM admtf_PrimaryKey_Features(a_SchemaName::NAME,a_PrimaryKeyName::NAME) pkf;  
END
$BODY$
LANGUAGE plpgsql;
COMMENT ON FUNCTION admtf_PrimaryKey_Features(a_SchemaName VARCHAR(256),a_PrimaryKeyName VARCHAR(256)) IS 'Возвращает список характеристик первичного ключа таблицы, принадлежащей схеме';
--ROLLBACK TRANSACTION;
COMMIT TRANSACTION;
SELECt * FROM admtf_PrimaryKey_Features('public'::NAME,'xpkstreet'::NAME);
SELECt * FROM admtf_PrimaryKey_Features('public'::VARCHAR(256),'xpkstreet'::VARCHAR(256));

Создание функции admtf_PrimaryKey_Attributes

BEGIN TRANSACTION;
DROP FUNCTION IF EXISTS admtf_PrimaryKey_Attributes (a_SchemaName NAME,a_PrimaryKeyName NAME);
/********************************************************************/
/*  Функция возвращает список атрибутов первичного ключа и их характеристик */
/********************************************************************/
CREATE OR REPLACE FUNCTION admtf_PrimaryKey_Attributes
     (a_SchemaName      NAME default 'public',  /* название схемы базы данных           */
       a_PrimaryKeyName NAME default NULL       /* Название первичного ключа таблицы */
 )                                                                               
RETURNS  TABLE (r_PrimaryKeyNo SMALLINT,r_AttributeNumber SMALLINT,
     r_AttributeName NAME,r_UserTypeName NAME,r_TypeName NAME,
     r_isNotNULL BOOLEAN,r_Description Text) AS
$BODY$
DECLARE 
     c_PrimaryKeyKind   CONSTANT CHAR:='p';
     v_PrimaryKeyOID OID; /* ИД первичного ключа таблицы */
     v_PrimaryKeyName NAME;        /* Название первичного ключа таблицы */
     v_PrimaryKeyDescription TEXT; /* Описание первичного ключа таблицы */
     v_MasterTableName NAME; /* Название таблицы, которой принадлежит первичный ключ */
     v_PrimaryKeyArray SMALLINT[]; /* Массив порядновых номеров в таблице */
     v_MasterTableOID OID; /* ИД таблицы, которой принадлежит первичный ключ */
     v_AttributeNumber SMALLINT; /* Номер аттрибута в таблице */
     v_PKAttributeCount SMALLINT; /* Счетчик атрибутов первичного ключа*/
     v_AttNo SMALLINT; /* Порядковый номер атрибута первичного ключа*/
--**********************************************************************                
BEGIN           
        RETURN QUERY SELECT (rank() OVER (PARTITION BY con.conrelid ORDER BY
                                                      attr.attnum))::SMALLINT AS r_PrimaryKeyNo,
             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,
            TRIM(dsc.description) AS r_Description
        FROM (SELECT c.oid, c.conrelid,c.connamespace,c.confrelid,c.conname,
                          c.contype,c.conkey::SMALLINT[],
                          consrc, c.confkey::SMALLINT[],generate_subscripts(c.conkey, 1) as No 
                     FROM pg_constraint c) con 
                         INNER JOIN pg_namespace nspc ON con.connamespace = nspc.oid 
                         INNER JOIN pg_attribute attr ON attr.attrelid=con.conrelid
                                        AND attr.attnum=con.conkey[con.No]
                          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 con.contype=c_PrimaryKeyKind
                   AND LOWER(nspc.nspname)=LOWER(a_SchemaName) 
                  AND LOWER(con.conname)=LOWER(a_PrimaryKeyName)
          ORDER BY attr.attnum;
        RETURN;
END
$BODY$
LANGUAGE plpgsql;
COMMENT ON FUNCTION admtf_PrimaryKey_Attributes(a_SchemaName NAME,a_PrimaryKeyName NAME) IS 'Функция возвращает список атрибутов первичного ключа и их характеристик ';
--ROLLBACK TRANSACTION;
COMMIT TRANSACTION;
BEGIN TRANSACTION;
DROP FUNCTION IF EXISTS admtf_PrimaryKey_Attributes (a_SchemaName VARCHAR(256),a_PrimaryKeyName VARCHAR(256));
/********************************************************************/
/*  Функция возвращает список атрибутов первичного ключа и их характеристик */
/********************************************************************/
CREATE OR REPLACE FUNCTION admtf_PrimaryKey_Attributes
    (a_SchemaName VARCHAR(256) default 'public',  /* название схемы базы данных */
     a_PrimaryKeyName VARCHAR(256) default NULL /* Название первичного ключа таблицы */
 )
RETURNS  TABLE (r_PrimaryKeyNo SMALLINT,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_PrimaryKeyKind    CONSTANT CHAR:='p';
--*******************************************************************           
BEGIN           
    RETURN QUERY SELECT pka.r_PrimaryKeyNo::SMALLINT,pka.r_AttributeNumber::SMALLINT,
                             pka.r_AttributeName::VARCHAR(256),pka.r_UserTypeName::VARCHAR(256),
                             pka.r_TypeName::VARCHAR(256),pka.r_isNotNULL::BOOLEAN,
                             pka.r_Description::TEXT
         FROM admtf_PrimaryKey_Attributes(a_SchemaName::NAME,a_PrimaryKeyName::NAME) pka;
END
$BODY$
LANGUAGE plpgsql;
COMMENT ON FUNCTION admtf_PrimaryKey_Attributes(a_SchemaName VARCHAR(256),a_PrimaryKeyName VARCHAR(256)) IS 'Функция возвращает список атрибутов первичного ключа и их характеристик';
--ROLLBACK TRANSACTION;
COMMIT TRANSACTION;

SELECt * FROM admtf_PrimaryKey_Attributes('public'::NAME,'xpkstreet'::NAME);
SELECt * FROM admtf_PrimaryKey_Attributes('public'::VARCHAR(256),'xpkstreet'::VARCHAR(256));

BEGIN TRANSACTION;
DROP FUNCTION IF EXISTS admtf_PrimaryKey_ComplexFeatures (a_SchemaName NAME,a_PrimaryKeyName NAME);
/*****************************************************************************/
/*  Функция возвращает список характеристик первичного ключа таблицы, принадлежащей */
/*  схеме, а также список характеристик его атрибутов                                                      */
/*****************************************************************************/
CREATE OR REPLACE FUNCTION admtf_PrimaryKey_ComplexFeatures
    (a_SchemaName NAME default 'public', /* название схемы базы данных */
     a_PrimaryKeyName NAME default NULL /* Название первичного ключа таблицы */
 )                               
RETURNS  TABLE (rpk_FeatureCategory VARCHAR(10),rpk_FeatureNumber SMALLINT,rpk_FeatureName NAME,rpk_FeatureDescription TEXT,
rpk_UserTypeName NAME,rpk_TypeName NAME,rpk_isNotNULL BOOLEAN) AS
$BODY$
DECLARE
    c_PrimaryKeyCategory CONSTANT VARCHAR(10):='pk';    /* Категория характеристик  */
                                                               /* первичного ключа таблицы */
    c_AttributeCategory CONSTANT VARCHAR(10):='pkatt'; /* Категория характеристик атрибутов*/
                                                               /* первичного ключа таблицы */
    v_PrimaryKeyOID OID;                    /* ИД первичного ключа таблицы */
    v_PrimaryKeyName NAME;         /* Название первичного ключа таблицы */
    v_PrimaryKeyDescription TEXT;  /* Описание первичного ключа таблицы */
    v_FeatureCategory VARCHAR(10); /* Категория текущей характеристики */
    v_FeatureNumber SMALLINT; /* Порядковый номер характеристики заданной категории*/   
--***********************************************************************               
BEGIN   
    v_FeatureCategory:=c_PrimaryKeyCategory;
    v_FeatureNumber:=0;
    SELECT INTO v_PrimaryKeyName,v_PrimaryKeyDescription
                      rs_PrimaryKeyName,rs_PrimaryKeyDescription
        FROM admtf_PrimaryKey_Features(a_SchemaName,a_PrimaryKeyName);
    IF FOUND AND v_PrimaryKeyName IS NOT NULL THEN              
        RETURN QUERY SELECT v_FeatureCategory,v_FeatureNumber,v_PrimaryKeyName,
                       v_PrimaryKeyDescription,
                       NULL::NAME AS rpk_UserTypeName, NULL::NAME AS rpk_TypeName,
                       NULL::BOOLEAN AS rpk_isNotNULL;
        v_FeatureCategory:=c_AttributeCategory;
        v_FeatureNumber:=0;
        RETURN QUERY SELECT v_FeatureCategory,r_PrimaryKeyNo,r_AttributeName,r_Description,
                      r_UserTypeName,r_TypeName,r_isNotNULL
             FROM  admtf_PrimaryKey_Attributes(a_SchemaName,a_PrimaryKeyName);
     END IF;                    
     RETURN;
END
$BODY$
LANGUAGE plpgsql;
COMMENT ON FUNCTION admtf_PrimaryKey_ComplexFeatures(a_SchemaName NAME,a_PrimaryKeyName NAME) IS 'Возвращает список характеристик первичного ключа таблицы, принадлежащей схеме, а также список характеристик его атрибутов';
--ROLLBACK TRANSACTION;
COMMIT TRANSACTION;
BEGIN TRANSACTION;
DROP FUNCTION IF EXISTS admtf_PrimaryKey_ComplexFeatures (a_SchemaName VARCHAR(256),a_PrimaryKeyName VARCHAR(256));
/******************************************************************************/
/*  Функция возвращает список характеристик первичного ключа таблицы, принадлежащей  */
/* схеме, а также список характеристик его атрибутов                                                           */
/******************************************************************************/
CREATE OR REPLACE FUNCTION admtf_PrimaryKey_ComplexFeatures
    (a_SchemaName VARCHAR(256) default 'public', /* название схемы базы данных */
     a_PrimaryKeyName VARCHAR(256) default NULL /* Название первичного ключа таблицы */
 )
RETURNS  TABLE (rpk_FeatureCategory VARCHAR(10),rpk_FeatureNumber SMALLINT,rpk_FeatureName VARCHAR(256),rpk_FeatureDescription TEXT,
rpk_UserTypeName VARCHAR(256),rpk_TypeName VARCHAR(256),rpk_isNotNULL BOOLEAN) AS
$BODY$
DECLARE
    c_PrimaryKeyCategory CONSTANT VARCHAR(10):='pk'; /* Категория характеристик */
                                                                          /* первичного ключа таблицы */
--*************************************************************************             
BEGIN   
        RETURN QUERY SELECT pk.rpk_FeatureCategory::VARCHAR(10),
                    pk.rpk_FeatureNumber::SMALLINT,
                    pk.rpk_FeatureName::VARCHAR(256),pk.rpk_FeatureDescription::TEXT,
                    pk.rpk_UserTypeName::VARCHAR(256),pk.rpk_TypeName::VARCHAR(256),
                    pk.rpk_isNotNULL::BOOLEAN
           FROM  admtf_PrimaryKey_ComplexFeatures(a_SchemaName::NAME,
                                                         a_PrimaryKeyName::NAME) pk;
END
$BODY$
LANGUAGE plpgsql;
COMMENT ON FUNCTION admtf_PrimaryKey_ComplexFeatures(a_SchemaName VARCHAR(256),a_PrimaryKeyName VARCHAR(256)) IS 'Возвращает список характеристик первичного ключа таблицы, принадлежащей схеме, а также список характеристик его атрибутов';
--ROLLBACK TRANSACTION;
COMMIT TRANSACTION;

SELECt * FROM admtf_PrimaryKey_ComplexFeatures('public'::NAME,'xpkstreet'::NAME);
SELECt * FROM admtf_PrimaryKey_ComplexFeatures('public'::VARCHAR(256),'xpkstreet'::VARCHAR(256));

Создание функции admtf_ForeignKey_Features

BEGIN TRANSACTION;
DROP FUNCTION IF EXISTS admtf_ForeignKey_Features (a_SchemaName NAME,a_ForeignKeyName NAME);
/**************************************************************/
/*  Функция возвращает список характеристик внешнего ключа таблицы, */
/*  принадлежащего схеме                                                                       */
/**************************************************************/
CREATE OR REPLACE FUNCTION admtf_ForeignKey_Features
    (a_SchemaName NAME default 'public', /* название схемы базы данных */
     a_ForeignKeyName NAME default NULL /* Название внешнего ключа таблицы */
 )                                                                               
RETURNS  TABLE (rs_ForeignKeyName NAME,rs_ForeignKeyDescription TEXT) AS
$BODY$
DECLARE c_ForeignKeyKind        CONSTANT CHAR:='f';
    v_ForeignKeyOID OID;            /* ИД внешнего ключа таблицы */
    v_ForeignKeyName  NAME;      /* Название внешнего ключа таблицы */
    v_ForeignKeyDescription TEXT; /* Описание внешнего ключа таблицы */
    v_MasterTableName NAME; /* Название таблицы, которой принадлежит первичный ключ */
--************************************************************************              
BEGIN           
    SELECT INTO rs_ForeignKeyName,rs_ForeignKeyDescription 
                   con.conname,COALESCE(dsc.description,'Внешний ключ таблицы '|| tbl.relname)  
      FROM pg_constraint con 
         INNER JOIN pg_namespace nspc ON con.connamespace = nspc.oid 
         INNER JOIN pg_class tbl ON con.conrelid=tbl.oid
         LEFT OUTER JOIN pg_Description dsc ON con.oid=dsc.objoid
                              AND dsc.objsubid=0
      WHERE nspc.nspname=LOWER(a_SchemaName) AND con.contype =c_ForeignKeyKind
                           AND con.conname =LOWER(a_ForeignKeyName);
   RETURN QUERY SELECT rs_ForeignKeyName,rs_ForeignKeyDescription;      
END
$BODY$
LANGUAGE plpgsql;
COMMENT ON FUNCTION admtf_ForeignKey_Features(a_SchemaName NAME,a_ForeignKeyName NAME) IS 'Возвращает список характеристик внешнего ключа таблицы, принадлежащей схеме';
--ROLLBACK TRANSACTION;
COMMIT TRANSACTION;
BEGIN TRANSACTION;
DROP FUNCTION IF EXISTS admtf_ForeignKey_Features (a_SchemaName VARCHAR(256),a_ForeignKeyName VARCHAR(256));
/********************************************************************************************************/
/*  Функция возвращает список характеристик внешнего ключа таблицы, принадлежащего схеме                                */
/********************************************************************************************************/
CREATE OR REPLACE FUNCTION admtf_ForeignKey_Features
   (a_SchemaName VARCHAR(256) default 'public', /* название схемы базы данных */
   a_ForeignKeyName VARCHAR(256) default NULL /* Название внешнего ключа таблицы */
 )                                                                               
RETURNS  TABLE (rs_ForeignKeyName VARCHAR(256),rs_ForeignKeyDescription TEXT) AS
$BODY$
DECLARE c_ForeignKeyKind CONSTANT CHAR:='f';
        --*******************************************************************           
BEGIN           
   RETURN QUERY SELECT fkf.rs_ForeignKeyName::VARCHAR(256),
                                 fkf.rs_ForeignKeyDescription::TEXT
                              FROM admtf_ForeignKey_Features
                                             (a_SchemaName::NAME,a_ForeignKeyName::NAME) fkf;
END
$BODY$
LANGUAGE plpgsql;
COMMENT ON FUNCTION admtf_ForeignKey_Features(a_SchemaName VARCHAR(256),a_ForeignKeyName VARCHAR(256)) IS 'Возвращает список характеристик внешнего ключа таблицы, принадлежащей схеме';
--ROLLBACK TRANSACTION;
COMMIT TRANSACTION;
SELECt * FROM admtf_ForeignKey_Features('public'::VARCHAR(256),'fk_street_locality'::VARCHAR(256));
SELECt * FROM admtf_ForeignKey_Features('public'::NAME,'fk_street_locality'::NAME);

• О проекте
• Архив
• Реклама Партнёры Правовая информация Контакты Обратная связь
•