-- check
CREATE TABLE t01_chk (
    product_no integer,
    name text,
    price numeric CHECK (price > 0)
);

-- unique
CREATE TABLE t02_uniq (
    product_no integer UNIQUE,
    name text,
    price numeric
);

-- primary key
CREATE TABLE t03_pk1 (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);

CREATE TABLE t03_pk2 (
    product_no integer UNIQUE NOT NULL,
    name text,
    price numeric
);

-- foreign key
CREATE TABLE t04_fk (
    order_id integer PRIMARY KEY,
    product_no integer REFERENCES t03_pk1 (product_no),
    quantity integer
);

-- exclude
CREATE TABLE t05_ex (
    c circle,
    EXCLUDE USING gist (c WITH &&)
);

-- trigger
CREATE OR REPLACE FUNCTION trigger_notice_ab() RETURNS trigger as $$
  begin
    raise notice 'trigger % on % % % for %: (a,b)=(%,%)',
        TG_NAME, TG_TABLE_NAME, TG_WHEN, TG_OP, TG_LEVEL,
        NEW.a, NEW.b;
    if TG_LEVEL = 'ROW' then
       return NEW;
    end if;
    return null;
  end;
  $$ language plpgsql;

CREATE TABLE t06_trg (a int, b int);

CREATE CONSTRAINT TRIGGER t06_con_trig AFTER insert ON t06_trg DEFERRABLE
    FOR each row EXECUTE PROCEDURE trigger_notice_ab();

-- domain
CREATE DOMAIN t07_posint AS integer CHECK (VALUE > 0);
CREATE TABLE t07_dm (id t07_posint);

-- not null
CREATE TABLE t08_notnull (
	a varchar NOT NULL
);

-- default
CREATE TABLE t09_default (
	a varchar DEFAULT 'a'
);

-- Tests
\dco
\dco t01_chk_price_check
\dco t02*
\dcoS pg_stat*

-- Visibility tests
---- visible
create schema con_s1;
create TABLE con_s1.t10_test (a int PRIMARY KEY);
set search_path to public, con_s1;
\dco

---- not visible
create role regress_constraint nosuperuser;
set role regress_constraint;
\dco
reset role;

-- clean-up
DROP TABLE t01_chk,
t02_uniq,
t03_pk1,
t03_pk2,
t04_fk,
t05_ex,
t06_trg,
t07_dm,
t08_notnull,
t09_default,
con_s1.t10_test;

drop domain t07_posint;
drop schema con_s1;
drop role regress_constraint;