Manual:Rebuild all text indexes on Postgres

Sometimes PostgreSQL indexes are not correct. You can rebuild them, creating a database function:

create or replace function YOUR_DATABASE_SCHEMA.rebuild_text_indexes() returns void as $$
	declare
		texto text;
		pc cursor for select * from YOUR_DATABASE_SCHEMA.pagecontent for update;
		rpc YOUR_DATABASE_SCHEMA.pagecontent%rowtype;
		p cursor for select * from YOUR_DATABASE_SCHEMA.page for update;
		rp YOUR_DATABASE_SCHEMA.page%rowtype;
	begin
		open pc;
		loop
			fetch pc into rpc;
			if not found then
				exit;
			end if;
			texto := rpc.old_text;
			if (strpos(texto, '__NOINDEX__') > 0) then
				texto := '';
			end if;
			update YOUR_DATABASE_SCHEMA.pagecontent set textvector = to_tsvector(texto) where current of pc;
		end loop;
		close pc;
		
		open p;
		loop
			fetch p into rp;
			if not found then
				exit;
			end if;
			texto := rp.page_title;
			update YOUR_DATABASE_SCHEMA.page set titlevector = to_tsvector(texto) where current of p;
		end loop;
		close p;
	end;
$$ LANGUAGE 'plpgsql';

where YOUR_DATABASE_SCHEMA is the schema where your wiki data are installed.

After, you can execute this function and rebuild text indexes:

select YOUR_DATABASE_SCHEMA.rebuild_text_indexes();

--Lodopidolo 10:18, 28 July 2010 (UTC)