Script to store Nuxeo fulltext field as tsvector instead of text. See https://jira.nuxeo.com/browse/NXP-11897 for more information.

NXP-11897-convert-fulltext-to-tsvector.sql

-- ------------------------------------------------------------
-- NXP-11897 Script to store fulltext field as tsvector instead of text
-- WARNING:
-- 1. Be aware that after converting the fulltext table from text to tsvector
--    the phrase search will not work anymore
-- 2. Make sure to backup you database before running the script
-- 3. Make sure that your fulltext table is not already using tsvector (From
--    psql type '\d fullltext' and check that the fulltext field is 'text'
-- 4. Run the script using psql using the same user as the Nuxeo instance
-- Note that the old fulltext table is renamed into fulltext_old
\timing on
-- Build the statement to create a new fulltext table
\o /tmp/nxp-11897-new-table.sql
\pset tuples_only
SELECT 'SELECT id, jobid, ' || (SELECT array_to_string(array_agg(n.col), ', ') FROM (SELECT 'NX_TO_TSVECTOR(' || attname|| ') AS ' || attname AS col FROM pg_attribute WHERE  attrelid = 'fulltext'::regclass   
AND NOT attisdropped AND attnum > 0 AND (attname LIKE ('simpletext%') OR attname LIKE ('binarytext%') OR attname LIKE ('fulltext%'))
ORDER  BY attnum) as n) || ' INTO fulltext_new FROM (SELECT * FROM FULLTEXT) AS bar;';
\o
-- Execute
\i /tmp/nxp-11897-new-table.sql

-- Build the statement to rename indexes and create new one
\o /tmp/nxp-11897-rename-idx.sql
SELECT 'ALTER INDEX ' || relname || ' RENAME TO ' || relname || '_old;' || 'CREATE INDEX ' || relname || ' ON fulltext_new USING GIN('|| trim(trailing '_idx' from substring(relname from 10)) || ');' FROM (SELECT relname FROM pg_class WHERE oid IN
  (SELECT indexrelid FROM pg_index JOIN pg_class ON pg_class.oid = pg_index.indrelid
   WHERE pg_class.relname = 'fulltext' AND NOT indisunique AND NOT indisprimary) AND relname LIKE 'fulltext_fulltext%_idx') AS foo;
\o
-- Execute
\i /tmp/nxp-11897-rename-idx.sql

-- Rename fulltext table to fulltext_old
ALTER INDEX fulltext_pk RENAME TO fulltext_pk_old;
ALTER INDEX fulltext_jobid_idx RENAME TO fulltext_jobid_idx_old;
ALTER TABLE fulltext RENAME TO fulltext_old;

-- Switch table
ALTER TABLE fulltext_new RENAME TO fulltext;

-- Add constraints
ALTER TABLE ONLY fulltext
    ADD CONSTRAINT fulltext_pk PRIMARY KEY (id);
ALTER TABLE ONLY fulltext
    ADD CONSTRAINT fulltext_id_hierarchy_fk FOREIGN KEY (id) REFERENCES hierarchy(id) ON DELETE CASCADE;
CREATE INDEX fulltext_jobid_idx ON fulltext USING btree (jobid);