PostgreSQL判断表的某个字段是否存在索引_pgsql 判断索引是否存在

-- 函数: gis_index_exists

-- 功能: 某个字段上是否存在索引

-- 参数:

-- schemaname

-- tablename

-- columnname

-- 返回:

-- true 索引存在

-- false 无索引

CREATEOR REPLACE FUNCTION gis_index_exists(schemaname varchar, tablename varchar, columnname varchar) returnsBOOLEANas $$

select

count(1) = 1

from

pg_class t,

pg_class i,

pg_index ix,

pg_attribute a,

pg_namespace n

where

t.oid = ix.indrelid

and i.oid = ix.indexrelid

and a.attrelid = t.oid

and a.attnum = ANY(ix.indkey)

and t.relkind = 'r'

and n.nspname = $1

and t.relname = $2

and a.attname = $3

;

$$ LANGUAGE'sql';

-- 函数: gis_index_exists

-- 功能: 某个字段是否存在索引

-- 参数:

-- tablename

-- columnname

-- 返回:

-- true 索引存在

-- false 无索引

CREATEOR REPLACE FUNCTION gis_index_exists(tablename varchar, columnname varchar) returnsBOOLEANas $$

select gis_index_exists('public',$1,$2);

$$ LANGUAGE'sql';

-- 用法

select gis_index_exists('public','tablename','columnname') ;

select gis_index_exists('tablename','columnname') ;