Esta é a segunda parte do artigo que descreve funções definidas pelo usuário para trabalhar com diretórios do sistema: pg_class, pg_attribute, pg_constraints etc.
Esta parte do artigo discute funções que
retornam as características de restrições e índices .
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 um ;
Funções para documentar bancos de dados PostgreSQL. Parte três .
Funções para documentar bancos de dados PostgreSQL. O fim (parte quatro) .
admtf_Table_Constraintes lista de restrições da tabela do banco de dados e suas características
A função admtf_Table_Constraintes retorna uma lista de restrições (CONSTRAINT) da tabela do banco de dados e suas características. O código-fonte pode ser visualizado e baixado aqui , e aqui está uma versão de uma função que não usa um cursor .
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 descrição de uma restrição específica é uma combinação do registro em pg_class descrevendo-o como uma relação física e o registro em pg_constraint contendo dados sobre as características específicas da restrição.
o código fonte do operador na figuraSELECT tbl.OID,con.conname,con.contype,con.conkey,reftbl.OID, reftbl.relname,con.confkey,con.consrc FROM pg_constraint con INNER JOIN pg_namespace nsp ON con.connamespace=nsp.oid LEFT OUTER JOIN pg_class tbl ON con.conrelid=tbl.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_TableOID) ORDER BY con.contype DESC,con.conname;
Os dados principais (nome e tipo de restrição) são recuperados da entrada do diretório pg_constraint . As características de cada restrição são extraídas do mesmo catálogo, que são apresentadas na forma de tabelas OID ( conrelid , confrelid ) ou matrizes de números de série de atributos ( conkey , confkey ) que participam da restrição.
A função retorna características de restrição na forma de nomes e atributos de tabela. Nesse caso, os nomes da tabela são extraídos da entrada do catálogo pg_class pelo identificador (OID) e os nomes dos atributos das entradas do catálogo pg_attribute pelo identificador da tabela e número de série do atributo. Porque Como os números de série são armazenados no diretório principal na forma de uma matriz (lista), as listas de nomes de atributos são formadas dentro da função usando um loop.
A função retorna uma característica especial - a regra para verificar os valores dos campos nas entradas da tabela (restrição CHECK). Essa característica é armazenada como um valor de texto no campo consrc do diretório pg_constraint .
Tabela 7. O resultado da execução da função admtf_Table_Constraintes ('public', 'Street').
A versão em texto da tabela na figura| Título | Tipo | Atributos da tabela de origem | O nome da tabela externa | Atributos de uma tabela externa | Regra de validação |
|---|
| xpkstreet | p | wcrccode, localityid, streetid | | | |
| fk_street_locality | f | wcrccode, localityid | localidade | wcrccode, localityid | |
| fk_street_streettype | f | streettypeacrm | streettype | streettypeacrm | |
| ck_street_streetname | c | nome da rua | | | ((nome da rua) :: texto! ~ * '[az]' :: texto) |
| ck_street_streettypeacrm | c | streettypeacrm | | | ((streettypeacrm) :: bpchar! ~ * '[az]' :: texto) |
Versão sem cursor
Prevejo perguntas e comentários sobre o uso do cursor na versão principal da função.
Não vou responder - não há camaradas pelo gosto e cor. Mas darei uma versão da função sem um cursor. A versão da implementação da função sem usar o cursor pode ser visualizada e baixada aqui .
A principal dificuldade é organizar a junção (JOIN) das tabelas pelos valores localizados no tipo de atributo da matriz de uma delas. Tais matrizes neste caso são conkey e confkey .
SELECT c.conname,c.contype,c.conkey::SMALLINT[], GENERATE_SUBSCRIPTS(c.conkey, 1) as No FROM pg_constraint c WHERE c.conname='fk_street_locality' ORDER BY No;
Para resolver esse problema, o PostgrSQL contém funções que retornam uma tabela de valores de ponteiros para elementos da matriz. No nosso caso, a função generate_subscripts será usada. Além de gerar muitos ponteiros para a posição da matriz passada como parâmetro, ele também transforma um registro que contém a matriz em vários pelo número de elementos na matriz. Cada registro dessa tabela contém um valor exclusivo - a posição da matriz.
Tabela 8. Propagando a sequência original usando generate_subscripts .| Nome da restrição | Tipo | Matriz de números de atributo | Ponteiro para uma posição de matriz |
|---|
| fk_street_locality | f | {1,2} | 1 |
| fk_street_locality | f | {1,2} | 2 |
o código fonte do operador na figura SELECT con.conname AS ConstraintName,con.contype::VARCHAR(2) AS ConstraintType, STRING_AGG(attr.attname, ', 'ORDER BY con.No) AS r_ConstraintKeyNames, reftbl.relname AS RefTableName, STRING_AGG(rattr.attname,', 'ORDER BY con.No) AS r_RefTableKeyNames, con.consrc AS ConstraintSource FROM (SELECT c.oid, c.conrelid,c.confrelid,c.conname,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_class tbl ON con.conrelid=tbl.oid INNER JOIN pg_attribute attr ON attr.attrelid=tbl.oid AND attr.attnum=con.conkey[con.No] INNER JOIN pg_namespace nsp ON tbl.relnamespace=nsp.oid LEFT OUTER JOIN pg_class reftbl ON con.confrelid=reftbl.oid LEFT OUTER JOIN pg_attribute rattr ON rattr.attrelid=reftbl.oid AND rattr.attnum=con.confkey[con.No] WHERE LOWER(nsp.nspname)=LOWER(a_SchemaName) AND LOWER(tbl.relname)=LOWER(a_TableName) GROUP BY con.conname,con.contype,reftbl.relname,con.consrc ORDER BY con.contype DESC,con.conname;
Essa tabela pode ser conectada ao catálogo de atributos pg_attribute extraindo nomes de atributos sob a condição attr.attrelid = tbl.oid AND attr.attnum = con.conkey [con.No] .
Agora resta remover registros desnecessários agrupando registros e criar uma sequência a partir dos nomes dos atributos.
A criação de uma linha é realizada usando a função de agregação STRING_AGG , na qual você deve especificar a opção de classificação (ORDER BY), caso contrário, a ordem dos atributos pode não estar de acordo com a ordem de declaração dos atributos no índice.
O tempo de execução de ambas as versões das funções coincidiu. Foram necessários 20 ms para gerar os dados na tabela de resultados.
Lista de funções Admtf_Table_Indexes dos índices da tabela do banco de dados e suas características
A função admtf_Table_Indexes retorna uma lista de índices (INDEX) da tabela do banco de dados e suas características. O código-fonte pode ser visualizado e baixado aqui , e aqui está uma versão de uma função que não usa um cursor .
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 ).
o código fonte do operador na figura SELECT tbl.oid,inxcls.relname,inxam.amname,inx.indisunique,inx.indisprimary, inx.indkey::SMALLINT[],inx.indoption::SMALLINT[],inxam.amcanorder FROM pg_index inx INNER JOIN pg_class inxcls ON inx.indexrelid=inxcls.oid INNER JOIN pg_namespace inxnsp ON inxcls.relnamespace=inxnsp.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 LOWER(tbl.relname)=LOWER(a_TableOID) ORDER BY inxam.amname, inxcls.relname;
A descrição de um único índice é uma combinação de um registro em pg_class descrevendo-o como uma relação física e um registro em pg_index contendo dados sobre as características específicas do índice. Além disso, informações sobre métodos de acesso ao índice são armazenadas no diretório do sistema pg_am.
CASE inxam.amcanorder WHEN true THEN CASE inx.indoption[inx.No] & 1 WHEN 1 THEN ' DESC' ELSE ' ASC' END ELSE '' END;
Um atributo de exclusividade do índice ( indisunique ), um sinal de que o índice é construído de acordo com a descrição da chave primária ( indisprimary ), bem como matrizes de números de série dos atributos da tabela, com base nos valores dos quais o índice ( indkey ) e os sinais da ordem de classificação dos valores dos atributos são extraídos da entrada do catálogo pg_index. no índice ( introdução ).
Na entrada do catálogo que descreve o método de acesso do índice pg_am, são extraídos o atributo de adequação dos dados incluídos no índice para classificação ( amcanorder ) e o nome ou o tipo do método de acesso do índice ( amname ).
Em outras palavras, o atributo amcanorder indica se é possível estabelecer uma ordem de classificação para os valores dos atributos incluídos no índice. Se amcanorder = true , a ordem de classificação pode ser especificada, caso contrário não. Na mesma figura, o significado dos valores da matriz de opções é visível - se o bit direito da forma binária do valor contiver 1B, o valor do atributo correspondente será classificado em ordem decrescente, caso contrário - em ordem crescente.
Listas de nomes de atributos incluídos no índice, bem como sinais da ordem dos valores dos atributos, são formados dentro da função usando um ciclo.
Tabela 9. O resultado da execução da função admtf_Table_Indexes ('public', 'Street').
A versão em texto da tabela na figura| Nome do índice | Método | ? Único | ? chave primária | Atributos no índice |
|---|
| xie1street | btree | f | f | ASC wcrccode, ASC localityid, ASC streettypeacrm, ASC nome da rua |
| xie2stree | btree | f | f | ASC wcrccode, ASC localityid, nome da rua ASC |
| xie3street | btree | f | f | nome da rua ASC |
| xie9street | btree | f | f | wcrccode ASC, localityid ASC, nome da rua DESC |
| xpkstreet | btree | t | t | wcrccode ASC, localityid ASC, streetid ASC |
| xts1street | gim | f | f | streettsvector |
| xts2street | gim | f | f | streettsvector |
Versão sem cursor
A abordagem para criar uma versão de uma função sem cursor é exatamente a mesma descrita na seção anterior:
- reprodução de registros usando generate_subscripts;
- agrupamento subsequente de registros;
- Criando uma lista de atributos de índice usando a função STRING_AGG com a opção ORDER BY.
o código fonte do operador na figura SELECT inxcls.relname AS r_IndexName ,inxam.amname AS r_IndexType, inx.indisunique AS r_isUnique,inx.indisprimary AS 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 LOWER(tbl.relname)=LOWER(a_TableName) GROUP BY inxcls.relname,inxam.amname,inx.indisunique,inx.indisprimary ORDER BY inxcls.relname;
O tempo de execução de ambas as versões das funções coincidiu, levei 20 ms para gerar os dados na tabela de resultados.
Portanto, não vou mais produzir versões de funções, pois quem quiser pode refazê-los ao seu gosto ou entrar em contato comigo, enviarei uma versão modificada gratuitamente .
Veja também a primeira , terceira e quarta partes do artigo.
APÊNDICE 1. Scripts
Criando a função admtf_Table_Constraintes
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_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; v_ConstraintOID INTEGER; v_ConstraintKeyNos SMALLINT[]; v_ConstraintName name; v_ConstraintType name; v_isUnique BOOLEAN; v_isPrimary BOOLEAN; v_AttributeNum INTEGER; v_AttributeName name; v_ConstraintKeyNames TEXT; v_RefTableOID INTEGER; v_RefTableName name; v_RefTableKeyNos SMALLINT[]; v_RefTableKeyNames TEXT; v_ConstraintSource TEXT; c_Delimiter CONSTANT VARCHAR(2):=',';
Criando uma versão do admtf_Table_Constraintes sem um cursor
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_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; v_ConstraintOID INTEGER; v_ConstraintKeyNos SMALLINT[]; v_ConstraintName name; v_ConstraintType name; v_isUnique BOOLEAN; v_isPrimary BOOLEAN; v_AttributeNum INTEGER; v_AttributeName name; v_ConstraintKeyNames TEXT; v_RefTableOID INTEGER; v_RefTableName name; v_RefTableKeyNos SMALLINT[]; v_RefTableKeyNames TEXT; v_ConstraintSource TEXT; c_Delimiter CONSTANT VARCHAR(2):=',';
Criando a função admtf_Table_Indexes
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_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; v_IndexOID INTEGER; v_IndexKeyNos SMALLINT[]; v_IndexName NAME; v_IndexAMName NAME; v_isUnique BOOLEAN; v_isPrimary BOOLEAN; v_AttributeNum INTEGER; v_AttributeName NAME; v_IndexKeyNames TEXT; c_Delimiter CONSTANT VARCHAR(2):=',';
Criando uma versão do admtf_Table_Indexes sem um cursor
. 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):=', ';