Postgre에 사용자 정의 유형이 이미 있는지 확인합니다.SQL

Postgre에 사용자 정의 유형이 이미 있는지 확인합니다.SQL

예를 들어 DB에 사용자 정의 유형을 만들었습니다.

예.CREATE TYPE abc ...

그러면 사용자 정의 유형이 존재하는지 여부를 확인할 수 있습니까?아마도, 포스트그레스 정보 표를 사용하는 것이 있습니까?

그 주된 이유는 Postgre 이후입니다.SQL이 지원하지 않는 것 같습니다.CREATE OR REPLACE TYPE ...특정 유형이 두 번 이상 생성되면 기존 유형을 먼저 삭제한 다음 새 유형을 다시 로드할 수 있습니다.

이 목적을 위해 함수를 만들 필요 없이 간단한 스크립트로 유형을 생성할 수 있는 완벽한 솔루션을 여기에 추가합니다.

--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$$;

@Cromax의 대답에서 영감을 받아 스키마에 대처하는 가장 간단한 솔루션은 다음과 같습니다.

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

CREATE TYPE 문은 현재 트랜잭션을 중단하지 않도록 예외 처리기로 묶습니다.

실제로, Postgres는 다음을 가지고 있지 않습니다.CREATE OR REPLACE유형에 대한 기능입니다.따라서 가장 좋은 방법은 중단하는 것입니다.

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

간단한 해결책이 항상 최고입니다.

다음 표를 볼 수 있습니다.

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

그게 사실이라면abc존재한다.

@log의 딜레마를 @bluish의 대답으로 해결하려면 다음을 사용하는 것이 더 적절할 수 있습니다.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'스키마에 대해 자세히 설명할 필요가 없는 경우) 실제 등록된 유형으로 이동합니다.유형이 존재하는 경우 "잘못된" 일이 발생하지 않고 전체 블록이 종료됩니다. 유형이 변경되지 않기 때문입니다.my_type이미 있습니다.하지만 캐스팅이 불가능할 경우 코드 오류가 발생합니다.42704라벨이 있는undefined_object그래서 다음 줄에서 오류를 감지하고 오류가 발생하면 새로운 데이터 유형을 만듭니다.

-- 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();
-----------------------------------

@Cromax의 답변에서 영감을 받아 예외 조항의 추가 오버헤드를 피하면서도 유형의 존재에 대한 올바른 스키마를 확인하는 시스템 카탈로그 정보 기능을 사용하는 대안 솔루션이 있습니다.

DO $$ BEGIN
    IF to_regtype('my_schema.abc') IS NULL THEN
        CREATE TYPE my_schema.abc ... ;
    END IF;
END $$;

기본값의 경우public사용 중인 스키마는 다음과 같습니다.

DO $$ BEGIN
    IF to_regtype('abc') IS NULL THEN
        CREATE TYPE abc ... ;
    END IF;
END $$;

저도 같은 일을 하려고 합니다. 유형이 존재하는지 확인합니다.

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)

am에는 search_path(Im)를.pg_catalog.pg_type_is_visible(t.oid)확인해보세요. WHERE의 모든 조건이 무엇을 하고 있는지는 모르겠지만, 제 사건과 관련이 없어 보였습니다.현재 사용 중:

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

보다 일반적인 솔루션

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'')');

이것은 스키마와 잘 어울리며 예외 처리를 방지합니다.

DO $$
BEGIN
    IF NOT EXISTS (
      SELECT 1 FROM pg_type t
      LEFT JOIN pg_namespace p ON t.typnamespace=p.oid
      WHERE t.typname='my_type' AND p.nspname='my_schema'
    ) THEN
        CREATE TYPE my_schema.my_type AS (/* fields go here */);
    END IF;
END
$$;

다른 대안

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);

다음을 시도해야 합니다.

SELECT * from pg_enum WHERE enumlabel='WHAT YOU WANT';

블루스 코드로 계속 진행하세요, 우리는 또한 현재 스키마에 DB가 그러한 유형을 가지고 있는지 확인해야 합니다.db 스키마 중 하나에 동일한 형식이 있는 경우 현재 코드는 형식을 만들지 않기 때문입니다.따라서 완전한 범용 코드는 다음과 같습니다.

$$
    BEGIN
        IF NOT EXISTS(select 
                      from pg_type
                      WHERE typname = 'YOUR_ENUM_NAME'
                        AND typnamespace in
                            (SELECT oid FROM pg_catalog.pg_namespace where nspname = "current_schema"())) THEN
            CREATE TYPE YOUR_ENUM_NAME AS ENUM (....list of values ....);
        END IF;
    END
$$;```

언급URL : https://stackoverflow.com/questions/7624919/check-if-a-user-defined-type-already-exists-in-postgresql