Intellect Board — cистема управления сайтом

Ррелевантность в числовом виде возвращает функция rank из tsearch2:
SELECT rank(fulltext_idx, to_tsquery('слово1 & слово2')) as rank
      FROM table1 WHERE fulltext_idx @@ to_tsquery('слово1 & слово2');
вернет в поле "rank" релевантность в числовом виде.

Список таблиц возвращается достаточно громоздким запросом, но если создать такую функцию:
CREATE OR REPLACE FUNCTION show_tables() RETURNS SETOF name AS $$
SELECT pt.relname
FROM(( pg_catalog.pg_class pt
     ))LEFT JOIN pg_catalog.pg_roles r ON r.oid = pt.relowner
     LEFT JOIN pg_catalog.pg_namespace pn ON pn.oid = pt.relnamespace
WHERE pt.relkind IN ('r','')
      AND pt.relname NOT LIKE ('pg_ts_%')
      AND pn.nspname NOT IN ('pg_catalog', 'pg_toast')
      AND pg_catalog.pg_table_is_visible(pt.oid)
ORDER BY 1
$$ IMMUTABLE LANGUAGE SQL;
то запрос:
SELECT show_tables()
выдаст список таблиц.
Строка AND pt.relname NOT LIKE ('pg_ts_%') в теле функции нужна, если установлен модуль tsearch2, чтоб не показывать его спец.таблицы.

Более-менее все сделал, только теперь оно ругается, что "tsearch locale not set". Где и как это задается?

Русская локаль для tsearch2 устанавливается сразу после коннекта с базой запросом:
SELECT set_curcfg('default_russian')
но здесь есть такие грабли: конфигурация russian_default в tsearch2 — в KOI8 (snowball, словарь russian_stem.c), и если кодировка базы тоже KOI8, все работает на ура. Как будет работать, если кодировка базы win1251, не знаю (скорее всего не будет) — я посредством некоторых плясок с бубном перевел tsearch2 в win1251. Делал это так:

1. В исходниках найти каталог snowball (у меня в FreeBSD было /usr/ports/databases/postgresql81-server/work/postgresql-8.1.3/contrib/tsearch2/snowball) и заменить в нем файл russian_stem.c прикрепленным файлом (тот же russian_stem.c, но преобразованный в win1251). После этого пересобрать tsearch2.

2. В каталоге, где лежат контиб-файлы (во Фре — /usr/local/share/postgresql/contrib) найти файл russian.stop и сковертировать его в win1251 (в *nix — через iconv, в win — любым доступным спосбом)
То же самое с russian.stop можно сделать в исходниках — при сборке он просто копируется в рабочий каталог contrib.

Когда-то эта функция писалась для другой цели, но сейчас, несолько доработанная, возможно пригодится для реализации бекапа:
CREATE OR REPLACE FUNCTION show_ddl(table_name name) RETURNS text AS $$
DECLARE ddl text;
  t_ddl record;
  t_key record;
  q_attr int;
  counter int;
  t_oid oid;
BEGIN  
  ddl := 'CREATE TABLE '||table_name||' (';
  t_oid := oid FROM( pg_class where relname = table_name;
  q_attr := count(*) from pg_attribute where attrelid = t_oid and attnum > 0;
  counter :=0;
  FOR t_ddl in
   SELECT pa.attname as atname,
          pa.attnotnull as notnull,
          pa.atthasdef as hasdef,
          format_type(pa.atttypid, pa.atttypmod) as typedefn,
          pad.adsrc as deflt
    FROM pg_attribute pa
       )LEFT JOIN pg_type pt ON pa.atttypid = pt.oid
       LEFT JOIN pg_attrdef pad on
            (pad.adrelid=pa.attrelid and pad.adnum=pa.attnum)
    WHERE pa.attrelid = t_oid
          AND  pa.attnum > 0
          ORDER BY attnum
  LOOP
   counter := counter + 1;
   ddl := ddl||'\n'||t_ddl.atname||' ';
   IF t_ddl.deflt like 'nextval(%' THEN ddl := ddl||' serial ';
   ELSE
     ddl :=  ddl||t_ddl.typedefn;
     IF t_ddl.notnull THEN ddl := ddl||' NOT NULL ';
     END IF;
     IF t_ddl.hasdef  THEN ddl := ddl||' DEFAULT '||t_ddl.deflt;
     END IF;
   END IF;
   IF counter < q_attr THEN ddl := ddl||',';
   END IF;
  END LOOP;
SELECT into t_ddl relname, oid FROM pg_class
   WHERE oid=(SELECT indexrelid
                 FROM pg_index i
                 WHERE i.indisprimary AND i.indrelid = t_oid);
IF FOUND THEN
ddl := ddl||',\nCONSTRAINT '||t_ddl.relname||' PRIMARY KEY '||'(';
q_attr := count(*) FROM pg_attribute WHERE attrelid = t_ddl.oid ;
counter :=0;
  FOR t_key in
    SELECT attname FROM pg_attribute
           WHERE attrelid = t_ddl.oid order by attnum
  LOOP
   counter := counter+1;
   ddl := ddl || t_key.attname ;
   IF counter < q_attr THEN ddl := ddl||', ';
   ELSE
   ddl := ddl||')\n';
   END IF;
END LOOP;
END IF;
ddl := ddl||')' ;
RETURN ddl;
END;
$$ language 'plpgsql';
SELECT show_ddl(имя_таблицы) вернет ddl таблицы.

Вот - теперь SELECT show_ddl('table_name') возвращает почти полный DDL. Этот код вставить между
ddl := ddl||');'; и RETURN ddl;
-- ******************* INDEXES ***************************
FOR t_ddl in
   SELECT  pg_catalog.pg_get_indexdef(i.indexrelid, 0, true) AS indx
       FROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index i
       WHERE c.oid = t_oid AND c.oid = i.indrelid AND i.indexrelid = c2.oid AND NOT i.indisprimary
       ORDER BY c2.relname
LOOP
  IF FOUND THEN
    ddl := ddl||'\n'||t_ddl.indx||';\n';
  END IF;
END LOOP;
-- ****************** TRIGGERS **************************
FOR t_ddl IN
   SELECT pg_catalog.pg_get_triggerdef(t.oid) AS trgs
       FROM pg_catalog.pg_trigger t
       WHERE t.tgrelid = t_oid
LOOP
  IF FOUND THEN
    ddl := ddl||'\n'||t_ddl.trgs||';\n';
  END IF;
END LOOP;

Запрос:SELECT show_ddl('ib_post') Возвращаемое значение:CREATE TABLE ib_post (
p_id  serial ,
p_tid integer NOT NULL ,
p_uid integer NOT NULL ,
p_text text NOT NULL ,
p__modcomment text,
p__time integer NOT NULL ,
p__edittime integer NOT NULL ,
p_signature integer NOT NULL ,
p__smiles integer NOT NULL ,
p__bcode integer NOT NULL ,
p__html integer NOT NULL ,
p_attach integer NOT NULL ,
p_uname character varying(32) NOT NULL ,
p__ip integer NOT NULL ,
p_title character varying(64) NOT NULL ,
p__premoderate integer NOT NULL ,
ftidx tsvector,
CONSTRAINT ib_post_pkey PRIMARY KEY (p_id)
);

CREATE INDEX id_ftid ON ib_post USING gist (ftidx);

CREATE INDEX tid ON ib_post USING btree (p_tid, p__premoderate);

CREATE INDEX "time" ON ib_post USING btree (p__time);

CREATE INDEX uid ON ib_post USING btree (p_uid);

CREATE TRIGGER tg_ftidx AFTER INSERT OR UPDATE ON ib_post FOR EACH ROW EXECUTE PROCEDURE tsearch2('idx_ftid', 'p_text', 'p_title');