public
Last active

Automatic index generation for all hstore keys

  • Download Gist
indexes_for_hstore_column.sql
SQL
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69
CREATE OR REPLACE FUNCTION indexes_for_hstore_column(full_table_name varchar, hstore_column varchar) RETURNS SETOF varchar AS $$
DECLARE
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;
BEGIN
-- 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';
END IF;
-- 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
-- ...build 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;
END IF;
END LOOP;
-- Pass back whatever statements we've accumulated
RETURN;
END
$$ LANGUAGE plpgsql STABLE;

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

Please sign in to comment on this gist.

Something went wrong with that request. Please try again.