Как проверить, существует ли таблица в данной схеме

Question

База данных Postgres 8.4 и выше содержит общие таблицы в public Схема и таблицы компании в company схемы.
company имена схем всегда начинаются с 'company' и заканчивается номером компании.
Так что могут быть схемы:

public
company1
company2
company3
...
companynn

Приложение всегда работает с одной компанией.
search_path определяется соответственно в строке подключения odbc или npgsql, например:

search_path='company3,public'

Как проверить, существует ли данная таблица в указанном companyn схема?

select isSpecific('company3','tablenotincompany3schema')

должен вернуться false, а также

select isSpecific('company3','tableincompany3schema')

должен вернуться true,

В любом случае функция должна проверять только companyn Схема пройдена, а не другие схемы.

Если данная таблица существует в обоих public и передал схему, функция должна вернуть true,
Это должно работать для Postgres 8.4 или новее.

Answer 1

Это зависит от того, что именно вы хотите проверить.

Информационная схема?

Чтобы найти "существует ли таблица" (независимо от того, кто спрашивает), запросить информационную схему (information_schema.tables), строго говоря, неверно, потому что ( согласно документации):

Отображаются только те таблицы и представления, к которым у текущего пользователя есть доступ (будучи владельцем или обладающим некоторыми привилегиями).

Запрос, продемонстрированный @kong, может вернуть FALSE, но таблица все еще может существовать. Это отвечает на вопрос:

Как проверить, существует ли таблица (или представление) и имеет ли текущий пользователь к ней доступ?

SELECT EXISTS (
   SELECT 1
   FROM   information_schema.tables 
   WHERE  table_schema = 'schema_name'
   AND    table_name = 'table_name'
   );

Информационная схема в основном полезна для переносимости между основными версиями и разными СУБД. Но реализация медленная, потому что Postgres должен использовать сложные представления, чтобы соответствовать стандарту (information_schema.tables это довольно простой пример). А некоторая информация (например, OID) теряется при переводе из системных каталогов, которые фактически содержат всю информацию.

Системные каталоги

Ваш вопрос был:

Как проверить, существует ли таблица?

SELECT EXISTS (
   SELECT 1 
   FROM   pg_catalog.pg_class c
   JOIN   pg_catalog.pg_namespace n ON n.oid = c.relnamespace
   WHERE  n.nspname = 'schema_name'
   AND    c.relname = 'table_name'
   AND    c.relkind = 'r'    -- only tables
   );

Используйте системные каталоги pg_class а также pg_namespace напрямую, что также значительно быстрее. Тем не менее, согласно документации по pg_class:

Каталог pg_class каталоги таблиц и большинство всего остального, которое имеет столбцы или иным образом аналогично таблице. Это включает в себя индексы (но см. Также pg_index), последовательности, представления, материализованные представления, составные типы и таблицы TOAST;

Для этого конкретного вопроса вы также можете использовать системный вид pg_tables, Немного проще и более переносимо для основных версий Postgres (что вряд ли относится к этому основному запросу):

SELECT EXISTS (
   SELECT 1 
   FROM   pg_tables
   WHERE  schemaname = 'schema_name'
   AND    tablename = 'table_name'
   );

Идентификаторы должны быть уникальными среди всех объектов, упомянутых выше. Если вы хотите спросить:

Как проверить, берется ли имя для таблицы или подобного объекта в данной схеме?

SELECT EXISTS (
   SELECT 1 
   FROM   pg_catalog.pg_class c
   JOIN   pg_catalog.pg_namespace n ON n.oid = c.relnamespace
   WHERE  n.nspname = 'schema_name'
   AND    c.relname = 'table_name'
   );

• Соответствующий ответ на dba.SE, посвященный обсуждению "Информационная схема и системные каталоги"

Альтернатива: приведение к regclass

SELECT 'schema_name.table_name'::regclass

Это вызывает исключение, если таблица (необязательно дополненная схемой) (или другой объект, занимающий это имя) не существует.

Если вы не указали имя таблицы в схеме, приведение к regclass по умолчанию search_path и возвращает OID для первой найденной таблицы - или исключение, если таблица не входит ни в одну из перечисленных схем. Обратите внимание, что системные схемы pg_catalog а также pg_temp (схема для временных объектов текущего сеанса) автоматически являются частью search_path,

Вы можете использовать это и поймать возможное исключение в функции. Пример:

• Проверьте, существует ли последовательность в Postgres (plpgsql)

Такой запрос позволяет избежать возможных исключений и поэтому работает немного быстрее.

to_regclass(rel_name) в Postgres 9,4+

Теперь намного проще:

SELECT to_regclass('schema_name.table_name');

То же, что и актерский состав, но он возвращается...

... ноль, а не выдача ошибки, если имя не найдено

Answer 2

Возможно, используйте information_schema:

SELECT EXISTS(
    SELECT * 
    FROM information_schema.tables 
    WHERE 
      table_schema = 'company3' AND 
      table_name = 'tableincompany3schema'
);

Answer 3

С https://www.postgresql.org/docs/current/app-psql.html#APP-PSQL-PATTERNS

Например, \ dt foo *. bar отображает все таблицы, имя таблицы которых включает bar, которые находятся в схемах, имя схемы которых начинается с foo.

Answer 4

Для PostgreSQL 9.3 или меньше... Или кому нравится все нормализовано к тексту

Три варианта моей старой библиотеки SwissKnife: relname_exists(anyThing), relname_normalized(anyThing) а также relnamechecked_to_array(anyThing), Все проверяет из таблицы pg_catalog.pg_class и возвращает стандартные универсальные типы данных (логические, текстовые или текстовые[]).

/**
 * From my old SwissKnife Lib to your SwissKnife. License CC0.
 * Check and normalize to array the free-parameter relation-name.
 * Options: (name); (name,schema), ("schema.name"). Ignores schema2 in ("schema.name",schema2).
 */
CREATE FUNCTION relname_to_array(text,text default NULL) RETURNS text[] AS $f$
     SELECT array[n.nspname::text, c.relname::text]
     FROM   pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace,
            regexp_split_to_array($1,'\.') t(x) -- not work with quoted names
     WHERE  CASE
              WHEN COALESCE(x[2],'')>'' THEN n.nspname = x[1]      AND c.relname = x[2]
              WHEN $2 IS NULL THEN           n.nspname = 'public'  AND c.relname = $1
              ELSE                           n.nspname = $2        AND c.relname = $1
            END
$f$ language SQL IMMUTABLE;

CREATE FUNCTION relname_exists(text,text default NULL) RETURNS boolean AS $wrap$
  SELECT EXISTS (SELECT relname_to_array($1,$2))
$wrap$ language SQL IMMUTABLE;

CREATE FUNCTION relname_normalized(text,text default NULL,boolean DEFAULT true) RETURNS text AS $wrap$
  SELECT COALESCE(array_to_string(relname_to_array($1,$2), '.'), CASE WHEN $3 THEN '' ELSE NULL END)
$wrap$ language SQL IMMUTABLE;