Opened 6 years ago

Closed 6 years ago

#2361 closed enhancement (fixed)

[raster] Add index flag column to raster_columns

Reported by: Bborie Park Owned by: Bborie Park
Priority: medium Milestone: PostGIS 2.2.0
Component: raster Version: trunk
Keywords: history Cc:

Description

Add column to raster_columns view indicating if a raster column has an index...

The following query should be a decent starting point...

SELECT
	n.nspname AS "schema",
	c2.relname AS "table",
	c.relname AS "index",
	att.attname AS "column"
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_index i
	ON i.indexrelid = c.oid
JOIN pg_catalog.pg_class c2
	ON i.indrelid = c2.oid
JOIN pg_catalog.pg_namespace n
	ON n.oid = c.relnamespace
JOIN pg_am am
	ON c.relam = am.oid
JOIN pg_attribute att
	ON att.attrelid = c2.oid
		AND pg_catalog.format_type(att.atttypid, att.atttypmod) = 'raster'
WHERE c.relkind IN ('i')
	AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
	AND n.nspname = 'tops'
	AND am.amname = 'gist'
	AND strpos(split_part(pg_catalog.pg_get_indexdef(i.indexrelid, 0, true), ' gist ', 2), att.attname) > 0
ORDER BY 1,2,3;

Change History (1)

comment:1 Changed 6 years ago by Bborie Park

Keywords: history added
Milestone: PostGIS FuturePostGIS 2.2.0
Resolution: fixed
Status: newclosed

Added in r11553

Note: See TracTickets for help on using tickets.