예시

CREATE TYPE temp.plan AS ENUM ('Gold', 'Silver', 'Bronze');

create table temp.writer_subscriber (
  writer_subscriber_id serial primary key,
  writer_id uuid references temp.writers on delete cascade,
  subscriber_id uuid references temp.writers on delete cascade,
  plan temp.plan not null default 'Bronze',
  end_date timestamptz default current_timestamp
) ;

create or replace function add_subscriber
(writer_name text, subscriber_name text, plan temp.plan)
returns void as $$
declare 
  writer_id_var uuid;
  subscriber_id_var uuid;
begin
  select id into writer_id_var from temp.writers 
    where name = writer_name limit 1;

  select id into subscriber_id_var from temp.writers 
    where name = subscriber_name limit 1;
  
  insert into temp.writer_subscriber 
    (writer_id, subscriber_id, plan) values
    (writer_id_var, subscriber_id_var, plan);
end;
$$ language plpgsql;

-- add
select add_subscriber('judy', 'james', 'Gold');

alter table temp.writer_subscriber
rename column end_date to start_date
-- rename의 경우에는 alter column이 아니네

++

대소문자를 구분하지 않는 ENUM을 생성하는 경우:

sqlCopy code
CREATE TYPE my_enum_type AS ENUM ('value1', 'value2', 'value3') COLLATE "C";

도메인

도메인은 데이터 타입과 체크 제약 조건을 결합한 사용자 지정 데이터 타입을 나타냅니다.

• !~ : 정규 표현식에서 "매치되지 않음"을 의미합니다.
• '\\s' : 정규 표현식에서 공백 문자를 의미합니다.

CREATE DOMAIN contact_name AS 
   VARCHAR NOT NULL CHECK (value !~ '\\s');
-------------------
CREATE TABLE mailing_list (
    id serial PRIMARY KEY,
    first_name contact_name,
    last_name contact_name,
    email VARCHAR NOT NULL
);

컴포짓 타입

CREATE TYPE statement allows you to create a composite type

which can be used as the return type of a function.