Algebraic Data Types in Postgres

问题:

Is it possible to create an Algebraic Data Type in Postgres and then use it as a column type?

For example:

CREATE TYPE hoofed AS ENUM('horse', 'goat');

CREATE TYPE monkey AS ENUM('chimp','macaque');

CREATE TYPE ANIMAL AS ENUM(hoofed, monkey);

This fails with:

syntax error at or near "hoofed"
LINE 1: CREATE TYPE ANIMAL AS ENUM(hoofed, monkey);

Is it possible to do something like this?

Ultimately what I would then like to be able to do is something like so:

CREATE TABLE zoo (
    a ANIMAL,
    name text
);

INSERT INTO zoo(a, name) VALUES('horse', 'bob');
INSERT INTO zoo(a, name) VALUES('macaque', 'jimmy');

And for both of the records to be independently valid.

EDIT: @Abihabi87's response below does allow me to create, in effect, a product type, but it still does not allow me to create a union type as desired.

回答1:

You cant create type enum from others enum type:

you can create ANIMAL that like:

CREATE TYPE ANIMAL AS (h hoofed,m monkey);

Example in use:

CREATE TABLE your_table
(
    a ANIMAL
);

INSERT INTO your_table(a) select (select ('horse','macaque')::ANIMAL);

回答2:

Use the function:

create or replace function create_enum(name, variadic regtype[])
returns void language plpgsql as $$
begin
    execute format(
        'create type %I as enum(%s)', 
        $1, 
        string_agg(quote_literal(enumlabel), ',' order by enumtypid, enumsortorder))
    from pg_enum
    where enumtypid = any($2);
end $$;

Pass the name of a new type and a list of enum types as arguments:

select create_enum('animal', 'hoofed', 'monkey');

select enum_range(null::animal) as animal;

           animal           
----------------------------
 {horse,goat,chimp,macaque}
(1 row)

回答3:

Effectively you are trying to merge two enum types.
There are some open questions:

• Can there be duplicate strings?
• Is the design supposed to be static (changes to enum type hoofed do not change type animal later) or dynamic (the opposite).
• Merge exactly two enum types or more?
• Since the order of elements is significant, what is the order of elements in animal supposed to be?
• Is this a one-time operation or intended for repeated use?

Assuming no duplicates, static design, two enum types, existing order of elements as appended and one-time operation.

You can use the built-in enum support function enum_range(anyenum) to get an array of all elements for a given enum type.

DO
$$
BEGIN
EXECUTE (
   SELECT 'CREATE TYPE animal AS ENUM (' 
        || array_to_string(enum_range(null::hoofed)::text[]
                        || enum_range(null::monkey)::text[], ''',''')
        || ''')'
   );
END
$$;

回答4:

With ENUM types, you cannot achieve dynamic type composition/union. However, with DOMAIN types, you could achieve something similar:

create function valid_any_domain(anyelement, variadic regtype[])
  returns boolean
  language plpgsql
  immutable
as $func$
declare
  t regtype;
begin
  foreach t in array $2 loop
    begin
      execute format('select $1::%s', t) using $1;
    exception
      when not_null_violation or check_violation then
        continue;
    end;
    return true;
  end loop;

  return false;
end;
$func$;

create domain hoofed as text
  check (value in ('horse', 'goat'));

create domain monkey as text
  check (value in ('chimp','macaque'));

create domain animal as text
  check (valid_any_domain(value, 'hoofed', 'monkey'));

Changing the base types will dynamically change the composite/union type too, but still requires a manual constraint validation (especially, when some value(s) are removed from the valid spectrum):

alter domain hoofed drop constraint hoofed_check;
alter domain hoofed add check (value in ('horse', 'goat', 'zebra'));
alter domain animal validate constraint animal_check;

http://rextester.com/MBVC62095

Note: however, with DOMAIN types, you will lose an ENUM property: the custom ordering. DOMAINs will always use the underlying type's ordering.