检查用户定义的类型是否已存在于 PostgreSQL 中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7624919/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Check if a user-defined type already exists in PostgreSQL
提问by Larry
Say I have created some user-defined types in the DB,
假设我在数据库中创建了一些用户定义的类型，
i.e. CREATE TYPE abc ...
IE CREATE TYPE abc ...
Is it then possible to determine if the user-defined type exists or not? Perhaps, using any of the postgres information tables?
那么是否可以确定用户定义的类型是否存在？也许，使用任何 postgres 信息表？
The main reason for this is since PostgreSQL does not seem to support CREATE OR REPLACE TYPE ..., and if a certain type gets created more than once, I want to be able to drop the existing one first, then re-load the new one.
主要原因是因为 PostgreSQL 似乎不支持CREATE OR REPLACE TYPE ...，如果某种类型被创建多次，我希望能够先删除现有的，然后重新加载新的。
回答by bluish
I add here the complete solution for creating types in a simple script, without the need of creating a function just for this purpose.
我在这里添加了在简单脚本中创建类型的完整解决方案，而无需为此目的创建函数。
--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$$;
回答by rog
The simplest solution I've found so far that copes with schemas, inspired by @Cromax's answer, is this:
到目前为止，我发现的最简单的解决方案是在@Cromax 的回答的启发下处理模式：
DO $$ BEGIN
CREATE TYPE my_type AS (/* fields go here */);
EXCEPTION
WHEN duplicate_object THEN null;
END $$;
Just what you might expect really - we just wrap the CREATE TYPE statement in an exception handler so it doesn't abort the current transaction.
正如您所期望的那样 - 我们只是将 CREATE TYPE 语句包装在异常处理程序中，因此它不会中止当前事务。
回答by mu is too short
回答by Nulik
Indeed, Postgres does not have CREATE OR REPLACEfunctionality for types. So the best approach is to drop it:
实际上，Postgres 没有CREATE OR REPLACE类型功能。所以最好的方法是放弃它：
DROP TYPE IF EXISTS YOUR_TYPE;
CREATE TYPE YOUR_TYPE AS (
id integer,
field varchar
);
Simple solution is always the best one.
简单的解决方案总是最好的。
回答by Cromax
To solve @rog's dilemma to @bluish's answer it could be more appropriate to make use of regtypedata type. Consider this:
为了解决@rog 对@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 $$;
PERFORMclause is like SELECT, but it discards results, so basically we're checking if it is possible to cast 'my_schema.my_type'(or just 'my_type'if you don't care to be schema specific) to actual registered type. If the type does exist, then nothing "wrong" will happen and because of RETURNwhole block will end—no changes, since the type my_typeis already there. But if the cast is not possible, then there will be thrown error code 42704which has label of undefined_object. So in the next lines we try to catch that error and if that happens, we simply create our new data type.
PERFORM子句就像SELECT，但它会丢弃结果，所以基本上我们正在检查是否可以将'my_schema.my_type'（或者只是'my_type'如果您不关心特定于模式）转换为实际注册的类型。如果类型确实存在，那么不会发生任何“错误”并且因为RETURN整个块将结束——没有变化，因为类型my_type已经存在。但是如果无法进行转换，则会抛出42704标签为undefined_object. 因此，在接下来的几行中，我们尝试捕获该错误，如果发生这种情况，我们只需创建新的数据类型。
回答by Eloy Zuniga Jr.
-- 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();
-----------------------------------
回答by bsb
I'm trying to do the same thing, ensure a type exists.
我正在尝试做同样的事情，确保类型存在。
I started psql with the --echo-hidden(-E) option and entered \dT:
我用--echo-hidden( -E) 选项启动 psql并输入\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)
If you are using schemas and search_path (I am) then you'll probably need to keep the pg_catalog.pg_type_is_visible(t.oid)check. I don't know what all the conditions in the WHERE are doing, but they didn't seem relevant to my case. Currently using:
如果您正在使用模式和 search_path（我是），那么您可能需要保留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);
回答by Feki Zied
A more generic solution
更通用的解决方案
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;
and then you can call it like this:
然后你可以这样称呼它：
select create_type('lwm2m_instancetype', 'enum (''single'',''multiple'')');
select create_type('lwm2m_instancetype', 'enum (''single'',''multiple'')');
回答by Panchove
Another alternative
另一种选择
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);
回答by Julien
This plays well with schemas, and avoids exception handling:
这与模式配合得很好，并避免了异常处理：
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
$$;
