Ini adalah bagian kedua dari artikel yang menjelaskan fungsi yang ditentukan pengguna untuk bekerja dengan direktori sistem: pg_class, pg_attribute, pg_constraints, dll.
Bagian artikel ini membahas fungsi yang
mengembalikan karakteristik kendala dan indeks .
Paruh pertama artikel memberikan komentar tentang implementasi fungsi. Yang kedua adalah kode sumber fungsi. Bagi pembaca yang hanya tertarik pada teks sumber, kami sarankan segera melanjutkan ke
Lampiran .
Lihat juga
Fungsi untuk mendokumentasikan database PostgreSQL. Bagian satu ;
Fungsi untuk mendokumentasikan database PostgreSQL. Bagian tiga .
Fungsi untuk mendokumentasikan database PostgreSQL. Akhir (bagian empat) .
admtf_Table_Constraintes daftar batasan tabel database dan karakteristiknya
Fungsi admtf_Table_Constraintes mengembalikan daftar kendala (CONSTRAINT) dari tabel database dan karakteristiknya. Kode sumber dapat dilihat dan diunduh di sini , dan ini adalah versi dari fungsi yang tidak menggunakan kursor .
Sebagai parameter, fungsi mengambil nama tabel sumber (
a_TableName ) dan nama skema tempat tabel dibuat (
a_SchemaName ).
Deskripsi batasan tertentu adalah kombinasi dari catatan di pg_class yang menggambarkannya sebagai hubungan fisik dan catatan di pg_constraint yang berisi data tentang karakteristik spesifik kendala.
kode sumber operator pada gambarSELECT 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;
Data utama (nama dan jenis pembatasan) diambil dari entri direktori pg_constraint . Karakteristik setiap kendala diekstraksi dari katalog yang sama, yang disajikan dalam bentuk tabel OID ( conrelid , confrelid ) atau array nomor seri atribut ( conkey , confkey ) yang berpartisipasi dalam kendala.
Fungsi mengembalikan karakteristik kendala dalam bentuk nama tabel dan atribut. Dalam hal ini, nama tabel diekstraksi dari entri katalog pg_class oleh identifier (OID), dan nama atribut dari entri katalog pg_attribute oleh pengidentifikasi tabel dan nomor seri atribut. Karena Karena nomor seri disimpan di direktori utama dalam bentuk array (daftar), maka daftar nama atribut dibentuk di dalam fungsi menggunakan loop.
Fungsi mengembalikan satu karakteristik khusus - aturan untuk memeriksa nilai-nilai bidang dalam entri tabel (PERIKSA pembatasan). Karakteristik ini disimpan sebagai nilai teks di bidang consrc pada direktori pg_constraint .
Tabel 7. Hasil menjalankan fungsi admtf_Table_Constraintes ('publik', 'Street').
Versi teks dari tabel pada gambar| Judul | Jenis | Atribut tabel sumber | Nama tabel eksternal | Atribut dari Tabel Eksternal | Aturan validasi |
|---|
| xpkstreet | hal | kode sandi, localityid, streetid | | | |
| fk_street_locality | f | kode sandi, localityid | lokalitas | kode sandi, localityid | |
| fk_street_streettype | f | streettypeacrm | streettype | streettypeacrm | |
| ck_street_streetname | c | nama jalan | | | ((nama jalan) :: teks! ~ * '[az]' :: teks) |
| ck_street_streettypeacrm | c | streettypeacrm | | | ((streettypeacrm) :: bpchar! ~ * '[az]' :: text) |
Versi tanpa kursor
Saya melihat pertanyaan dan komentar tentang penggunaan kursor di versi utama fungsi.
Saya tidak akan menjawab - tidak ada kawan untuk rasa dan warnanya. Tapi saya akan memberikan versi fungsi tanpa kursor. Versi implementasi fungsi tanpa menggunakan kursor dapat dilihat dan diunduh di sini .
Kesulitan utama adalah untuk mengatur join (GABUNG) dari tabel sesuai dengan nilai-nilai yang terletak pada tipe atribut dari array salah satunya. Array dalam kasus ini adalah conkey dan 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;
Untuk mengatasi masalah seperti itu, PostgrSQL berisi fungsi yang mengembalikan tabel nilai pointer ke elemen array. Dalam kasus kami, fungsi generate_subscripts akan digunakan. Tidak hanya menghasilkan banyak pointer ke posisi array yang diteruskan sebagai parameter, itu juga mengubah satu catatan yang berisi array menjadi beberapa dengan jumlah elemen dalam array. Setiap catatan dari tabel tersebut mengandung satu nilai unik - posisi array.
Tabel 8. Mempropagandakan string asli menggunakan gener_subscript .| Nama batasan | Jenis | Array nomor atribut | Pointer ke posisi array |
|---|
| fk_street_locality | f | {1,2} | 1 |
| fk_street_locality | f | {1,2} | 2 |
kode sumber operator pada gambar 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;
Tabel seperti itu dapat dihubungkan ke katalog atribut pg_attribute dengan mengekstraksi nama atribut darinya di bawah kondisi attr.attrelid = tbl.oid DAN attr.attnum = con.conkey [con.No] .
Sekarang tinggal menghapus catatan yang tidak perlu dengan mengelompokkan catatan, dan membuat string dari nama atribut.
Membuat baris dilakukan dengan menggunakan fungsi agregasi STRING_AGG , di mana Anda harus menentukan opsi pengurutan (ORDER BY), jika tidak, urutan atribut mungkin tidak sesuai dengan urutan pernyataan atribut di indeks.
Waktu eksekusi kedua versi fungsi bertepatan. Butuh 20 ms untuk menampilkan data dalam tabel hasil.
Admtf_Table_Indexes daftar fungsi indeks tabel database dan karakteristiknya
Fungsi admtf_Table_Indexes mengembalikan daftar indeks (INDEX) dari tabel database dan karakteristiknya. Kode sumber dapat dilihat dan diunduh di sini , dan ini adalah versi dari fungsi yang tidak menggunakan kursor .
Sebagai parameter, fungsi tersebut mengambil nama tabel sumber (
a_TableName ) dan nama skema tempat tabel dibuat (
a_SchemaName ).
kode sumber operator pada gambar 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;
Deskripsi indeks tunggal adalah kombinasi dari catatan di pg_class yang menggambarkannya sebagai hubungan fisik dan catatan di pg_index yang berisi data tentang karakteristik spesifik indeks. Selain itu, informasi tentang metode akses indeks disimpan di direktori sistem pg_am.
CASE inxam.amcanorder WHEN true THEN CASE inx.indoption[inx.No] & 1 WHEN 1 THEN ' DESC' ELSE ' ASC' END ELSE '' END;
Atribut keunikan indeks ( indisunique ), tanda bahwa indeks dibangun sesuai dengan deskripsi kunci primer ( indisprimary ), serta array nomor seri atribut tabel, berdasarkan nilai-nilai yang indeks ( indkey ) dan tanda-tanda urutan nilai atribut pengurutan diekstraksi dari entri katalog pg_index, diekstraksi dalam indeks ( indoption ).
Dari entri katalog yang menggambarkan metode akses indeks pg_am , atribut kesesuaian data termasuk dalam indeks untuk pengurutan ( amcanorder ) dan nama atau jenis metode akses indeks ( amname ) diekstraksi .
Dengan kata lain, atribut amcanorder menunjukkan apakah mungkin untuk menetapkan urutan untuk nilai atribut yang termasuk dalam indeks. Jika amcanorder = true , maka urutan sortir dapat ditentukan, jika tidak, tidak. Dari gambar yang sama, arti dari nilai-nilai array indoption terlihat - jika bit kanan dari bentuk biner dari nilai berisi 1B, maka nilai atribut yang sesuai diurutkan dalam urutan menurun, jika tidak - dalam urutan naik.
Daftar nama atribut yang termasuk dalam indeks, serta tanda-tanda pemesanan nilai atribut, dibentuk di dalam fungsi menggunakan siklus.
Tabel 9. Hasil mengeksekusi fungsi admtf_Table_Indexes ('publik', 'Street').
Versi teks dari tabel pada gambar| Nama Indeks | Metode | ? Unik | ? kunci utama | Atribut dalam indeks |
|---|
| xie1 jalan | btree | f | f | wcrccode ASC, ASC localityid, streettypeacrm ASC, streetname ASC |
| xie2stree | btree | f | f | wcrccode ASC, localityid ASC, streetname ASC |
| xie3jalan | btree | f | f | streetname ASC |
| "Aku jalan | btree | f | f | wcrccode ASC, localityid ASC, streetname DESC |
| xpkstreet | btree | t | t | wcrccode ASC, lokalitas ASC, streetid ASC |
| xts1street | gin | f | f | streettsvector |
| xts2street | gin | f | f | streettsvector |
Versi tanpa kursor
Pendekatan untuk membuat versi fungsi tanpa kursor sama persis seperti yang dijelaskan di bagian sebelumnya:
- reproduksi rekaman menggunakan generate_subscript;
- pengelompokan catatan selanjutnya;
- Membuat daftar atribut indeks menggunakan fungsi STRING_AGG dengan opsi ORDER BY.
kode sumber operator pada gambar 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;
Waktu pelaksanaan kedua versi fungsi bertepatan, saya butuh 20 ms untuk menampilkan data dalam tabel hasil.
Karenanya, saya tidak akan lagi menghasilkan versi fungsi, seperti mereka yang ingin dapat membuat kembali sesuai dengan keinginan mereka atau menghubungi saya, saya akan mengirim versi yang dimodifikasi secara gratis .
Lihat juga bagian pertama , ketiga dan keempat artikel.
LAMPIRAN 1. Skrip
Membuat Fungsi admtf_Table_Constraintes
Komentar pada kode sumber fungsi dapat ditemukan di sini.kode sumber fungsi 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):=',';
Membuat versi admtf_Table_Constraintes tanpa kursor
Komentar pada kode sumber fungsi dapat ditemukan di sini.kode sumber fungsi 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):=',';
Membuat Fungsi admtf_Table_Indexes
Komentar pada kode sumber fungsi dapat ditemukan di sini.kode sumber fungsi 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):=',';
Membuat versi admtf_Table_Indexes tanpa kursor
. 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):=', ';