Skip to content

Instantly share code, notes, and snippets.

@levlaz

levlaz/types_and_roles_demo.sql

Created March 28, 2016 19:11
Show Gist options
  • Save levlaz/0af3425c79f1c99a88da to your computer and use it in GitHub Desktop.
Save levlaz/0af3425c79f1c99a88da to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. levlaz created this gist Mar 28, 2016.
    27 changes: 27 additions & 0 deletions types_and_roles_demo.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,27 @@
    BEGIN;

    DO $$
    BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'task_status') THEN
    create type task_status AS ENUM ('todo', 'doing', 'blocked', 'done');
    END IF;
    END
    $$;
    CREATE TABLE IF NOT EXISTS
    tasks (
    id integer PRIMARY KEY,
    title varchar(200),
    status task_status NOT NULL DEFAULT 'todo',
    created_date timestamp
    );

    DO $$
    BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'reader') THEN
    CREATE ROLE reader;
    END IF;
    END
    $$;
    GRANT SELECT ON tasks TO reader;

    COMMIT;