Функции для документирования баз данных PostgreSQL. Часть вторая
BEGIN TRANSACTION;
DROP FUNCTION IF EXISTS admtf_Table_Constraintes (a_SchemaName NAME, a_TableName NAME);
/********************************************************************************************************/
/* Функция возвращает список ограничений таблицы */
/********************************************************************************************************/
CREATE OR REPLACE FUNCTION admtf_Table_Constraintes
(a_SchemaName name default 'public', /* название схемы базы данных */
a_TableName name default NULL /* Название таблицы */
)
RETURNS TABLE (r_ConstraintName NAME,r_ConstraintType NAME,r_ConstraintKeyNames Text,r_RefTableName NAME,r_RefTableKeyNames Text,r_ConstraintSource Text) AS
$BODY$
DECLARE v_Scale INTEGER; /* Масштаб колонки */
v_ConstraintRec RECORD; /* Данные об ограничении*/
v_TableOID INTEGER; /* OID таблицы*/
v_ConstraintOID INTEGER; /* OID ограничения*/
v_ConstraintKeyNos SMALLINT[]; /* */
v_ConstraintName name; /* Название ограничения */
v_ConstraintType name; /* Буквенное обозначение типа ограничения */
v_isUnique BOOLEAN; /* Признак уникальности ограничения*/
v_isPrimary BOOLEAN; /* Признак того что индекс представляет Primary KEY таблицы*/
v_AttributeNum INTEGER; /* Порядковый номер аттрибута*/
v_AttributeName name; /* Наименование аттрибута*/
v_ConstraintKeyNames TEXT; /* Строка со списком аттрибутов ограничения*/
v_RefTableOID INTEGER; /* OID таблицы, на которую ссылается ограничение */
v_RefTableName name;/* Название таблицы, на которую ссылается ограничение */
v_RefTableKeyNos SMALLINT[]; /* */
v_RefTableKeyNames TEXT; /* Строка со списком аттрибутов таблицы, на которую ссылается ограничение*/
v_ConstraintSource TEXT; /* Строка с описанием уловия CHECK*/
c_Delimiter CONSTANT VARCHAR(2):=',';
--******************************************************************************************************
BEGIN
FOR v_ConstraintRec IN SELECT con.oid AS ConstraintOID ,tbl.OID AS TableOID,
con.conname AS ConstraintName,
con.contype AS ConstraintType,
con.conkey AS ConstraintKeyNos,
reftbl.OID AS RefTableOID,
reftbl.relname AS RefTableName,
con.confkey AS RefTableKeyNos,
con.consrc AS ConstraintSource
FROM pg_constraint con
INNER JOIN pg_class tbl ON con.conrelid=tbl.oid
INNER JOIN pg_namespace nsp ON tbl.relnamespace=nsp.oid
LEFT OUTER JOIN pg_class reftbl ON con.confrelid=reftbl.oid
WHERE LOWER(nsp.nspname)=LOWER(a_SchemaName)
AND LOWER(tbl.relname)=LOWER(a_TableName)
ORDER BY con.contype DESC,con.conname
LOOP
v_ConstraintOID:=v_ConstraintRec.ConstraintOID;
v_TableOID:=v_ConstraintRec.TableOID;
v_ConstraintName:=v_ConstraintRec.ConstraintName;
v_ConstraintType:=v_ConstraintRec.ConstraintType;
v_ConstraintKeyNos:=v_ConstraintRec.ConstraintKeyNos;
v_RefTableOID:=v_ConstraintRec.RefTableOID;
v_RefTableName:=v_ConstraintRec.RefTableName;
v_RefTableKeyNos:=v_ConstraintRec.RefTableKeyNos;
v_ConstraintSource:=v_ConstraintRec.ConstraintSource;
v_ConstraintKeyNames:='';
FOREACH v_AttributeNum IN ARRAY v_ConstraintKeyNos
LOOP
SELECT INTO v_AttributeName attr.attname::VARCHAR(100) AS r_AttributeName
FROM pg_attribute attr
WHERE attr.attrelid=v_TableOID
AND attr.attnum=v_AttributeNum;
v_ConstraintKeyNames:=v_ConstraintKeyNames||
CASE WHEN v_ConstraintKeyNames='' THEN '' ELSE c_Delimiter END
||v_AttributeName;
END LOOP;
v_RefTableKeyNames:='';
IF v_RefTableKeyNos IS NOT NULL THEN
FOREACH v_AttributeNum IN ARRAY v_RefTableKeyNos
LOOP
SELECT INTO v_AttributeName
attr.attname::VARCHAR(100) AS r_AttributeName
FROM pg_attribute attr
WHERE attr.attrelid=v_RefTableOID AND attr.attnum=v_AttributeNum;
v_RefTableKeyNames:=v_RefTableKeyNames||
CASE WHEN v_RefTableKeyNames='' THEN '' ELSE c_Delimiter END
||v_AttributeName;
END LOOP;
END IF;
RETURN QUERY SELECT v_ConstraintName,v_ConstraintType,v_ConstraintKeyNames,
v_RefTableName,v_RefTableKeyNames,
v_ConstraintSource;
END LOOP;
RETURN;
END
$BODY$
LANGUAGE plpgsql;
COMMENT ON FUNCTION admtf_Table_Constraintes(a_SchemaName NAME, a_TableName NAME) IS 'Возвращает возвращает ограничений таблицы ';
--ROLLBACK TRANSACTION;
COMMIT TRANSACTION;
BEGIN TRANSACTION;
DROP FUNCTION IF EXISTS admtf_Table_Constraintes (a_SchemaName VARCHAR(256), a_TableName VARCHAR(256));
/********************************************************************************************************/
/* Функция возвращает список ограничений таблицы */
/********************************************************************************************************/
CREATE OR REPLACE FUNCTION admtf_Table_Constraintes
(a_SchemaName VARCHAR(256) default 'public', /* название схемы базы данных */
a_TableName VARCHAR(256) default NULL /* Название таблицы */
)
RETURNS TABLE (r_ConstraintName VARCHAR(256),r_ConstraintType VARCHAR(256),r_ConstraintKeyNames Text,r_RefTableName VARCHAR(256),r_RefTableKeyNames Text,r_ConstraintSource Text) AS
$BODY$
DECLARE
--******************************************************************************************************
BEGIN
RETURN QUERY SELECT tc.r_ConstraintName::VARCHAR(256),
tc.r_ConstraintType::VARCHAR(256),tc.r_ConstraintKeyNames::TEXT,
tc.r_RefTableName::VARCHAR(256),tc.r_RefTableKeyNames::TEXT,
tc.r_ConstraintSource::TEXT
FROM admtf_Table_Constraintes(a_SchemaName::NAME,a_TableName::NAME) tc;
END
$BODY$
LANGUAGE plpgsql;
COMMENT ON FUNCTION admtf_Table_Constraintes(a_SchemaName VARCHAR(256), a_TableName VARCHAR(256)) IS 'Возвращает возвращает ограничений таблицы ';
--ROLLBACK TRANSACTION;
COMMIT TRANSACTION;
SELECT * FROM admtf_Table_Constraintes('public'::VARCHAR(256),'Street'::VARCHAR(256));
SELECT * FROM admtf_Table_Constraintes('public'::name,'Street'::name);
Создание версии функции admtf_Table_Constraintes без курсора
Комментарии к исходному коду функции можно посмотреть здесь.
BEGIN TRANSACTION;
DROP FUNCTION IF EXISTS admtf_Table_Constraintes (a_SchemaName NAME, a_TableName NAME);
/********************************************************************************************************/
/* Функция возвращает список ограничений таблицы */
/********************************************************************************************************/
CREATE OR REPLACE FUNCTION admtf_Table_Constraintes
(a_SchemaName name default 'public', /* название схемы базы данных */
a_TableName name default NULL /* Название таблицы */
)
RETURNS TABLE (r_ConstraintName NAME,r_ConstraintType NAME,r_ConstraintKeyNames Text,r_RefTableName NAME,r_RefTableKeyNames Text,r_ConstraintSource Text) AS
$BODY$
DECLARE v_Scale INTEGER; /* Масштаб колонки */
v_ConstraintRec RECORD; /* Данные об ограничении*/
v_TableOID INTEGER; /* OID таблицы*/
v_ConstraintOID INTEGER; /* OID ограничения*/
v_ConstraintKeyNos SMALLINT[]; /* */
v_ConstraintName name; /* Название ограничения */
v_ConstraintType name; /* Буквенное обозначение типа ограничения */
v_isUnique BOOLEAN; /* Признак уникальности ограничения*/
v_isPrimary BOOLEAN;/* Признак того что индекс представляет Primary KEY таблицы*/
v_AttributeNum INTEGER; /* Порядковый номер аттрибута*/
v_AttributeName name; /* Наименование аттрибута*/
v_ConstraintKeyNames TEXT; /* Строка со списком аттрибутов ограничения*/
v_RefTableOID INTEGER; /* OID таблицы, на которую ссылается ограничение */
v_RefTableName name;/* Название таблицы, на которую ссылается ограничение */
v_RefTableKeyNos SMALLINT[]; /* */
v_RefTableKeyNames TEXT;/* Строка со списком аттрибутов таблицы, на которую ссылается ограничение*/
v_ConstraintSource TEXT; /* Строка с описанием уловия CHECK*/
c_Delimiter CONSTANT VARCHAR(2):=',';
--******************************************************************************************************
BEGIN
FOR v_ConstraintRec IN SELECT con.oid AS ConstraintOID ,
tbl.OID AS TableOID,
con.conname AS ConstraintName,
con.contype AS ConstraintType,
con.conkey AS ConstraintKeyNos,
reftbl.OID AS RefTableOID,
reftbl.relname AS RefTableName,
con.confkey AS RefTableKeyNos,
con.consrc AS ConstraintSource
FROM pg_constraint con
INNER JOIN pg_class tbl ON con.conrelid=tbl.oid
INNER JOIN pg_namespace nsp ON tbl.relnamespace=nsp.oid
LEFT OUTER JOIN pg_class reftbl ON con.confrelid=reftbl.oid
WHERE LOWER(nsp.nspname)=LOWER(a_SchemaName)
AND LOWER(tbl.relname)=LOWER(a_TableName)
ORDER BY con.contype DESC,con.conname
LOOP
v_ConstraintOID:=v_ConstraintRec.ConstraintOID;
v_TableOID:=v_ConstraintRec.TableOID;
v_ConstraintName:=v_ConstraintRec.ConstraintName;
v_ConstraintType:=v_ConstraintRec.ConstraintType;
v_ConstraintKeyNos:=v_ConstraintRec.ConstraintKeyNos;
v_RefTableOID:=v_ConstraintRec.RefTableOID;
v_RefTableName:=v_ConstraintRec.RefTableName;
v_RefTableKeyNos:=v_ConstraintRec.RefTableKeyNos;
v_ConstraintSource:=v_ConstraintRec.ConstraintSource;
v_ConstraintKeyNames:='';
FOREACH v_AttributeNum IN ARRAY v_ConstraintKeyNos
LOOP
SELECT INTO v_AttributeName
attr.attname::VARCHAR(100) AS r_AttributeName
FROM pg_attribute attr
WHERE attr.attrelid=v_TableOID
AND attr.attnum=v_AttributeNum;
v_ConstraintKeyNames:=v_ConstraintKeyNames||
CASE WHEN v_ConstraintKeyNames='' THEN '' ELSE c_Delimiter END
||v_AttributeName;
END LOOP;
v_RefTableKeyNames:='';
IF v_RefTableKeyNos IS NOT NULL THEN
FOREACH v_AttributeNum IN ARRAY v_RefTableKeyNos
LOOP
SELECT INTO v_AttributeName
attr.attname::VARCHAR(100) AS r_AttributeName
FROM pg_attribute attr
WHERE attr.attrelid=v_RefTableOID
AND attr.attnum=v_AttributeNum;
v_RefTableKeyNames:=v_RefTableKeyNames||
CASE WHEN v_RefTableKeyNames='' THEN '' ELSE c_Delimiter END
||v_AttributeName;
END LOOP;
END IF;
RETURN QUERY SELECT v_ConstraintName,v_ConstraintType,v_ConstraintKeyNames,
v_RefTableName,v_RefTableKeyNames,
v_ConstraintSource;
END LOOP;
RETURN;
END
$BODY$
LANGUAGE plpgsql;
COMMENT ON FUNCTION admtf_Table_Constraintes(a_SchemaName NAME, a_TableName NAME) IS 'Возвращает возвращает ограничений таблицы ';
--ROLLBACK TRANSACTION;
COMMIT TRANSACTION;
BEGIN TRANSACTION;
DROP FUNCTION IF EXISTS admtf_Table_Constraintes (a_SchemaName VARCHAR(256), a_TableName VARCHAR(256));
/********************************************************************************************************/
/* Функция возвращает список ограничений таблицы */
/********************************************************************************************************/
CREATE OR REPLACE FUNCTION admtf_Table_Constraintes
(a_SchemaName VARCHAR(256) default 'public', /* название схемы базы данных */
a_TableName VARCHAR(256) default NULL /* Название таблицы */
)
RETURNS TABLE (r_ConstraintName VARCHAR(256),r_ConstraintType VARCHAR(256),r_ConstraintKeyNames Text,r_RefTableName VARCHAR(256),r_RefTableKeyNames Text,r_ConstraintSource Text) AS
$BODY$
DECLARE
--******************************************************************************************************
BEGIN
RETURN QUERY SELECT tc.r_ConstraintName::VARCHAR(256),
tc.r_ConstraintType::VARCHAR(256),
tc.r_ConstraintKeyNames::TEXT,
tc.r_RefTableName::VARCHAR(256),
tc.r_RefTableKeyNames::TEXT,
tc.r_ConstraintSource::TEXT
FROM admtf_Table_Constraintes(a_SchemaName::NAME,a_TableName::NAME) tc;
END
$BODY$
LANGUAGE plpgsql;
COMMENT ON FUNCTION admtf_Table_Constraintes(a_SchemaName VARCHAR(256), a_TableName VARCHAR(256)) IS 'Возвращает возвращает ограничений таблицы ';
--ROLLBACK TRANSACTION;
COMMIT TRANSACTION;
SELECT * FROM admtf_Table_Constraintes('public'::VARCHAR(256),'Street'::VARCHAR(256));
SELECT * FROM admtf_Table_Constraintes('public'::name,'Street'::name);
Создание функции admtf_Table_Indexes
Комментарии к исходному коду функции можно посмотреть здесь.
BEGIN TRANSACTION;
DROP FUNCTION IF EXISTS admtf_Table_Indexes (a_SchemaName NAME, a_TableName NAME);
/********************************************************************************************************/
/* Функция возвращает список индексов таблицы */
/********************************************************************************************************/
CREATE OR REPLACE FUNCTION admtf_Table_Indexes
(a_SchemaName NAME default 'public', /* название схемы базы данных */
a_TableName NAME default NULL /* Название таблицы */
)
RETURNS TABLE (r_IndexName NAME,r_IndexType NAME,r_isUnique BOOLEAN,r_isPrimary BOOLEAN, r_IndexKeyNames Text) AS
$BODY$
DECLARE
c_IndexKind CONSTANT CHAR:='i';
v_IndexRec RECORD; /* Данные об индексе*/
v_Scale INTEGER; /* Масштаб колонки */
v_TableOID INTEGER; /* OID таблицы*/
v_IndexOID INTEGER; /* OID индекса*/
v_IndexKeyNos SMALLINT[]; /* */
v_IndexName NAME; /* Название индекса */
v_IndexAMName NAME; /* Наименование типа индекса (метода доступа) */
v_isUnique BOOLEAN; /* Признак уникальности индекса*/
v_isPrimary BOOLEAN; /* Признак того что индекс представляет Primary KEY таблицы*/
v_AttributeNum INTEGER; /* Порядковый номер атрибута*/
v_AttributeName NAME; /* Наименование атрибута*/
v_IndexKeyNames TEXT; /* Строка со списком атрибутов индекса*/
c_Delimiter CONSTANT VARCHAR(2):=',';
--******************************************************************************************************
BEGIN
FOR v_IndexRec IN SELECT inxcls.oid AS IndexOID,tbl.oid AS TableOID,
inxcls.relname AS IndexName,inxam.amname AS IndexAMName,
inx.indisunique AS isUnique,inx.indisprimary isPrimary,
inx.indkey::SMALLINT[] AS IndexKeyNos
FROM pg_index inx
INNER JOIN pg_class inxcls ON inx.indexrelid=inxcls.oid
INNER JOIN pg_am inxam ON inxcls.relam=inxam.oid
INNER JOIN pg_class tbl ON inx.indrelid=tbl.oid
INNER JOIN pg_namespace nsp ON tbl.relnamespace=nsp.oid
WHERE LOWER(nsp.nspname)=LOWER(a_SchemaName)
AND inxcls.relkind=c_IndexKind
AND tbl.relname=LOWER(a_TableName)
ORDER BY inxam.amname, inxcls.relname LOOP
v_IndexOID:=v_IndexRec.IndexOID;
v_TableOID:=v_IndexRec.TableOID;
v_IndexName:=v_IndexRec.IndexName;
v_IndexAMName:=v_IndexRec.IndexAMName;
v_isUnique:=v_IndexRec.isUnique;
v_isPrimary:=v_IndexRec.isPrimary;
v_IndexKeyNos:=v_IndexRec.IndexKeyNos;
v_IndexKeyNames:='';
FOREACH v_AttributeNum IN ARRAY v_IndexKeyNos
LOOP
SELECT INTO v_AttributeName attr.attname::VARCHAR(100) AS r_AttributeName
FROM pg_attribute attr
WHERE attr.attrelid=v_TableOID AND attr.attnum=v_AttributeNum;
v_IndexKeyNames:=v_IndexKeyNames||
CASE WHEN v_IndexKeyNames='' THEN ''
ELSE c_Delimiter||' ' END ||
v_AttributeName;
END LOOP;
RETURN QUERY SELECT v_IndexName,v_IndexAMName,v_isUnique,
v_isPrimary,v_IndexKeyNames;
END LOOP;
RETURN;
END
$BODY$
LANGUAGE plpgsql;
COMMENT ON FUNCTION admtf_Table_Indexes(a_SchemaName NAME, a_TableName NAME) IS 'Возвращает список индексов таблицы ';
--ROLLBACK TRANSACTION;
COMMIT TRANSACTION;
BEGIN TRANSACTION;
DROP FUNCTION IF EXISTS admtf_Table_Indexes (a_SchemaName VARCHAR(256), a_TableName VARCHAR(256));
/********************************************************************************************************/
/* Функция возвращает список индексов таблицы */
/********************************************************************************************************/
CREATE OR REPLACE FUNCTION admtf_Table_Indexes
(a_SchemaName VARCHAR(256) default 'public', /* название схемы базы данных */
a_TableName VARCHAR(256) default NULL /* Название таблицы */
)
RETURNS TABLE (r_IndexName VARCHAR(256),r_IndexType VARCHAR(256),r_isUnique BOOLEAN,r_isPrimary BOOLEAN, r_IndexKeyNames TEXT) AS
$BODY$
DECLARE
--******************************************************************************************************
BEGIN
RETURN QUERY SELECT ti.r_IndexName::VARCHAR(256),
ti.r_IndexType::VARCHAR(256),
ti.r_isUnique::BOOLEAN,
ti.r_isPrimary::BOOLEAN,
ti.r_IndexKeyNames::TEXT
FROM admtf_Table_Indexes(a_SchemaName::NAME,a_TableName::NAME) ti;
END
$BODY$
LANGUAGE plpgsql;
COMMENT ON FUNCTION admtf_Table_Indexes(a_SchemaName VARCHAR(256), a_TableName VARCHAR(256)) IS 'Возвращает список индексов таблицы ';
--ROLLBACK TRANSACTION;
COMMIT TRANSACTION;
SELECT * FROM admtf_Table_Indexes('public'::NAME,'Street'::NAME);
SELECT * FROM admtf_Table_Indexes('public'::VARCHAR(256),'Street'::VARCHAR(256));
Создание версии функции admtf_Table_Indexes без курсора
Комментарии к исходному коду функции можно посмотреть здесь.
BEGIN TRANSACTION;
DROP FUNCTION IF EXISTS admtf_Table_Indexes (a_SchemaName NAME, a_TableName NAME);
/********************************************************************************************************/
/* Функция возвращает список индексов таблицы */
/********************************************************************************************************/
CREATE OR REPLACE FUNCTION admtf_Table_Indexes
(a_SchemaName NAME default 'public', /* название схемы базы данных */
a_TableName NAME default NULL /* Название таблицы */
)
RETURNS TABLE (r_IndexName NAME,r_IndexType NAME,r_isUnique BOOLEAN,r_isPrimary BOOLEAN, r_IndexKeyNames Text) AS
$BODY$
DECLARE
c_IndexKind CONSTANT CHAR:='i';
c_Delimiter CONSTANT VARCHAR(2):=', ';
--******************************************************************************************************
BEGIN
RETURN QUERY SELECT inxcls.relname AS r_IndexName,
inxam.amname AS r_IndexType,
inx.indisunique AS r_isUnique,
inx.indisprimary r_isPrimary,
STRING_AGG(attr.attname||CASE inxam.amcanorder WHEN true
THEN CASE inx.indoption[inx.No] & 1
WHEN 1 THEN ' DESC'
ELSE ' ASC'
END
ELSE ''
END,
c_Delimiter ORDER BY inx.No)
FROM (SELECT i.indrelid, i.indexrelid,i.indisunique,
i.indisprimary,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_am inxam ON inxcls.relam=inxam.oid
INNER JOIN pg_class tbl ON inx.indrelid=tbl.oid
INNER JOIN pg_namespace nsp ON tbl.relnamespace=nsp.oid
INNER JOIN pg_attribute attr ON attr.attrelid=tbl.OID
AND attr.attnum=inx.indkey[inx.No]
WHERE LOWER(nsp.nspname)=LOWER(a_SchemaName)
AND inxcls.relkind=c_IndexKind
AND tbl.relname=LOWER(a_TableName)
GROUP BY inxcls.relname,inxam.amname,inx.indisunique,inx.indisprimary
ORDER BY inxcls.relname;
RETURN;
END
$BODY$
LANGUAGE plpgsql;
COMMENT ON FUNCTION admtf_Table_Indexes(a_SchemaName NAME, a_TableName NAME) IS 'Возвращает список индексов таблицы ';
--ROLLBACK TRANSACTION;
COMMIT TRANSACTION;
BEGIN TRANSACTION;
DROP FUNCTION IF EXISTS admtf_Table_Indexes (a_SchemaName VARCHAR(256), a_TableName VARCHAR(256));
/********************************************************************************************************/
/* Функция возвращает список индексов таблицы */
/********************************************************************************************************/
CREATE OR REPLACE FUNCTION admtf_Table_Indexes
(a_SchemaName VARCHAR(256) default 'public', /* название схемы базы данных*/
a_TableName VARCHAR(256) default NULL /* Название таблицы */
)
RETURNS TABLE (r_IndexName VARCHAR(256),r_IndexType VARCHAR(256),r_isUnique BOOLEAN,r_isPrimary BOOLEAN, r_IndexKeyNames TEXT) AS
$BODY$
DECLARE
--******************************************************************************************************
BEGIN
RETURN QUERY SELECT ti.r_IndexName::VARCHAR(256),
ti.r_IndexType::VARCHAR(256),
ti.r_isUnique::BOOLEAN,
ti.r_isPrimary::BOOLEAN,
ti.r_IndexKeyNames::TEXT
FROM admtf_Table_Indexes(a_SchemaName::NAME,a_TableName::NAME) ti;
END
$BODY$
LANGUAGE plpgsql;
COMMENT ON FUNCTION admtf_Table_Indexes(a_SchemaName VARCHAR(256), a_TableName VARCHAR(256)) IS 'Возвращает список индексов таблицы ';
--ROLLBACK TRANSACTION;
COMMIT TRANSACTION;
SELECT * FROM admtf_Table_Indexes('public'::NAME,'Street'::NAME);
SELECT * FROM admtf_Table_Indexes('public'::VARCHAR(256),'Street'::VARCHAR(256));
