Projects:DB Support Indexes With Functions/Technical Specifications

Contents 1 How to retrieve information related with function based indexes 1.1 In PostgreSQL 1.2 In Oracle 2 How to check if a function only has one input parameter 2.1 In PostgreSQL 2.2 In Oracle

How to retrieve information related with function based indexes

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