postgreSQL에 테이블 만들기 (account, post, comment, image)

$$: PL/pgSQL 코드를 감싸는 따옴표로, 여러 줄의 PL/pgSQL 코드를 정의할 때 사용됩니다.
LANGUAGE plpgsql;: 이 함수가 PL/pgSQL 언어로 작성되었음을 명시합니다.

CREATE TABLE account (
    idx SERIAL PRIMARY KEY,
    id VARCHAR(20) NOT NULL UNIQUE,
    pw VARCHAR(20) NOT NULL,
    name VARCHAR(20) NOT NULL,
    phonenumber VARCHAR(15) NOT NULL UNIQUE,
    email VARCHAR(24) NOT NULL UNIQUE,
    is_admin BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE post (
    idx SERIAL PRIMARY KEY,
    account_idx INT NOT NULL,
    title VARCHAR(300) NOT NULL,
    content VARCHAR(3000) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    modified_at TIMESTAMP DEFAULT NULL,
    FOREIGN KEY (account_idx) REFERENCES account(idx)
);

CREATE TABLE comment (
    idx SERIAL PRIMARY KEY,
    post_idx INT NOT NULL,
    account_idx INT NOT NULL,
    content VARCHAR(600) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    modified_at TIMESTAMP DEFAULT NULL,
    FOREIGN KEY (post_idx) REFERENCES post(idx),
    FOREIGN KEY (account_idx) REFERENCES account(idx)
);

CREATE TABLE image (
    idx SERIAL PRIMARY KEY,
    post_idx INT NOT NULL,
    account_idx INT NOT NULL,
    path VARCHAR(100) NOT NULL,
    created_at TIMESTAMP NOT NULL,
    FOREIGN KEY (post_idx) REFERENCES post (idx),
    FOREIGN KEY (account_idx) REFERENCES account(idx)
);

CREATE OR REPLACE FUNCTION update_modified_timestamp()
RETURNS TRIGGER AS $$
BEGIN
    NEW.modified_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_post_modified_timestamp
BEFORE UPDATE ON post
FOR EACH ROW
EXECUTE FUNCTION update_modified_timestamp();

CREATE TRIGGER update_comment_modified_timestamp
BEFORE UPDATE ON comment
FOR EACH ROW
EXECUTE FUNCTION update_modified_timestamp();

CREATE OR REPLACE FUNCTION update_timestamp()
RETURNS TRIGGER AS $$
BEGIN
    NEW.created_at = CURRENT_TIMESTAMP;
    NEW.modified_at = NULL;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_post_timestamp
BEFORE INSERT ON post
FOR EACH ROW
EXECUTE FUNCTION update_timestamp();

CREATE TRIGGER update_comment_timestamp
BEFORE INSERT ON comment
FOR EACH ROW
EXECUTE FUNCTION update_timestamp();