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