Skip to content
Create a gist now

Instantly share code, notes, and snippets.

Automatic index generation for all hstore keys
CREATE OR REPLACE FUNCTION indexes_for_hstore_column(full_table_name varchar, hstore_column varchar) RETURNS SETOF varchar AS $$
table_oid oid;
table_name varchar;
schema_name varchar;
key varchar;
index_name varchar;
index_exists integer;
unique_keys_query varchar;
create_index_statement varchar;
-- Split full_table_name on dots to account for schemas
schema_name := split_part(full_table_name, '.', 1);
table_name := split_part(full_table_name, '.', 2);
-- Fill in 'public' if there was no dot
IF table_name IS NULL THEN
table_name := schema_name;
schema_name := 'public';
-- Get the OID of the table we're working on
SELECT oid INTO table_oid
FROM pg_catalog.pg_class
WHERE relname=table_name AND relnamespace=(
SELECT oid FROM pg_catalog.pg_namespace
WHERE nspname=schema_name
-- Generate a query that will get a list of unique keys in the target column
unique_keys_query := format(
'SELECT DISTINCT (each(%I)).key FROM %I.%I',
hstore_column, schema_name, table_name
-- Loop over those keys
FOR key IN EXECUTE unique_keys_query LOOP
-- Generate the name of this index based on the [table, hstore, key] combination
index_name := table_name || '_' || hstore_column || '_' || key || '_key';
-- Test for existence of an index with this name
-- No, this does not check whether the index is indexing the right thing,
-- but I'm going to assume it does :-P
SELECT count(*) INTO index_exists
FROM pg_catalog.pg_class
WHERE relkind='i' AND relname=index_name AND relnamespace=(
SELECT oid FROM pg_catalog.pg_namespace
WHERE nspname=schema_name
-- If there is no such index...
IF index_exists = 0 THEN
-- a query to create it...
create_index_statement := format(
'CREATE INDEX %I ON %I.%I ((%I->%L));',
index_name, schema_name, table_name,
hstore_column, key
-- ...and put it in the list of statements to output
RETURN NEXT create_index_statement;
-- Pass back whatever statements we've accumulated

This is terrible, just terrible. But... once the idea entered my brain, I had to run with it.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.