Il s'agit de la deuxième partie de l'article qui décrit les fonctions définies par l'utilisateur pour travailler avec les répertoires système: pg_class, pg_attribute, pg_constraints, etc.
Cette partie de l'article traite des fonctions qui
renvoient les caractéristiques des contraintes et des index .
La première moitié de l'article fournit des commentaires sur la mise en œuvre des fonctions. Le second est le code source des fonctions. Pour les lecteurs qui ne sont intéressés que par les textes sources, nous suggérons de passer immédiatement à l'
annexe .
Voir aussi
Fonctions de documentation des bases de données PostgreSQL. Première partie ;
Fonctions de documentation des bases de données PostgreSQL. Troisième partie .
Fonctions de documentation des bases de données PostgreSQL. La fin (quatrième partie) .
admtf_Table_Constraintes liste des restrictions de table de base de données et leurs caractéristiques
La fonction admtf_Table_Constraintes renvoie une liste des contraintes (CONSTRAINT) de la table de base de données et leurs caractéristiques. Le code source peut être consulté et téléchargé ici , et voici une version d'une fonction qui n'utilise pas de curseur .
En tant que paramètres, la fonction prend le nom de la table source (
a_TableName ) et le nom du schéma dans lequel la table est créée (
a_SchemaName ).
La description d'une contrainte particulière est une combinaison de l'enregistrement dans pg_class la décrivant comme une relation physique et l'enregistrement dans pg_constraint contenant des données sur les caractéristiques spécifiques de la contrainte.
le code source de l'opérateur sur la figureSELECT 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;
Les données principales (nom et type de restriction) sont extraites de l' entrée du répertoire pg_constraint . Les caractéristiques de chaque contrainte sont extraites du même catalogue, qui sont présentées sous forme de tables OID ( conrelid , confrelid ) ou de tableaux de numéros de série d'attributs ( conkey , confkey ) participant à la contrainte.
La fonction renvoie des caractéristiques de contrainte sous la forme de noms et d'attributs de table. Dans ce cas, les noms de table sont extraits de l' entrée de catalogue pg_class par identifiant (OID), et les noms d' attribut des entrées de catalogue pg_attribute par l'identifiant de table et le numéro de série d'attribut. Parce que Étant donné que les numéros de série sont stockés dans le répertoire principal sous la forme d'un tableau (liste), des listes de noms d'attributs sont formées à l'intérieur de la fonction à l'aide d'une boucle.
La fonction renvoie une caractéristique spéciale - la règle de vérification des valeurs des champs dans les entrées de table (restriction CHECK). Cette caractéristique est stockée sous forme de valeur de texte dans le champ consrc du répertoire pg_constraint .
Tableau 7. Résultat de l'exécution de la fonction admtf_Table_Constraintes ('public', 'Street').
La version texte du tableau de la figure| Le titre | Tapez | Attributs de table source | Le nom de la table externe | Attributs d'une table externe | Règle de validation |
|---|
| xpkstreet | p | wcrccode, localityid, streetid | | | |
| fk_street_locality | f | wcrccode, localityid | localité | wcrccode, localityid | |
| fk_street_streettype | f | streettypeacrm | streettype | streettypeacrm | |
| ck_street_streetname | c | nom de rue | | | ((nom de rue) :: text! ~ * '[az]' :: text) |
| ck_street_streettypeacrm | c | streettypeacrm | | | ((streettypeacrm) :: bpchar! ~ * '[az]' :: texte) |
Version sans curseur
Je prévois des questions et commentaires sur l'utilisation du curseur dans la version principale de la fonction.
Je ne répondrai pas - il n'y a pas de camarades pour le goût et la couleur. Mais je vais donner une version de la fonction sans curseur. La version de l'implémentation de la fonction sans utiliser le curseur peut être consultée et téléchargée ici .
La principale difficulté est d'organiser la jointure (JOIN) des tables par les valeurs situées dans le type d'attribut du tableau de l'une d'entre elles. Dans ce cas, ces tableaux sont conkey et 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;
Pour résoudre un tel problème, PostgrSQL contient des fonctions qui renvoient une table de valeurs de pointeurs vers des éléments de tableau. Dans notre cas, la fonction generate_subscripts sera utilisée. Non seulement il génère de nombreux pointeurs vers la position du tableau qui lui est transmise en tant que paramètre, mais il transforme également un enregistrement contenant le tableau en plusieurs par le nombre d'éléments du tableau. Chaque enregistrement d'une telle table contient une valeur unique - la position du tableau.
Tableau 8. Propagation de la chaîne d'origine à l'aide de generate_subscripts .| Nom de restriction | Tapez | Tableau de numéros d'attribut | Pointeur vers une position de tableau |
|---|
| fk_street_locality | f | {1,2} | 1 |
| fk_street_locality | f | {1,2} | 2 |
le code source de l'opérateur sur la figure 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;
Une telle table peut être connectée au catalogue d' attributs pg_attribute en en extrayant les noms d'attribut sous la condition attr.attrelid = tbl.oid AND attr.attnum = con.conkey [con.No] .
Il reste maintenant à supprimer les enregistrements inutiles en regroupant les enregistrements et à créer une chaîne à partir des noms des attributs.
La création d'une ligne s'effectue à l'aide de la fonction d'agrégation STRING_AGG , dans laquelle vous devez spécifier l'option de tri (ORDER BY), sinon l'ordre des attributs peut ne pas être conforme à l'ordre de déclaration des attributs dans l'index.
Le temps d'exécution des deux versions des fonctions a coïncidé. Il a fallu 20 ms pour sortir les données dans le tableau des résultats.
Admtf_Table_Indexes, fonction liste des index de table de base de données et leurs caractéristiques
La fonction admtf_Table_Indexes renvoie une liste d'index (INDEX) de la table de base de données et leurs caractéristiques. Le code source peut être consulté et téléchargé ici , et voici une version d'une fonction qui n'utilise pas de curseur .
En tant que paramètres, la fonction prend le nom de la table source (
a_TableName ) et le nom du schéma dans lequel la table est créée (
a_SchemaName ).
le code source de l'opérateur sur la figure 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;
La description d'un seul index est une combinaison d'un enregistrement dans pg_class le décrivant comme une relation physique et d'un enregistrement dans pg_index contenant des données sur les caractéristiques spécifiques de l'index. De plus, les informations sur les méthodes d'accès aux index sont stockées dans le répertoire système pg_am.
CASE inxam.amcanorder WHEN true THEN CASE inx.indoption[inx.No] & 1 WHEN 1 THEN ' DESC' ELSE ' ASC' END ELSE '' END;
Un attribut d'unicité d'index ( indisunique ), un signe que l'index est construit conformément à la description de la clé primaire ( indisprimary ), ainsi que des tableaux de numéros de série des attributs de table, basés sur les valeurs dont l'index ( indkey ) et les signes de l'ordre de tri des valeurs d'attribut sont extraits de l' entrée de catalogue pg_index, sont extraits dans l'index ( indoption ).
À partir de l'entrée de catalogue décrivant la méthode d'accès de l'index pg_am , l'attribut de l'adéquation des données triées incluses dans l'index ( amcanorder ) et le nom ou le type de la méthode d'accès à l'index ( amname ) sont extraits .
En d'autres termes, l'attribut amcanorder indique s'il est possible d'établir un ordre de tri pour les valeurs des attributs inclus dans l'index. Si amcanorder = true , l'ordre de tri peut être spécifié, sinon non. Sur la même figure, la signification des valeurs du tableau d' indoption est visible - si le bit droit de la forme binaire de la valeur contient 1B, alors la valeur de l'attribut correspondant est triée par ordre décroissant, sinon - par ordre croissant.
Des listes de noms d'attributs inclus dans l'index, ainsi que des signes de classement des valeurs d'attribut, sont formées à l'intérieur de la fonction à l'aide d'un cycle.
Tableau 9. Résultat de l'exécution de la fonction admtf_Table_Indexes ('public', 'Street').
La version texte du tableau de la figure| Nom d'index | La méthode | ? Unique | ? clé primaire | Attributs dans l'index |
|---|
| xie1street | btree | f | f | wcrccode ASC, localityid ASC, streettypeacrm ASC, streetname ASC |
| xie2stree | btree | f | f | wcrccode ASC, localityid ASC, streetname ASC |
| xie3street | btree | f | f | nom de rue ASC |
| xie9street | btree | f | f | wcrccode ASC, localityid ASC, nom de rue DESC |
| xpkstreet | btree | t | t | wcrccode ASC, localityid ASC, streetid ASC |
| xts1street | Gin | f | f | streettsvector |
| xts2street | Gin | f | f | streettsvector |
Version sans curseur
L'approche pour créer une version d'une fonction sans curseur est exactement la même que celle décrite dans la section précédente:
- reproduction d'enregistrements à l'aide de generate_subscripts;
- regroupement ultérieur des enregistrements;
- Création d'une liste d'attributs d'index à l'aide de la fonction STRING_AGG avec l'option ORDER BY.
le code source de l'opérateur sur la figure 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;
Le temps d'exécution des deux versions des fonctions a coïncidé, il m'a fallu 20 ms pour sortir les données dans le tableau de résultats.
Par conséquent, je ne produirai plus de versions de fonctions, comme ceux qui le souhaitent peuvent les refaire à leur guise ou me contacter, j'enverrai gratuitement une version modifiée .
Voir également les première , troisième et quatrième parties de l'article.
ANNEXE 1. Scripts
Création de la fonction admtf_Table_Constraintes
Vous trouverez des commentaires sur le code source de la fonction ici.code source de la fonction 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):=',';
Création d'une version d'admtf_Table_Constraintes sans curseur
Vous trouverez des commentaires sur le code source de la fonction ici.code source de la fonction 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):=',';
Création de la fonction admtf_Table_Indexes
Vous trouverez des commentaires sur le code source de la fonction ici.code source de la fonction 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):=',';
Création d'une version d'admtf_Table_Indexes sans curseur
. 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):=', ';
Voir aussi
PostgreSQL. ;PostgreSQL. .PostgreSQL. ( ) .