Projects:DB Support Indexes With Functions/Technical Specifications
Contents |
In PostgreSQL
This query returns the indexes defined for a given table, along with the index pression for function based triggers
SELECT PG_CLASS.RELNAME, PG_CLASS1.RELNAME, CASE PG_INDEX.indisunique WHEN true THEN 'UNIQUE' ELSE 'NONUNIQUE' END, PG_GET_EXPR(PG_INDEX.indexprs,PG_INDEX.indrelid, true) FROM PG_INDEX, PG_CLASS, PG_CLASS PG_CLASS1, PG_NAMESPACE WHERE PG_INDEX.indexrelid = PG_CLASS.OID AND PG_INDEX.indrelid = PG_CLASS1.OID AND PG_CLASS.RELNAMESPACE = PG_NAMESPACE.OID AND PG_CLASS1.RELNAMESPACE = PG_NAMESPACE.OID AND PG_NAMESPACE.NSPNAME = CURRENT_SCHEMA() AND PG_INDEX.INDISPRIMARY ='f' AND PG_CLASS1.relname = 'c_bpartner' AND PG_CLASS.RELNAME NOT IN (SELECT pg_constraint.conname::text FROM pg_constraint JOIN pg_class ON pg_class.oid = pg_constraint.conrelid WHERE pg_constraint.contype = 'u')
From the trigger expression it is easy to know what functions apply to what columns.
In Oracle
SELECT U.INDEX_NAME, U.UNIQUENESS, UE.COLUMN_EXPRESSION FROM USER_INDEXES U LEFT JOIN USER_IND_EXPRESSIONS UE ON U.INDEX_NAME = UE.INDEX_NAME WHERE U.TABLE_NAME = 'C_BPARTNER' AND (U.INDEX_TYPE = 'NORMAL' OR U.INDEX_TYPE = 'FUNCTION-BASED NORMAL') AND NOT EXISTS (SELECT 1 FROM USER_CONSTRAINTS WHERE TABLE_NAME = U.TABLE_NAME AND INDEX_NAME = U.INDEX_NAME AND CONSTRAINT_TYPE IN ('U', 'P')) ORDER BY INDEX_NAME
How to check if a function only has one input parameter
In PostgreSQL
SELECT count(*) FROM pg_proc WHERE upper(proname) = ? AND pronargs = 1
In Oracle
SELECT count(count(*)) FROM ALL_ARGUMENTS WHERE OBJECT_NAME = ? AND in_out = 'IN' GROUP BY subprogram_id HAVING count(*) = 1