Проверьте, существует ли уже определенный пользователем тип в PostgreSQL

Скажем, что я создал определенные пользовательские типы в БД,

то есть. CREATE TYPE abc ...

Можно ли тогда определить, существует ли определенный пользователем тип? Возможно, используя любую из информационных таблиц postgres?

Основная причина этого в том, что PostgreSQL, похоже, не поддерживает CREATE OR REPLACE TYPE ..., и если определенный тип создается несколько раз, я хочу сначала удалить существующий, а затем перезагрузить новый.

Ответ 1

Я добавляю здесь полное решение для создания типов в простой script, без необходимости создания функции только для этой цели.

--create types
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'my_type') THEN
        CREATE TYPE my_type AS
        (
            --my fields here...
        );
    END IF;
    --more types here...
END$$;

Ответ 2

Простейшее решение, которое я нашел на сегодняшний день, которое справляется со схемами, вдохновленными ответом @Cromax, заключается в следующем:

DO $$ BEGIN
    CREATE TYPE my_type AS (/* fields go here */);
EXCEPTION
    WHEN duplicate_object THEN null;
END $$;

Как раз то, что вы могли ожидать - мы просто обернули оператор CREATE TYPE в обработчик исключений, чтобы он не прерывал текущую транзакцию.

Ответ 3

Вы можете посмотреть в pg_type таблице:

select exists (select 1 from pg_type where typname = 'abc');

Если это правда, то abc существует.

Ответ 4

Действительно, Postgres не имеет функциональности CREATE OR REPLACE для типов. Поэтому лучше всего отказаться от него:

DROP TYPE IF EXISTS YOUR_TYPE;
CREATE TYPE YOUR_TYPE AS (
    id      integer,
    field   varchar
);

Простое решение всегда самое лучшее.

Ответ 5

Чтобы решить дилемму от @rog до ответа @bishish, было бы целесообразнее использовать тип данных regtype. Учтите это:

DO $$ BEGIN
    PERFORM 'my_schema.my_type'::regtype;
EXCEPTION
    WHEN undefined_object THEN
        CREATE TYPE my_schema.my_type AS (/* fields go here */);
END $$;

Предложение PERFORM похоже на SELECT, но оно отбрасывает результаты, поэтому в основном мы проверяем, возможно ли приведение 'my_schema.my_type' (или просто 'my_type', если вам не нужна конкретная схема) к фактическому зарегистрированному типу. Если тип существует, то ничего "неправильного" не произойдет, и из-за RETURN закончится весь блок - без изменений, так как тип my_type уже существует. Но если приведение невозможно, то будет выдан код ошибки 42704 с меткой undefined_object. Поэтому в следующих строках мы пытаемся уловить эту ошибку, и если это произойдет, мы просто создаем наш новый тип данных.

Ответ 6

-- All of this to create a type if it does not exist
CREATE OR REPLACE FUNCTION create_abc_type() RETURNS integer AS $$
DECLARE v_exists INTEGER;

BEGIN
    SELECT into v_exists (SELECT 1 FROM pg_type WHERE typname = 'abc');
    IF v_exists IS NULL THEN
        CREATE TYPE abc AS ENUM ('height', 'weight', 'distance');
    END IF;
    RETURN v_exists;
END;
$$ LANGUAGE plpgsql;

-- Call the function you just created
SELECT create_abc_type();

-- Remove the function you just created
DROP function create_abc_type();
-----------------------------------

Ответ 7

Я пытаюсь сделать то же самое, убедитесь, что существует тип.

Я начал psql с опцией --echo-hidden (-E) и ввел \dT:

$ psql -E
psql (9.1.9)
testdb=> \dT
********* QUERY **********
SELECT n.nspname as "Schema",
  pg_catalog.format_type(t.oid, NULL) AS "Name",
  pg_catalog.obj_description(t.oid, 'pg_type') as "Description"
FROM pg_catalog.pg_type t
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace
WHERE (t.typrelid = 0 OR (SELECT c.relkind = 'c' FROM pg_catalog.pg_class c WHERE c.oid = t.typrelid))
  AND NOT EXISTS(SELECT 1 FROM pg_catalog.pg_type el WHERE el.oid = t.typelem AND el.typarray = t.oid)
      AND n.nspname <> 'pg_catalog'
      AND n.nspname <> 'information_schema'
  AND pg_catalog.pg_type_is_visible(t.oid)
ORDER BY 1, 2;
**************************
 List of data types
 Schema |       Name       | Description 
--------+------------------+-------------
 public | errmsg_agg_state | 
(1 row)

Если вы используете схемы и search_path (я), вам, вероятно, потребуется сохранить проверку pg_catalog.pg_type_is_visible(t.oid). Я не знаю, что делают все условия в ГДЕ, но они, похоже, не имеют отношения к моему делу. В настоящее время используется:

SELECT 1 FROM pg_catalog.pg_type as t
   WHERE typname = 'mytype' AND pg_catalog.pg_type_is_visible(t.oid);

Ответ 8

Более общее решение

CREATE OR REPLACE FUNCTION create_type(name text, _type text) RETURNS 
integer AS $$
DECLARE v_exists INTEGER;

BEGIN
    SELECT into v_exists (SELECT 1 FROM pg_type WHERE typname = name);
    IF v_exists IS NULL THEN
            EXECUTE format('CREATE TYPE %I AS %s', name, _type);
    END IF;
    RETURN v_exists;
END;
$$ LANGUAGE plpgsql;

и тогда вы можете назвать это так:

select create_type('lwm2m_instancetype', 'enum (''single'',''multiple'')');

Ответ 9

Еще одна альтернатива

WITH namespace AS(
    SELECT oid 
        FROM pg_namespace 
        WHERE nspname = 'my_schema'
),
type_name AS (
    SELECT 1 type_exist  
        FROM pg_type 
        WHERE typname = 'my_type' AND typnamespace = (SELECT * FROM namespace)
)
SELECT EXISTS (SELECT * FROM type_name);