Automatic index generation for all hstore keys

indexes_for_hstore_column.sql

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.