Durante o trabalho com o PostgreSQL, mais de cem funções para trabalhar com diretórios do sistema foram acumuladas: pg_class, pg_attribute, pg_constraints, etc.
O que fazer com eles? Eles são usados relativamente raramente. Incluir em algum projeto? O cliente Krasnoyarsk não pagará por esse "absurdo". E, no entanto, e se eles forem úteis para outra pessoa, exceto o autor. E ele decidiu colocá-los, como livros lidos, em um armário público para aqueles que desejavam.
Alguém quer usá-los em seu trabalho. E alguém interessado em outra experiência que não seja sua com catálogos de sistemas.
Mas, para não transformar a publicação em uma enumeração chata, não se sabe por que as funções foram criadas, decidi me debruçar sobre aquelas que podem ser unidas por um objetivo comum. Portanto, as funções usadas para exibir uma lista estendida de características de uma tabela arbitrária de banco de dados são selecionadas.
A lista estendida de características da tabela de banco de dados é retornada pela função
admtf_Table_ComplexFeatures , que será chamada de função head neste artigo. Assim, o artigo se limitará a considerar as funções chamadas no processo de execução da função principal.
A primeira metade do artigo fornece comentários sobre a implementação de funções. O segundo é o código fonte das funções. Para os leitores que estão interessados apenas nos textos originais, sugerimos seguir imediatamente para o
Apêndice .
Veja também
Funções para documentar bancos de dados PostgreSQL. Parte dois ;
Funções para documentar bancos de dados PostgreSQL. Parte três .
Funções para documentar bancos de dados PostgreSQL. O fim (parte quatro) .
De quais recursos avançados estamos falando?
Para ter uma idéia do significado das características estendidas da tabela do banco de dados, começamos considerando a seguinte lista de características. A lista contém as características da tabela de banco de dados Street retornada por
admtf_Table_ComplexFeatures ( 'public' , 'street' ) .
A tabela abaixo contém uma lista abreviada dos recursos da tabela Street. Um conjunto completo de características desta tabela é fornecido em Materiais Suplementares do Apêndice 2.
Tabela 1. Recursos avançados da tabela Street.
A versão em texto da tabela na figura| Categoria | Não. | Título | Comentário | tipo | Tipo de base | ? não NULL |
|---|
| tbl | 0 0 | rua | Lista de ruas em assentamentos | | | |
| att | 1 | wcrccode | Código do país | wcrccode | smallint | t |
| att | 2 | localityid | ID da comunidade | localityid | inteiro | t |
| att | 3 | streetid | Rua identificação da vila | streetid | smallint | t |
| att | 4 | streettypeacrm | Sigla da rua | streettypeacrm | personagem (8) | f |
| att | 5 | nome da rua | Nome da rua | streettypeacrm | varchar (150) | t |
| pk | 0 0 | xpkstreet | Chave primária da tabela de ruas | | | |
| pkatt | 1 | wcrccode | Código do país | wcrccode | smallint | t |
| fk01 | 1 | fk_street_locality | Chave estrangeira da tabela | | | |
| fk02 | 2 | fk_street_streettype | Chave estrangeira da tabela | | | |
| idx01 | 1 | xie1street | Índice por tipo e nome da rua do assentamento | | | |
| idx02 | 2 | xie2street | Código postal | | | |
| idx03 | 3 | xie3street | Índice de nomes de ruas de todos os assentamentos | | | |
| idx04 | 4 | xpkstreet | O índice exclusivo (chave primária) da tabela de ruas | | | |
Enumerando a hostilidade a esse conjunto de letras e números, você pode ver que estamos falando das características usuais de uma tabela de banco de dados:
- Nomes de tabelas;
- A lista de atributos da tabela e seus tipos;
- A chave primária e a lista de chaves estrangeiras da tabela, juntamente com os atributos da tabela que as compõem;
- Uma lista de índices de tabela.
A exclusividade de cada entrada da lista de características é garantida pelos valores dos campos "categoria" e pelo número de série ("Não") da característica.
Tabela 2. Categorias de características da tabela.
A versão em texto da tabela na figura| Acrônimo | Nomeação |
|---|
| tb | Características da tabela |
| att | Características do atributo da tabela |
| seq | Características da sequência |
| pk | Principais Recursos Principais |
| pkAtt | Características do atributo da chave primária |
| fk99 | Principais recursos estrangeiros |
| fk99att | Características de Atributos de Chave Estrangeira |
| fk99rtbl | Características da tabela referenciada pela chave estrangeira |
| fk99ratt | Características de atributo da tabela referenciada pela chave estrangeira |
| Idx99 | Características do Índice |
| Idx99att | Características de Atributo de Índice |
| Inhtbl99 | Características da tabela gerada |
O valor da categoria é necessário para distinguir diferentes grupos de características. Um número de série, para distinguir as características dentro do grupo.
Várias chaves estrangeiras (FOREIGN KEY) e índices podem ser declarados na tabela do banco de dados. Portanto, o valor da categoria para essas características e seus descendentes contém um número de série. Por exemplo, uma entrada com a chave "Categoria" = idx02att e "Não" = 1 indica o primeiro atributo do 2º índice.
Na lista de categorias acima, o local do número de série é indicado como '99'.
Observação 1
o código fonte do operador na figuraSELECT * FROM admtf_Table_ComplexFeatures('pg_catalog','pg_class');
O artigo fornece exemplos das características das tabelas, que são brevemente descritas em um esquema auxiliar criado especificamente para demonstrar os recursos das funções. Mas o leitor, tendo criado essa ou aquela função em seu banco de dados, pode usar os nomes de seus esquemas e tabelas como parâmetros. Além disso, por exemplo, o diretório pg_class pode ser usado como parâmetro, embora neste caso um número limitado de características seja emitido.
O fim da observação.
Estrutura da função principal
Fig. 1. As funções que causam a função da cabeça.Tabela 3. Designação de Funções.
A versão em texto da tabela na figura| Não. | Título | Nomeação |
|---|
| 1 | admtf_Table_Features | A função retorna uma lista de características da tabela de banco de dados |
| 2 | admtf_Table_Attributes | A função retorna uma lista de atributos da tabela de banco de dados e suas características. |
| 3 | admtf_Table_Constraintes | A função retorna uma lista de restrições da tabela do banco de dados e suas características. |
| 4 | admtf_Table_Indexes | A função retorna uma lista de índices da tabela de banco de dados e suas características. |
| 5 | admtf_Table_InheritanceChildrens | A função retorna uma lista de tabelas geradas por (IHERITS) da tabela de banco de dados de origem. |
| 6 | admtf_Table_Sequences | A função retorna uma lista de sequências (SEQUENCE) da qual a tabela depende. |
| 7 | admtf_PrimaryKey_ComplexFeatures | A função retorna uma lista completa (estendida) de características da chave primária (PRIMARY KEY) da tabela do banco de dados. |
| 8 | admtf_ForeignKey_ComplexFeatures | A função retorna uma lista completa (estendida) de características de chave estrangeira (FOREIGN KEY) da tabela de banco de dados. |
| 9 | admtf_Index_ComplexFeatures | A função retorna uma lista completa (estendida) de características do índice da tabela do banco de dados. |
| 10 | admtf_Table_ComplexFeatures | A função retorna uma lista completa (estendida) de características da tabela de banco de dados. |
Observação 2.
As descrições das funções serão organizadas na ordem mostrada acima. O motivo é que o artigo terá que ser dividido em várias partes. E as funções organizadas nessa ordem podem ser usadas independentemente do fato de que algumas delas serão descritas apenas nas seguintes partes da publicação.
O fim da observação.
Lista de funções Admtf_Table_Features de características da tabela de banco de dados
A função admtf_Table_Features retorna uma lista de características da própria tabela do banco de dados. O código-fonte pode ser visualizado e baixado aqui.
Como parâmetros, a função assume o nome da tabela de origem (a_TableName ) e o nome do esquema no qual a tabela é criada ( a_SchemaName ).
A função extrai os dados principais da entrada do catálogo pg_class , que contém, além das entradas da tabela, também registros sobre sequências, visualizações, visualizações materializadas e tipos compostos. Portanto, para selecionar tabelas, a condição relkind = 'r' é usada .
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);
Além disso, a função acessa os dados do diretório pg_namespace e pg_description . O primeiro contém os nomes dos esquemas do banco de dados e o segundo contém comentários sobre todos os objetos do banco de dados.
É importante prestar atenção à condição objsubid = 0 . Ele define o comentário na tabela, pois o valor do campo objetivo é o mesmo para a tabela e seus atributos. O comentário sobre o atributo da tabela está contido na entrada em que objsubid corresponde ao número desse atributo.
Tabela 4. O resultado da execução da função admtf_Table_Features ('public', 'Street').| Título | Comentário | Número de atributos | Número de restrições CHECK | ? existe uma chave primária | ? são índices declarados | ? existem descendentes | O número de entradas na tabela |
|---|
| rua | Lista de ruas em assentamentos | 22 | 0 0 | t | t | f | 20150 |
Observação 3
Preste atenção ao número de atributos da tabela de ruas. Difere significativamente do número de atributos especificados no esquema auxiliar.
Tabela 5. Atributos adicionais da tabela Street.
| attname | atttypid | attnum | Nota |
|---|
| cmin | 29 | -4 | Atributo do sistema |
| xmin | 28. | -3 | Atributo do sistema |
| ctid | 27 | -1 | Atributo do sistema |
| wcrccode | 795369 | 1 | Atributo válido |
| localityid | 795352 | 2 | Atributo válido |
| streetid | 795364 | 3 | Atributo válido |
| streettypeacrm | 1919168 | 4 | Atributo válido |
| nome da rua | 1043 | 5 | Atributo válido |
| ........ pg.dropped.6 ........ | 0 0 | 6 | Atributo remoto |
| ........ pg.dropped.7 ........ | 0 0 | 7 | Atributo remoto |
O fato é que o PostgreSQL, além dos principais atributos, leva em consideração vários atributos do sistema e até atributos excluídos.
Observação final
Função lista admtf_Table_Attributes dos atributos da tabela do banco de dados e suas características
A função admtf_Table_Attributes retorna uma lista de atributos da tabela de banco de dados. O código-fonte pode ser visualizado e baixado aqui.
Como parâmetros, a função assume o nome da tabela de origem ( a_TableName ) e o nome do esquema no qual a tabela é criada ( a_SchemaName ).
A função recupera os dados principais das entradas de diretório pg_attribute e pg_type . O primeiro contém registros com dados sobre os atributos de tabelas, visualizações, visualizações materializadas, tipos compostos e até funções. O segundo é sobre as características dos tipos de atributos.
Talvez seja necessário algum esclarecimento pela maneira como o usuário e os tipos de base são definidos na função.
Um atributo de tabela é declarado com um tipo personalizado se o campo typebasetype for maior que 0. na entrada de diretório pg_type correspondente, caso contrário, o atributo será do tipo base. Portanto, o diretório pg_type está envolvido duas vezes na cláusula FROM. Na primeira entrada de diretório, a presença de um tipo de usuário é determinada, se não for definida (tipo de tipo de base = 0 ), um valor de tipo base será gerado a partir dessa entrada. Caso contrário, o tipo base é determinado a partir do registro para o qual btyp.OID = typ.typbasetype.
Diretamente, a linha com o tipo base é formada usando a função do catálogo do sistema FORMAT_TYPE (type_oid, typemod) . O primeiro parâmetro é o registro OID do tipo base. O segundo parâmetro é o valor do modificador para tipos que contêm um tamanho. Por exemplo, VARCHAR (100) ou NUMERIC (4,2), DECIMAL (4,2). O valor do parâmetro typemod é obtido de typ.typtypmod se o atributo for de um tipo de usuário, caso contrário, de attr.atttypmod , ou seja, diretamente de um registro de atributo.
o código fonte do operador na figura 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;
Além disso, a função acessa os dados do diretório pg_class , pg_namespace e pg_description . O primeiro e o segundo diretórios são usados para procurar atributos pelos nomes da tabela de esquema e banco de dados.
O terceiro diretório é usado para recuperar o comentário para o atributo da tabela.
O comentário sobre o atributo da tabela está na entrada em que dsc.objoid contém o OID da tabela de origem e dsc.objsubid é o número de série do atributo na tabela, ou seja, attr.attnum .
Para impedir que a função retorne atributos remotos e do sistema, a cláusula WHERE define a condição attr.attnum> 0 AND attr.atttypID> 0 .
Tabela 6. O resultado da execução da função admtf_Table_Attributes ('public', 'Street').| Não. | Título | Tipo personalizado | Tipo de base | ? não NULL | Comentário |
|---|
| 1 | wcrccode | wcrccode | smallint | t | Código do país |
| 2 | localityid | localityid | inteiro | t | ID da comunidade |
| 3 | streetid | streetid | smallint | t | Rua identificação da vila |
| 4 | streettypeacrm | streettypeacrm | personagem (8) | f | Sigla da rua |
| 5 | nome da rua | | varchar (150) | t | Nome da rua do assentamento |
Versão da função usando alias de regclass para o tipo oid
Os identificadores de objeto do PostgreSQL (OIDs) são do mesmo tipo que o OID, atualmente implementado como um número inteiro de quatro bytes não assinado. Porém, devido à presença de aliases desse tipo, um número inteiro pode ser representado como o nome de um objeto. E vice-versa - converta o nome do objeto em um número inteiro do tipo OID.
Como exemplo, dê uma olhada na seguinte instrução SELECT . Ele recupera os nomes da tabela de atributos e os nomes de seus tipos de maneira incomum - em vez de acessar os campos de diretório correspondentes com os nomes dessas características, eles são usados:
- 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;
A seguir, o resultado da execução desta consulta.
Na lista de valores de saída do operador SELECT antes da conversão usando aliases do tipo OID, todos os valores, exceto o nome do atributo, são numéricos, mas, como resultado, os nomes da tabela e os tipos de atributos são exibidos. Os tipos de valores de saída podem ser considerados na segunda linha do cabeçalho da tabela.
Além disso, na cláusula WHERE da instrução, a condição attr.attrelid = ('public' || '.' || 'Street') :: regclass está localizada , no lado esquerdo do qual é um valor numérico e, à direita, é uma string que é convertida em um valor numérico com usando o alias regclass .
o código fonte do operador na figura 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;
Usando o alias regclass da instrução principal, você pode remover a conexão com dois diretórios. Mas essa melhoria quase não teve efeito no desempenho da função - nas duas versões, a função é executada em 11 ms. Talvez devido ao fato de a tabela de teste ter poucos atributos.
Observação 4Uma falha grave na condição no formato attr.attrelid = (a_SchemaName || '.' || a_TableName) :: regclass é manifestada quando há um esquema e / ou tabela com um nome incomum no banco de dados. Por exemplo, "Meu esquema" e / ou "Minha tabela" . Esses valores devem ser transmitidos entre aspas duplas ou usar a função QUOTE_IDENT, caso contrário, a função falhará com um erro do sistema.
o código fonte do operador na figura 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;
Portanto, prefiro usar condições no formato
LOWER (nspc.nspname) = LOWER (a_SchemaName) AND LOWER (tbl.relname) = LOWER (a_TableName) , o que não leva a erros do sistema.
Observação finalAPÊNDICE 1. Scripts
Criando a função admtf_Table_Features
Comentários sobre o código fonte da função podem ser encontrados aqui.código fonte da função 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;
Criando a função admtf_Table_Attributes
Comentários sobre o código fonte da função podem ser encontrados aqui.código fonte da função 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;
Criando a Função admtf_Table_Attributes Usando Alias de Regclass
Comentários sobre o código fonte da função podem ser encontrados aqui.código fonte da função 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;
APÊNDICE 2. Materiais adicionais
Esquema auxiliar de banco de dados
- PAÍS - Classificador de países do mundo - OKSM (classificação russa dos países do mundo);
- HOUSEADDR - Lista de números de casas nas ruas de assentamentos;
- LCLTYTYPE - Diretório de tipos de assentamentos;
- LOCALIDADE - Lista de assentamentos;
- RUA - Lista de ruas em assentamentos;
- STREETTYPE — ;
- TERRITORY — (, , , ..);
- TERRITORYTYPE — .
Street ()
.1. Street ().
| Categoria | Não. | Título | Comentário | tipo | Tipo de base | ? não NULL |
|---|
| tbl | 0 0 | rua | Lista de ruas em assentamentos | | | |
| att | 1 | wcrccode | Código do país | wcrccode | smallint | t |
| att | 2 | localityid | ID da comunidade | localityid | inteiro | t |
| att | 3 | streetid | Rua identificação da vila | streetid | smallint | t |
| att | 4 | streettypeacrm | Sigla da rua | streettypeacrm | personagem (8) | f |
| att | 5 | nome da rua | Nome da rua | streettypeacrm | varchar (150) | t |
| pk | 0 0 | xpkstreet | Chave primária da tabela de ruas | | | |
| pkatt | 1 | wcrccode | Código do país | wcrccode | smallint | t |
| pkatt | 2 | localityid | ID da comunidade | localityid | inteiro | t |
| pkatt | 3 | streetid | Rua identificação da vila | streetid | smallint | t |
| fk01 | 1 | fk_street_locality | Chave estrangeira da tabela | | | |
| fk01att | 1 | wcrccode | Código do país | wcrccode | smallint | t |
| fk01att | 2 | localityid | ID da comunidade | localityid | inteiro | t |
| fk01rtbl | 0 0 | localidade | | | | |
| fk01ratt | 1 | wcrccode | | wcrccode | smallint | t |
| fk01ratt | 2 | localityid | | localityid | integer | t |
| fk02 | 2 | fk_street_streettype | | | | |
| fk02att | 1 | streettypeacrm | | streettypeacrm | character(8) | f |
| fk02rtbl | 0 0 | streettype | | | | |
| fk02ratt | 1 | streettypeacrm | | streettypeacrm | character(8) | t |
| idx01 | 1 | xie1street | | | | |
| idx01att | 1 | wcrccode | | wcrccode | smallint | t |
| idx01att | 2 | localityid | | localityid | integer | t |
| idx01att | 3 | streettypeacrm | | streettypeacrm | character(8) | f |
| idx01att | 4 | streetname | | | varchar(150) | t |
| idx02 | 2 | xie2street | | | | |
| idx02att | 1 | wcrccode | | wcrccode | smallin | t |
| idx02att | 2 | localityid | | localityid | integer | t |
| idx02att | 3 | streetname | | | varchar(150) | t |
| idx03 | 3 | xie3street | | | | |
| idx03att | 1 | streetname | | | varchar(150) | t |
| idx04 | 4 | xpkstreet | ( ) street | | | |
| idx04att | 1 | wcrccode | | wcrccode | smallint | t |
| idx04att | 2 | localityid | | localityid | integer | t |
| idx04att | 3 | streetid | | streetid | smallint | t |