Esta es la segunda parte del artículo que describe funciones definidas por el usuario para trabajar con directorios del sistema: pg_class, pg_attribute, pg_constraints, etc.
Esta parte del artículo analiza funciones que
devuelven las características de restricciones e índices .
La primera mitad del artículo proporciona comentarios sobre la implementación de funciones. El segundo es el código fuente de las funciones. Para aquellos lectores que solo están interesados en los textos de origen, sugerimos proceder inmediatamente al
Apéndice .
Ver también
Funciones para documentar bases de datos PostgreSQL. Primera parte ;
Funciones para documentar bases de datos PostgreSQL. Tercera parte
Funciones para documentar bases de datos PostgreSQL. El final (parte cuatro) .
admtf_Table_Constraintes lista de restricciones de tabla de base de datos y sus características
La función admtf_Table_Constraintes devuelve una lista de restricciones (CONSTRAINT) de la tabla de la base de datos y sus características. El código fuente se puede ver y descargar aquí , y aquí hay una versión de una función que no utiliza un cursor .
Como parámetros, la función toma el nombre de la tabla fuente (
a_TableName ) y el nombre del esquema dentro del cual se crea la tabla (
a_SchemaName ).
La descripción de una restricción particular es una combinación del registro en pg_class que lo describe como una relación física y el registro en pg_constraint que contiene datos sobre las características específicas de la restricción.
El código fuente del operador en la 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;
Los datos principales (nombre y tipo de restricción) se recuperan de la entrada del directorio pg_constraint . Las características de cada restricción se extraen del mismo catálogo, que se presenta en forma de tablas OID ( conrelid , confrelid ) o matrices de números de serie de atributos ( conkey , confkey ) que participan en la restricción.
La función devuelve características de restricción en forma de nombres de tabla y atributos. En este caso, los nombres de tabla se extraen de la entrada de catálogo pg_class por identificador (OID), y los nombres de atributo de las entradas de catálogo pg_attribute por el identificador de tabla y el número de serie del atributo. Porque Como los números de serie se almacenan en el directorio principal en forma de matriz (lista), las listas de nombres de atributos se forman dentro de la función mediante un bucle.
La función devuelve una característica especial: la regla para verificar los valores de los campos en las entradas de la tabla (restricción CHECK). Esta característica se almacena como un valor de texto en el campo consrc del directorio pg_constraint .
Tabla 7. El resultado de ejecutar la función admtf_Table_Constraintes ('public', 'Street').
La versión de texto de la tabla en la figura.| Titulo | Tipo | Atributos de la tabla de origen | El nombre de la tabla externa. | Atributos de una tabla externa | Regla de validación |
|---|
| xpkstreet | p | wcrccode, localityid, streetid | | | |
| fk_street_locality | f | wcrccode, localityid | localidad | wcrccode, localityid | |
| fk_street_streettype | f | streettypeacrm | tipo de calle | streettypeacrm | |
| ck_street_streetname | c | nombre de calle | | | ((streetname) :: text! ~ * '[az]' :: text) |
| ck_street_streettypeacrm | c | streettypeacrm | | | ((streettypeacrm) :: bpchar! ~ * '[az]' :: texto) |
Versión sin cursor
Preveo preguntas y comentarios sobre el uso del cursor en la versión principal de la función.
No responderé: no hay compañeros para el sabor y el color. Pero daré una versión de la función sin cursor. La versión de la implementación de la función sin usar el cursor se puede ver y descargar aquí .
La principal dificultad es organizar la unión (JOIN) de las tablas de acuerdo con los valores ubicados en el tipo de atributo de la matriz de una de ellas. Tales matrices en este caso son conkey y 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 este problema, PostgrSQL contiene funciones que devuelven una tabla de valores de punteros a elementos de matriz. En nuestro caso, se utilizará la función generate_subscripts . No solo genera muchos punteros a la posición de la matriz que se le pasa como parámetro, sino que también convierte un registro que contiene la matriz en varios por el número de elementos en la matriz. Cada registro de dicha tabla contiene un valor único: la posición de la matriz.
Tabla 8. Propagación de la cadena original utilizando generate_subscripts .| Nombre de restricción | Tipo | Matriz de números de atributo | Puntero a una posición de matriz |
|---|
| fk_street_locality | f | {1,2} | 1 |
| fk_street_locality | f | {1,2} | 2 |
El código fuente del operador en la 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;
Dicha tabla se puede conectar al catálogo de atributos pg_attribute , extrayendo nombres de atributos de la misma por la condición attr.attrelid = tbl.oid AND attr.attnum = con.conkey [con.No] .
Ahora queda eliminar registros innecesarios agrupando registros y crear una cadena a partir de los nombres de los atributos.
La creación de una fila se realiza utilizando la función de agregación STRING_AGG , en la que debe especificar la opción de ordenación (ORDER BY); de lo contrario, el orden de los atributos puede no coincidir con el orden de declaración de los atributos en el índice.
El tiempo de ejecución de ambas versiones de las funciones coincidió. Se necesitaron 20 ms para generar los datos en la tabla de resultados.
Lista de funciones Admtf_Table_Indexes de índices de tablas de bases de datos y sus características
La función admtf_Table_Indexes devuelve una lista de índices (INDEX) de la tabla de la base de datos y sus características. El código fuente se puede ver y descargar aquí , y aquí hay una versión de una función que no utiliza un cursor .
Como parámetros, la función toma el nombre de la tabla fuente (
a_TableName ) y el nombre del esquema dentro del cual se crea la tabla (
a_SchemaName ).
El código fuente del operador en la 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;
La descripción de un índice único es una combinación de un registro en pg_class que lo describe como una relación física y un registro en pg_index que contiene datos sobre las características específicas del índice. Además, la información sobre los métodos de acceso al índice se almacena en el directorio del sistema pg_am.
CASE inxam.amcanorder WHEN true THEN CASE inx.indoption[inx.No] & 1 WHEN 1 THEN ' DESC' ELSE ' ASC' END ELSE '' END;
Un atributo de unicidad de índice ( indisunique ), un signo de que el índice se construye de acuerdo con la descripción de la clave primaria ( indisprimary ), así como las matrices de números de serie de los atributos de la tabla, en función de los valores de los cuales se extraen el índice ( indkey ) y los signos del orden de clasificación de los valores de los atributos de la entrada de catálogo pg_index. en el índice ( indopción ).
De la entrada de catálogo que describe el método de acceso del índice pg_am , se extraen el atributo de idoneidad de los datos ordenados incluidos en el índice ( amcanorder ) y el nombre o tipo del método de acceso al índice ( amname ).
En otras palabras, el atributo amcanorder indica si es posible establecer un orden de clasificación para los valores de los atributos incluidos en el índice. Si amcanorder = true , se puede especificar el orden de clasificación, de lo contrario no. A partir de la misma figura, el significado de los valores de la matriz de indopción es visible : si el bit derecho de la forma binaria del valor contiene 1B, entonces el valor del atributo correspondiente se ordena en orden descendente, de lo contrario, en orden ascendente.
Las listas de nombres de atributos incluidos en el índice, así como los signos del orden de los valores de los atributos, se forman dentro de la función utilizando un ciclo.
Tabla 9. El resultado de ejecutar la función admtf_Table_Indexes ('public', 'Street').
La versión de texto de la tabla en la figura.| Nombre del índice | Método | ? Único | ? clave primaria | Atributos en el índice |
|---|
| 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 | nombre de la calle ASC |
| xie9street | btree | f | f | wcrccode ASC, localityid ASC, streetname DESC |
| xpkstreet | btree | t | t | wcrccode ASC, localityid ASC, streetid ASC |
| xts1street | ginebra | f | f | streettsvector |
| xts2street | ginebra | f | f | streettsvector |
Versión sin cursor
El enfoque para crear una versión de una función sin cursor es exactamente el mismo que se describe en la sección anterior:
- reproducción de registros usando generate_subscripts;
- posterior agrupación de registros;
- Crear una lista de atributos de índice utilizando la función STRING_AGG con la opción ORDER BY.
El código fuente del operador en la 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;
El tiempo de ejecución de ambas versiones de las funciones coincidió, tardé 20 ms en generar los datos en la tabla de resultados.
Por lo tanto, ya no produciré versiones de funciones, como aquellos que lo deseen pueden rehacerlos a su gusto o contactarme, les enviaré una versión modificada gratis .
Ver también la primera , tercera y cuarta parte del artículo.
APÉNDICE 1. Scripts
Crear la función admtf_Table_Constraintes
Los comentarios sobre el código fuente de la función se pueden encontrar aquí.código fuente de la función 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):=',';
Crear una versión de admtf_Table_Constraintes sin cursor
Los comentarios sobre el código fuente de la función se pueden encontrar aquí.código fuente de la función 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):=',';
Crear la función admtf_Table_Indexes
Los comentarios sobre el código fuente de la función se pueden encontrar aquí.código fuente de la función 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):=',';
Crear una versión de admtf_Table_Indexes sin 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):=', ';
Ver también
PostgreSQL. ;PostgreSQL. .PostgreSQL. ( ) .