pgtap 0.90.0-1 / usr / share / postgresql / 9.1 / extension / pgtap-schema--0.90.0.sql

-- This file defines pgTAP Schema, a portable collection of schema-testing
-- functions for TAP-based unit testing on PostgreSQL 8.3 or higher. It is
-- distributed under the revised FreeBSD license. The home page for the pgTAP
-- project is:

--
-- http://pgtap.org/
--

-- Requires pgtap-core.sql
--
CREATE OR REPLACE FUNCTION _rexists ( CHAR, NAME, NAME )
RETURNS BOOLEAN AS $$
    SELECT EXISTS(
        SELECT true
          FROM pg_catalog.pg_namespace n
          JOIN pg_catalog.pg_class c ON n.oid = c.relnamespace
         WHERE c.relkind = $1
           AND n.nspname = $2
           AND c.relname = $3
    );
$$ LANGUAGE SQL;

CREATE OR REPLACE FUNCTION _rexists ( CHAR, NAME )
RETURNS BOOLEAN AS $$
    SELECT EXISTS(
        SELECT true
          FROM pg_catalog.pg_class c
         WHERE c.relkind = $1
           AND pg_catalog.pg_table_is_visible(c.oid)
           AND c.relname = $2
    );
$$ LANGUAGE SQL;

-- has_table( schema, table, description )
CREATE OR REPLACE FUNCTION has_table ( NAME, NAME, TEXT )
RETURNS TEXT AS $$
    SELECT ok( _rexists( 'r', $1, $2 ), $3 );
$$ LANGUAGE SQL;

-- has_table( table, description )
CREATE OR REPLACE FUNCTION has_table ( NAME, TEXT )
RETURNS TEXT AS $$
    SELECT ok( _rexists( 'r', $1 ), $2 );
$$ LANGUAGE SQL;

-- has_table( table )
CREATE OR REPLACE FUNCTION has_table ( NAME )
RETURNS TEXT AS $$
    SELECT has_table( $1, 'Table ' || quote_ident($1) || ' should exist' );
$$ LANGUAGE SQL;

-- hasnt_table( schema, table, description )
CREATE OR REPLACE FUNCTION hasnt_table ( NAME, NAME, TEXT )
RETURNS TEXT AS $$
    SELECT ok( NOT _rexists( 'r', $1, $2 ), $3 );
$$ LANGUAGE SQL;

-- hasnt_table( table, description )
CREATE OR REPLACE FUNCTION hasnt_table ( NAME, TEXT )
RETURNS TEXT AS $$
    SELECT ok( NOT _rexists( 'r', $1 ), $2 );
$$ LANGUAGE SQL;

-- hasnt_table( table )
CREATE OR REPLACE FUNCTION hasnt_table ( NAME )
RETURNS TEXT AS $$
    SELECT hasnt_table( $1, 'Table ' || quote_ident($1) || ' should not exist' );
$$ LANGUAGE SQL;

-- has_view( schema, view, description )
CREATE OR REPLACE FUNCTION has_view ( NAME, NAME, TEXT )
RETURNS TEXT AS $$
    SELECT ok( _rexists( 'v', $1, $2 ), $3 );
$$ LANGUAGE SQL;

-- has_view( view, description )
CREATE OR REPLACE FUNCTION has_view ( NAME, TEXT )
RETURNS TEXT AS $$
    SELECT ok( _rexists( 'v', $1 ), $2 );
$$ LANGUAGE SQL;

-- has_view( view )
CREATE OR REPLACE FUNCTION has_view ( NAME )
RETURNS TEXT AS $$
    SELECT has_view( $1, 'View ' || quote_ident($1) || ' should exist' );
$$ LANGUAGE SQL;

-- hasnt_view( schema, view, description )
CREATE OR REPLACE FUNCTION hasnt_view ( NAME, NAME, TEXT )
RETURNS TEXT AS $$
    SELECT ok( NOT _rexists( 'v', $1, $2 ), $3 );
$$ LANGUAGE SQL;

-- hasnt_view( view, description )
CREATE OR REPLACE FUNCTION hasnt_view ( NAME, TEXT )
RETURNS TEXT AS $$
    SELECT ok( NOT _rexists( 'v', $1 ), $2 );
$$ LANGUAGE SQL;

-- hasnt_view( view )
CREATE OR REPLACE FUNCTION hasnt_view ( NAME )
RETURNS TEXT AS $$
    SELECT hasnt_view( $1, 'View ' || quote_ident($1) || ' should not exist' );
$$ LANGUAGE SQL;

-- has_sequence( schema, sequence, description )
CREATE OR REPLACE FUNCTION has_sequence ( NAME, NAME, TEXT )
RETURNS TEXT AS $$
    SELECT ok( _rexists( 'S', $1, $2 ), $3 );
$$ LANGUAGE SQL;

-- has_sequence( sequence, description )
CREATE OR REPLACE FUNCTION has_sequence ( NAME, TEXT )
RETURNS TEXT AS $$
    SELECT ok( _rexists( 'S', $1 ), $2 );
$$ LANGUAGE SQL;

-- has_sequence( sequence )
CREATE OR REPLACE FUNCTION has_sequence ( NAME )
RETURNS TEXT AS $$
    SELECT has_sequence( $1, 'Sequence ' || quote_ident($1) || ' should exist' );
$$ LANGUAGE SQL;

-- hasnt_sequence( schema, sequence, description )
CREATE OR REPLACE FUNCTION hasnt_sequence ( NAME, NAME, TEXT )
RETURNS TEXT AS $$
    SELECT ok( NOT _rexists( 'S', $1, $2 ), $3 );
$$ LANGUAGE SQL;

-- hasnt_sequence( sequence, description )
CREATE OR REPLACE FUNCTION hasnt_sequence ( NAME, TEXT )
RETURNS TEXT AS $$
    SELECT ok( NOT _rexists( 'S', $1 ), $2 );
$$ LANGUAGE SQL;

-- hasnt_sequence( sequence )
CREATE OR REPLACE FUNCTION hasnt_sequence ( NAME )
RETURNS TEXT AS $$
    SELECT hasnt_sequence( $1, 'Sequence ' || quote_ident($1) || ' should not exist' );
$$ LANGUAGE SQL;

CREATE OR REPLACE FUNCTION _cexists ( NAME, NAME, NAME )
RETURNS BOOLEAN AS $$
    SELECT EXISTS(
        SELECT true
          FROM pg_catalog.pg_namespace n
          JOIN pg_catalog.pg_class c ON n.oid = c.relnamespace
          JOIN pg_catalog.pg_attribute a ON c.oid = a.attrelid
         WHERE n.nspname = $1
           AND c.relname = $2
           AND a.attnum > 0
           AND NOT a.attisdropped
           AND a.attname = $3
    );
$$ LANGUAGE SQL;

CREATE OR REPLACE FUNCTION _cexists ( NAME, NAME )
RETURNS BOOLEAN AS $$
    SELECT EXISTS(
        SELECT true
          FROM pg_catalog.pg_class c
          JOIN pg_catalog.pg_attribute a ON c.oid = a.attrelid
         WHERE c.relname = $1
           AND pg_catalog.pg_table_is_visible(c.oid)
           AND a.attnum > 0
           AND NOT a.attisdropped
           AND a.attname = $2
    );
$$ LANGUAGE SQL;

-- has_column( schema, table, column, description )
CREATE OR REPLACE FUNCTION has_column ( NAME, NAME, NAME, TEXT )
RETURNS TEXT AS $$
    SELECT ok( _cexists( $1, $2, $3 ), $4 );
$$ LANGUAGE SQL;

-- has_column( table, column, description )
CREATE OR REPLACE FUNCTION has_column ( NAME, NAME, TEXT )
RETURNS TEXT AS $$
    SELECT ok( _cexists( $1, $2 ), $3 );
$$ LANGUAGE SQL;

-- has_column( table, column )
CREATE OR REPLACE FUNCTION has_column ( NAME, NAME )
RETURNS TEXT AS $$
    SELECT has_column( $1, $2, 'Column ' || quote_ident($1) || '.' || quote_ident($2) || ' should exist' );
$$ LANGUAGE SQL;

-- hasnt_column( schema, table, column, description )
CREATE OR REPLACE FUNCTION hasnt_column ( NAME, NAME, NAME, TEXT )
RETURNS TEXT AS $$
    SELECT ok( NOT _cexists( $1, $2, $3 ), $4 );
$$ LANGUAGE SQL;

-- hasnt_column( table, column, description )
CREATE OR REPLACE FUNCTION hasnt_column ( NAME, NAME, TEXT )
RETURNS TEXT AS $$
    SELECT ok( NOT _cexists( $1, $2 ), $3 );
$$ LANGUAGE SQL;

-- hasnt_column( table, column )
CREATE OR REPLACE FUNCTION hasnt_column ( NAME, NAME )
RETURNS TEXT AS $$
    SELECT hasnt_column( $1, $2, 'Column ' || quote_ident($1) || '.' || quote_ident($2) || ' should not exist' );
$$ LANGUAGE SQL;

-- _col_is_null( schema, table, column, desc, null )
CREATE OR REPLACE FUNCTION _col_is_null ( NAME, NAME, NAME, TEXT, bool )
RETURNS TEXT AS $$
BEGIN
    IF NOT _cexists( $1, $2, $3 ) THEN
        RETURN fail( $4 ) || E'\n'
            || diag ('    Column ' || quote_ident($1) || '.' || quote_ident($2) || '.' || quote_ident($3) || ' does not exist' );
    END IF;
    RETURN ok(
        EXISTS(
            SELECT true
              FROM pg_catalog.pg_namespace n
              JOIN pg_catalog.pg_class c ON n.oid = c.relnamespace
              JOIN pg_catalog.pg_attribute a ON c.oid = a.attrelid
             WHERE n.nspname = $1
               AND c.relname = $2
               AND a.attnum  > 0
               AND NOT a.attisdropped
               AND a.attname    = $3
               AND a.attnotnull = $5
        ), $4
    );
END;
$$ LANGUAGE plpgsql;

-- _col_is_null( table, column, desc, null )
CREATE OR REPLACE FUNCTION _col_is_null ( NAME, NAME, TEXT, bool )
RETURNS TEXT AS $$
BEGIN
    IF NOT _cexists( $1, $2 ) THEN
        RETURN fail( $3 ) || E'\n'
            || diag ('    Column ' || quote_ident($1) || '.' || quote_ident($2) || ' does not exist' );
    END IF;
    RETURN ok(
        EXISTS(
            SELECT true
              FROM pg_catalog.pg_class c
              JOIN pg_catalog.pg_attribute a ON c.oid = a.attrelid
             WHERE pg_catalog.pg_table_is_visible(c.oid)
               AND c.relname = $1
               AND a.attnum > 0
               AND NOT a.attisdropped
               AND a.attname    = $2
               AND a.attnotnull = $4
        ), $3
    );
END;
$$ LANGUAGE plpgsql;

-- col_not_null( schema, table, column, description )
CREATE OR REPLACE FUNCTION col_not_null ( NAME, NAME, NAME, TEXT )
RETURNS TEXT AS $$
    SELECT _col_is_null( $1, $2, $3, $4, true );
$$ LANGUAGE SQL;

-- col_not_null( table, column, description )
CREATE OR REPLACE FUNCTION col_not_null ( NAME, NAME, TEXT )
RETURNS TEXT AS $$
    SELECT _col_is_null( $1, $2, $3, true );
$$ LANGUAGE SQL;

-- col_not_null( table, column )
CREATE OR REPLACE FUNCTION col_not_null ( NAME, NAME )
RETURNS TEXT AS $$
    SELECT _col_is_null( $1, $2, 'Column ' || quote_ident($1) || '.' || quote_ident($2) || ' should be NOT NULL', true );
$$ LANGUAGE SQL;

-- col_is_null( schema, table, column, description )
CREATE OR REPLACE FUNCTION col_is_null ( NAME, NAME, NAME, TEXT )
RETURNS TEXT AS $$
    SELECT _col_is_null( $1, $2, $3, $4, false );
$$ LANGUAGE SQL;

-- col_is_null( schema, table, column )
CREATE OR REPLACE FUNCTION col_is_null ( NAME, NAME, NAME )
RETURNS TEXT AS $$
    SELECT _col_is_null( $1, $2, $3, false );
$$ LANGUAGE SQL;

-- col_is_null( table, column )
CREATE OR REPLACE FUNCTION col_is_null ( NAME, NAME )
RETURNS TEXT AS $$
    SELECT _col_is_null( $1, $2, 'Column ' || quote_ident($1) || '.' || quote_ident($2) || ' should allow NULL', false );
$$ LANGUAGE SQL;

CREATE OR REPLACE FUNCTION _get_col_type ( NAME, NAME, NAME )
RETURNS TEXT AS $$
    SELECT display_type(a.atttypid, a.atttypmod)
      FROM pg_catalog.pg_namespace n
      JOIN pg_catalog.pg_class c     ON n.oid = c.relnamespace
      JOIN pg_catalog.pg_attribute a ON c.oid = a.attrelid
     WHERE n.nspname = $1
       AND c.relname = $2
       AND a.attname = $3
       AND attnum    > 0
       AND NOT a.attisdropped
$$ LANGUAGE SQL;

CREATE OR REPLACE FUNCTION _get_col_type ( NAME, NAME )
RETURNS TEXT AS $$
    SELECT display_type(a.atttypid, a.atttypmod)
      FROM pg_catalog.pg_attribute a
      JOIN pg_catalog.pg_class c ON  a.attrelid = c.oid
     WHERE pg_table_is_visible(c.oid)
       AND c.relname = $1
       AND a.attname = $2
       AND attnum    > 0
       AND NOT a.attisdropped
       AND pg_type_is_visible(a.atttypid)
$$ LANGUAGE SQL;

CREATE OR REPLACE FUNCTION _get_col_ns_type ( NAME, NAME, NAME )
RETURNS TEXT AS $$
    SELECT display_type(tn.nspname, a.atttypid, a.atttypmod)
      FROM pg_catalog.pg_namespace n
      JOIN pg_catalog.pg_class c      ON n.oid = c.relnamespace
      JOIN pg_catalog.pg_attribute a  ON c.oid = a.attrelid
      JOIN pg_catalog.pg_type t       ON a.atttypid = t.oid
      JOIN pg_catalog.pg_namespace tn ON t.typnamespace = tn.oid
     WHERE n.nspname = $1
       AND c.relname = $2
       AND a.attname = $3
       AND attnum    > 0
       AND NOT a.attisdropped
$$ LANGUAGE SQL;

CREATE OR REPLACE FUNCTION _quote_ident_like(TEXT, TEXT)
RETURNS TEXT AS $$
DECLARE
    have    TEXT;
    pcision TEXT;
BEGIN
    -- Just return it if rhs isn't quoted.
    IF $2 !~ '"' THEN RETURN $1; END IF;

    -- If it's quoted ident without precision, return it quoted.
    IF $2 ~ '"$' THEN RETURN quote_ident($1); END IF;

    pcision := substring($1 FROM '[(][^")]+[)]$');

    -- Just quote it if thre is no precision.
    if pcision IS NULL THEN RETURN quote_ident($1); END IF;

    -- Quote the non-precision part and concatenate with precision.
    RETURN quote_ident(substring($1 FOR char_length($1) - char_length(pcision)))
        || pcision;
END;
$$ LANGUAGE plpgsql;

-- col_type_is( schema, table, column, schema, type, description )
CREATE OR REPLACE FUNCTION col_type_is ( NAME, NAME, NAME, NAME, TEXT, TEXT )
RETURNS TEXT AS $$
DECLARE
    have_type TEXT := _get_col_ns_type($1, $2, $3);
    want_type TEXT;
BEGIN
    IF have_type IS NULL THEN
        RETURN fail( $6 ) || E'\n' || diag (
            '   Column ' || COALESCE(quote_ident($1) || '.', '')
            || quote_ident($2) || '.' || quote_ident($3) || ' does not exist'
        );
    END IF;

    want_type := quote_ident($4) || '.' || _quote_ident_like($5, have_type);
    IF have_type = want_type THEN
        -- We're good to go.
        RETURN ok( true, $6 );
    END IF;

    -- Wrong data type. tell 'em what we really got.
    RETURN ok( false, $6 ) || E'\n' || diag(
           '        have: ' || have_type ||
        E'\n        want: ' || want_type
    );
END;
$$ LANGUAGE plpgsql;

-- col_type_is( schema, table, column, schema, type )
CREATE OR REPLACE FUNCTION col_type_is ( NAME, NAME, NAME, NAME, TEXT )
RETURNS TEXT AS $$
    SELECT col_type_is( $1, $2, $3, $4, $5, 'Column ' || quote_ident($1) || '.' || quote_ident($2)
        || '.' || quote_ident($3) || ' should be type ' || quote_ident($4) || '.' || $5);
$$ LANGUAGE SQL;

-- col_type_is( schema, table, column, type, description )
CREATE OR REPLACE FUNCTION col_type_is ( NAME, NAME, NAME, TEXT, TEXT )
RETURNS TEXT AS $$
DECLARE
    have_type TEXT;
    want_type TEXT;
BEGIN
    -- Get the data type.
    IF $1 IS NULL THEN
        have_type := _get_col_type($2, $3);
    ELSE
        have_type := _get_col_type($1, $2, $3);
    END IF;

    IF have_type IS NULL THEN
        RETURN fail( $5 ) || E'\n' || diag (
            '   Column ' || COALESCE(quote_ident($1) || '.', '')
            || quote_ident($2) || '.' || quote_ident($3) || ' does not exist'
        );
    END IF;

    want_type := _quote_ident_like($4, have_type);
    IF have_type = want_type THEN
        -- We're good to go.
        RETURN ok( true, $5 );
    END IF;

    -- Wrong data type. tell 'em what we really got.
    RETURN ok( false, $5 ) || E'\n' || diag(
           '        have: ' || have_type ||
        E'\n        want: ' || want_type
    );
END;
$$ LANGUAGE plpgsql;

-- col_type_is( schema, table, column, type )
CREATE OR REPLACE FUNCTION col_type_is ( NAME, NAME, NAME, TEXT )
RETURNS TEXT AS $$
    SELECT col_type_is( $1, $2, $3, $4, 'Column ' || quote_ident($1) || '.' || quote_ident($2) || '.' || quote_ident($3) || ' should be type ' || $4 );
$$ LANGUAGE SQL;

-- col_type_is( table, column, type, description )
CREATE OR REPLACE FUNCTION col_type_is ( NAME, NAME, TEXT, TEXT )
RETURNS TEXT AS $$
    SELECT col_type_is( NULL, $1, $2, $3, $4 );
$$ LANGUAGE SQL;

-- col_type_is( table, column, type )
CREATE OR REPLACE FUNCTION col_type_is ( NAME, NAME, TEXT )
RETURNS TEXT AS $$
    SELECT col_type_is( $1, $2, $3, 'Column ' || quote_ident($1) || '.' || quote_ident($2) || ' should be type ' || $3 );
$$ LANGUAGE SQL;

CREATE OR REPLACE FUNCTION _has_def ( NAME, NAME, NAME )
RETURNS boolean AS $$
    SELECT a.atthasdef
      FROM pg_catalog.pg_namespace n
      JOIN pg_catalog.pg_class c ON n.oid = c.relnamespace
      JOIN pg_catalog.pg_attribute a ON c.oid = a.attrelid
     WHERE n.nspname = $1
       AND c.relname = $2
       AND a.attnum > 0
       AND NOT a.attisdropped
       AND a.attname = $3
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION _has_def ( NAME, NAME )
RETURNS boolean AS $$
    SELECT a.atthasdef
      FROM pg_catalog.pg_class c
      JOIN pg_catalog.pg_attribute a ON c.oid = a.attrelid
     WHERE c.relname = $1
       AND a.attnum > 0
       AND NOT a.attisdropped
       AND a.attname = $2
$$ LANGUAGE sql;

-- col_has_default( schema, table, column, description )
CREATE OR REPLACE FUNCTION col_has_default ( NAME, NAME, NAME, TEXT )
RETURNS TEXT AS $$
BEGIN
    IF NOT _cexists( $1, $2, $3 ) THEN
        RETURN fail( $4 ) || E'\n'
            || diag ('    Column ' || quote_ident($1) || '.' || quote_ident($2) || '.' || quote_ident($3) || ' does not exist' );
    END IF;
    RETURN ok( _has_def( $1, $2, $3 ), $4 );
END
$$ LANGUAGE plpgsql;

-- col_has_default( table, column, description )
CREATE OR REPLACE FUNCTION col_has_default ( NAME, NAME, TEXT )
RETURNS TEXT AS $$
BEGIN
    IF NOT _cexists( $1, $2 ) THEN
        RETURN fail( $3 ) || E'\n'
            || diag ('    Column ' || quote_ident($1) || '.' || quote_ident($2) || ' does not exist' );
    END IF;
    RETURN ok( _has_def( $1, $2 ), $3 );
END;
$$ LANGUAGE plpgsql;

-- col_has_default( table, column )
CREATE OR REPLACE FUNCTION col_has_default ( NAME, NAME )
RETURNS TEXT AS $$
    SELECT col_has_default( $1, $2, 'Column ' || quote_ident($1) || '.' || quote_ident($2) || ' should have a default' );
$$ LANGUAGE SQL;

-- col_hasnt_default( schema, table, column, description )
CREATE OR REPLACE FUNCTION col_hasnt_default ( NAME, NAME, NAME, TEXT )
RETURNS TEXT AS $$
BEGIN
    IF NOT _cexists( $1, $2, $3 ) THEN
        RETURN fail( $4 ) || E'\n'
            || diag ('    Column ' || quote_ident($1) || '.' || quote_ident($2) || '.' || quote_ident($3) || ' does not exist' );
    END IF;
    RETURN ok( NOT _has_def( $1, $2, $3 ), $4 );
END;
$$ LANGUAGE plpgsql;

-- col_hasnt_default( table, column, description )
CREATE OR REPLACE FUNCTION col_hasnt_default ( NAME, NAME, TEXT )
RETURNS TEXT AS $$
BEGIN
    IF NOT _cexists( $1, $2 ) THEN
        RETURN fail( $3 ) || E'\n'
            || diag ('    Column ' || quote_ident($1) || '.' || quote_ident($2) || ' does not exist' );
    END IF;
    RETURN ok( NOT _has_def( $1, $2 ), $3 );
END;
$$ LANGUAGE plpgsql;

-- col_hasnt_default( table, column )
CREATE OR REPLACE FUNCTION col_hasnt_default ( NAME, NAME )
RETURNS TEXT AS $$
    SELECT col_hasnt_default( $1, $2, 'Column ' || quote_ident($1) || '.' || quote_ident($2) || ' should not have a default' );
$$ LANGUAGE SQL;

CREATE OR REPLACE FUNCTION _def_is( TEXT, TEXT, anyelement, TEXT )
RETURNS TEXT AS $$
DECLARE
    thing text;
BEGIN
    IF $1 ~ '^[^'']+[(]' THEN
        -- It's a functional default.
        RETURN is( $1, $3, $4 );
    END IF;

    EXECUTE 'SELECT is('
             || COALESCE($1, 'NULL' || '::' || $2) || '::' || $2 || ', '
             || COALESCE(quote_literal($3), 'NULL') || '::' || $2 || ', '
             || COALESCE(quote_literal($4), 'NULL')
    || ')' INTO thing;
    RETURN thing;
END;
$$ LANGUAGE plpgsql;

-- _cdi( schema, table, column, default, description )
CREATE OR REPLACE FUNCTION _cdi ( NAME, NAME, NAME, anyelement, TEXT )
RETURNS TEXT AS $$
BEGIN
    IF NOT _cexists( $1, $2, $3 ) THEN
        RETURN fail( $5 ) || E'\n'
            || diag ('    Column ' || quote_ident($1) || '.' || quote_ident($2) || '.' || quote_ident($3) || ' does not exist' );
    END IF;

    IF NOT _has_def( $1, $2, $3 ) THEN
        RETURN fail( $5 ) || E'\n'
            || diag ('    Column ' || quote_ident($1) || '.' || quote_ident($2) || '.' || quote_ident($3) || ' has no default' );
    END IF;

    RETURN _def_is(
        pg_catalog.pg_get_expr(d.adbin, d.adrelid),
        display_type(a.atttypid, a.atttypmod),
        $4, $5
    )
      FROM pg_catalog.pg_namespace n, pg_catalog.pg_class c, pg_catalog.pg_attribute a,
           pg_catalog.pg_attrdef d
     WHERE n.oid = c.relnamespace
       AND c.oid = a.attrelid
       AND a.atthasdef
       AND a.attrelid = d.adrelid
       AND a.attnum = d.adnum
       AND n.nspname = $1
       AND c.relname = $2
       AND a.attnum > 0
       AND NOT a.attisdropped
       AND a.attname = $3;
END;
$$ LANGUAGE plpgsql;

-- _cdi( table, column, default, description )
CREATE OR REPLACE FUNCTION _cdi ( NAME, NAME, anyelement, TEXT )
RETURNS TEXT AS $$
BEGIN
    IF NOT _cexists( $1, $2 ) THEN
        RETURN fail( $4 ) || E'\n'
            || diag ('    Column ' || quote_ident($1) || '.' || quote_ident($2) || ' does not exist' );
    END IF;

    IF NOT _has_def( $1, $2 ) THEN
        RETURN fail( $4 ) || E'\n'
            || diag ('    Column ' || quote_ident($1) || '.' || quote_ident($2) || ' has no default' );
    END IF;

    RETURN _def_is(
        pg_catalog.pg_get_expr(d.adbin, d.adrelid),
        display_type(a.atttypid, a.atttypmod),
        $3, $4
    )
      FROM pg_catalog.pg_class c, pg_catalog.pg_attribute a, pg_catalog.pg_attrdef d
     WHERE c.oid = a.attrelid
       AND pg_table_is_visible(c.oid)
       AND a.atthasdef
       AND a.attrelid = d.adrelid
       AND a.attnum = d.adnum
       AND c.relname = $1
       AND a.attnum > 0
       AND NOT a.attisdropped
       AND a.attname = $2;
END;
$$ LANGUAGE plpgsql;

-- _cdi( table, column, default )
CREATE OR REPLACE FUNCTION _cdi ( NAME, NAME, anyelement )
RETURNS TEXT AS $$
    SELECT col_default_is(
        $1, $2, $3,
        'Column ' || quote_ident($1) || '.' || quote_ident($2) || ' should default to '
        || COALESCE( quote_literal($3), 'NULL')
    );
$$ LANGUAGE sql;

-- col_default_is( schema, table, column, default, description )
CREATE OR REPLACE FUNCTION col_default_is ( NAME, NAME, NAME, anyelement, TEXT )
RETURNS TEXT AS $$
    SELECT _cdi( $1, $2, $3, $4, $5 );
$$ LANGUAGE sql;

-- col_default_is( schema, table, column, default, description )
CREATE OR REPLACE FUNCTION col_default_is ( NAME, NAME, NAME, TEXT, TEXT )
RETURNS TEXT AS $$
    SELECT _cdi( $1, $2, $3, $4, $5 );
$$ LANGUAGE sql;

-- col_default_is( table, column, default, description )
CREATE OR REPLACE FUNCTION col_default_is ( NAME, NAME, anyelement, TEXT )
RETURNS TEXT AS $$
    SELECT _cdi( $1, $2, $3, $4 );
$$ LANGUAGE sql;

-- col_default_is( table, column, default, description )
CREATE OR REPLACE FUNCTION col_default_is ( NAME, NAME, TEXT, TEXT )
RETURNS TEXT AS $$
    SELECT _cdi( $1, $2, $3, $4 );
$$ LANGUAGE sql;

-- col_default_is( table, column, default )
CREATE OR REPLACE FUNCTION col_default_is ( NAME, NAME, anyelement )
RETURNS TEXT AS $$
    SELECT _cdi( $1, $2, $3 );
$$ LANGUAGE sql;

-- col_default_is( table, column, default::text )
CREATE OR REPLACE FUNCTION col_default_is ( NAME, NAME, text )
RETURNS TEXT AS $$
    SELECT _cdi( $1, $2, $3 );
$$ LANGUAGE sql;

-- _hasc( schema, table, constraint_type )
CREATE OR REPLACE FUNCTION _hasc ( NAME, NAME, CHAR )
RETURNS BOOLEAN AS $$
    SELECT EXISTS(
            SELECT true
              FROM pg_catalog.pg_namespace n
              JOIN pg_catalog.pg_class c      ON c.relnamespace = n.oid
              JOIN pg_catalog.pg_constraint x ON c.oid = x.conrelid
             WHERE c.relhaspkey = true
               AND n.nspname = $1
               AND c.relname = $2
               AND x.contype = $3
    );
$$ LANGUAGE sql;

-- _hasc( table, constraint_type )
CREATE OR REPLACE FUNCTION _hasc ( NAME, CHAR )
RETURNS BOOLEAN AS $$
    SELECT EXISTS(
            SELECT true
              FROM pg_catalog.pg_class c
              JOIN pg_catalog.pg_constraint x ON c.oid = x.conrelid
             WHERE c.relhaspkey = true
               AND pg_table_is_visible(c.oid)
               AND c.relname = $1
               AND x.contype = $2
    );
$$ LANGUAGE sql;

-- has_pk( schema, table, description )
CREATE OR REPLACE FUNCTION has_pk ( NAME, NAME, TEXT )
RETURNS TEXT AS $$
    SELECT ok( _hasc( $1, $2, 'p' ), $3 );
$$ LANGUAGE sql;

-- has_pk( table, description )
CREATE OR REPLACE FUNCTION has_pk ( NAME, TEXT )
RETURNS TEXT AS $$
    SELECT ok( _hasc( $1, 'p' ), $2 );
$$ LANGUAGE sql;

-- has_pk( table )
CREATE OR REPLACE FUNCTION has_pk ( NAME )
RETURNS TEXT AS $$
    SELECT has_pk( $1, 'Table ' || quote_ident($1) || ' should have a primary key' );
$$ LANGUAGE sql;

-- hasnt_pk( schema, table, description )
CREATE OR REPLACE FUNCTION hasnt_pk ( NAME, NAME, TEXT )
RETURNS TEXT AS $$
    SELECT ok( NOT _hasc( $1, $2, 'p' ), $3 );
$$ LANGUAGE sql;

-- hasnt_pk( table, description )
CREATE OR REPLACE FUNCTION hasnt_pk ( NAME, TEXT )
RETURNS TEXT AS $$
    SELECT ok( NOT _hasc( $1, 'p' ), $2 );
$$ LANGUAGE sql;

-- hasnt_pk( table )
CREATE OR REPLACE FUNCTION hasnt_pk ( NAME )
RETURNS TEXT AS $$
    SELECT hasnt_pk( $1, 'Table ' || quote_ident($1) || ' should not have a primary key' );
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION _pg_sv_column_array( OID, SMALLINT[] )
RETURNS NAME[] AS $$
    SELECT ARRAY(
        SELECT a.attname
          FROM pg_catalog.pg_attribute a
          JOIN generate_series(1, array_upper($2, 1)) s(i) ON a.attnum = $2[i]
         WHERE attrelid = $1
         ORDER BY i
    )
$$ LANGUAGE SQL stable;

-- Borrowed from newsysviews: http://pgfoundry.org/projects/newsysviews/
CREATE OR REPLACE FUNCTION _pg_sv_table_accessible( OID, OID )
RETURNS BOOLEAN AS $$
    SELECT CASE WHEN has_schema_privilege($1, 'USAGE') THEN (
                  has_table_privilege($2, 'SELECT')
               OR has_table_privilege($2, 'INSERT')
               or has_table_privilege($2, 'UPDATE')
               OR has_table_privilege($2, 'DELETE')
               OR has_table_privilege($2, 'RULE')
               OR has_table_privilege($2, 'REFERENCES')
               OR has_table_privilege($2, 'TRIGGER')
           ) ELSE FALSE
    END;
$$ LANGUAGE SQL immutable strict;

-- Borrowed from newsysviews: http://pgfoundry.org/projects/newsysviews/
CREATE OR REPLACE VIEW pg_all_foreign_keys
AS
  SELECT n1.nspname                                   AS fk_schema_name,
         c1.relname                                   AS fk_table_name,
         k1.conname                                   AS fk_constraint_name,
         c1.oid                                       AS fk_table_oid,
         _pg_sv_column_array(k1.conrelid,k1.conkey)   AS fk_columns,
         n2.nspname                                   AS pk_schema_name,
         c2.relname                                   AS pk_table_name,
         k2.conname                                   AS pk_constraint_name,
         c2.oid                                       AS pk_table_oid,
         ci.relname                                   AS pk_index_name,
         _pg_sv_column_array(k1.confrelid,k1.confkey) AS pk_columns,
         CASE k1.confmatchtype WHEN 'f' THEN 'FULL'
                               WHEN 'p' THEN 'PARTIAL'
                               WHEN 'u' THEN 'NONE'
                               else null
         END AS match_type,
         CASE k1.confdeltype WHEN 'a' THEN 'NO ACTION'
                             WHEN 'c' THEN 'CASCADE'
                             WHEN 'd' THEN 'SET DEFAULT'
                             WHEN 'n' THEN 'SET NULL'
                             WHEN 'r' THEN 'RESTRICT'
                             else null
         END AS on_delete,
         CASE k1.confupdtype WHEN 'a' THEN 'NO ACTION'
                             WHEN 'c' THEN 'CASCADE'
                             WHEN 'd' THEN 'SET DEFAULT'
                             WHEN 'n' THEN 'SET NULL'
                             WHEN 'r' THEN 'RESTRICT'
                             ELSE NULL
         END AS on_update,
         k1.condeferrable AS is_deferrable,
         k1.condeferred   AS is_deferred
    FROM pg_catalog.pg_constraint k1
    JOIN pg_catalog.pg_namespace n1 ON (n1.oid = k1.connamespace)
    JOIN pg_catalog.pg_class c1     ON (c1.oid = k1.conrelid)
    JOIN pg_catalog.pg_class c2     ON (c2.oid = k1.confrelid)
    JOIN pg_catalog.pg_namespace n2 ON (n2.oid = c2.relnamespace)
    JOIN pg_catalog.pg_depend d     ON (
                 d.classid = 'pg_constraint'::regclass
             AND d.objid = k1.oid
             AND d.objsubid = 0
             AND d.deptype = 'n'
             AND d.refclassid = 'pg_class'::regclass
             AND d.refobjsubid=0
         )
    JOIN pg_catalog.pg_class ci ON (ci.oid = d.refobjid AND ci.relkind = 'i')
    LEFT JOIN pg_depend d2      ON (
                 d2.classid = 'pg_class'::regclass
             AND d2.objid = ci.oid
             AND d2.objsubid = 0
             AND d2.deptype = 'i'
             AND d2.refclassid = 'pg_constraint'::regclass
             AND d2.refobjsubid = 0
         )
    LEFT JOIN pg_catalog.pg_constraint k2 ON (
                 k2.oid = d2.refobjid
             AND k2.contype IN ('p', 'u')
         )
   WHERE k1.conrelid != 0
     AND k1.confrelid != 0
     AND k1.contype = 'f'
     AND _pg_sv_table_accessible(n1.oid, c1.oid);

-- _keys( schema, table, constraint_type )
CREATE OR REPLACE FUNCTION _keys ( NAME, NAME, CHAR )
RETURNS SETOF NAME[] AS $$
    SELECT _pg_sv_column_array(x.conrelid,x.conkey)
      FROM pg_catalog.pg_namespace n
      JOIN pg_catalog.pg_class c       ON n.oid = c.relnamespace
      JOIN pg_catalog.pg_constraint x  ON c.oid = x.conrelid
     WHERE n.nspname = $1
       AND c.relname = $2
       AND x.contype = $3
$$ LANGUAGE sql;

-- _keys( table, constraint_type )
CREATE OR REPLACE FUNCTION _keys ( NAME, CHAR )
RETURNS SETOF NAME[] AS $$
    SELECT _pg_sv_column_array(x.conrelid,x.conkey)
      FROM pg_catalog.pg_class c
      JOIN pg_catalog.pg_constraint x  ON c.oid = x.conrelid
       AND c.relname = $1
       AND x.contype = $2
$$ LANGUAGE sql;

-- _ckeys( schema, table, constraint_type )
CREATE OR REPLACE FUNCTION _ckeys ( NAME, NAME, CHAR )
RETURNS NAME[] AS $$
    SELECT * FROM _keys($1, $2, $3) LIMIT 1;
$$ LANGUAGE sql;

-- _ckeys( table, constraint_type )
CREATE OR REPLACE FUNCTION _ckeys ( NAME, CHAR )
RETURNS NAME[] AS $$
    SELECT * FROM _keys($1, $2) LIMIT 1;
$$ LANGUAGE sql;

-- col_is_pk( schema, table, column, description )
CREATE OR REPLACE FUNCTION col_is_pk ( NAME, NAME, NAME[], TEXT )
RETURNS TEXT AS $$
    SELECT is( _ckeys( $1, $2, 'p' ), $3, $4 );
$$ LANGUAGE sql;

-- col_is_pk( table, column, description )
CREATE OR REPLACE FUNCTION col_is_pk ( NAME, NAME[], TEXT )
RETURNS TEXT AS $$
    SELECT is( _ckeys( $1, 'p' ), $2, $3 );
$$ LANGUAGE sql;

-- col_is_pk( table, column[] )
CREATE OR REPLACE FUNCTION col_is_pk ( NAME, NAME[] )
RETURNS TEXT AS $$
    SELECT col_is_pk( $1, $2, 'Columns ' || quote_ident($1) || '(' || _ident_array_to_string($2, ', ') || ') should be a primary key' );
$$ LANGUAGE sql;

-- col_is_pk( schema, table, column, description )
CREATE OR REPLACE FUNCTION col_is_pk ( NAME, NAME, NAME, TEXT )
RETURNS TEXT AS $$
    SELECT col_is_pk( $1, $2, ARRAY[$3], $4 );
$$ LANGUAGE sql;

-- col_is_pk( table, column, description )
CREATE OR REPLACE FUNCTION col_is_pk ( NAME, NAME, TEXT )
RETURNS TEXT AS $$
    SELECT col_is_pk( $1, ARRAY[$2], $3 );
$$ LANGUAGE sql;

-- col_is_pk( table, column )
CREATE OR REPLACE FUNCTION col_is_pk ( NAME, NAME )
RETURNS TEXT AS $$
    SELECT col_is_pk( $1, $2, 'Column ' || quote_ident($1) || '(' || quote_ident($2) || ') should be a primary key' );
$$ LANGUAGE sql;

-- col_isnt_pk( schema, table, column, description )
CREATE OR REPLACE FUNCTION col_isnt_pk ( NAME, NAME, NAME[], TEXT )
RETURNS TEXT AS $$
    SELECT isnt( _ckeys( $1, $2, 'p' ), $3, $4 );
$$ LANGUAGE sql;

-- col_isnt_pk( table, column, description )
CREATE OR REPLACE FUNCTION col_isnt_pk ( NAME, NAME[], TEXT )
RETURNS TEXT AS $$
    SELECT isnt( _ckeys( $1, 'p' ), $2, $3 );
$$ LANGUAGE sql;

-- col_isnt_pk( table, column[] )
CREATE OR REPLACE FUNCTION col_isnt_pk ( NAME, NAME[] )
RETURNS TEXT AS $$
    SELECT col_isnt_pk( $1, $2, 'Columns ' || quote_ident($1) || '(' || _ident_array_to_string($2, ', ') || ') should not be a primary key' );
$$ LANGUAGE sql;

-- col_isnt_pk( schema, table, column, description )
CREATE OR REPLACE FUNCTION col_isnt_pk ( NAME, NAME, NAME, TEXT )
RETURNS TEXT AS $$
    SELECT col_isnt_pk( $1, $2, ARRAY[$3], $4 );
$$ LANGUAGE sql;

-- col_isnt_pk( table, column, description )
CREATE OR REPLACE FUNCTION col_isnt_pk ( NAME, NAME, TEXT )
RETURNS TEXT AS $$
    SELECT col_isnt_pk( $1, ARRAY[$2], $3 );
$$ LANGUAGE sql;

-- col_isnt_pk( table, column )
CREATE OR REPLACE FUNCTION col_isnt_pk ( NAME, NAME )
RETURNS TEXT AS $$
    SELECT col_isnt_pk( $1, $2, 'Column ' || quote_ident($1) || '(' || quote_ident($2) || ') should not be a primary key' );
$$ LANGUAGE sql;

-- has_fk( schema, table, description )
CREATE OR REPLACE FUNCTION has_fk ( NAME, NAME, TEXT )
RETURNS TEXT AS $$
    SELECT ok( _hasc( $1, $2, 'f' ), $3 );
$$ LANGUAGE sql;

-- has_fk( table, description )
CREATE OR REPLACE FUNCTION has_fk ( NAME, TEXT )
RETURNS TEXT AS $$
    SELECT ok( _hasc( $1, 'f' ), $2 );
$$ LANGUAGE sql;

-- has_fk( table )
CREATE OR REPLACE FUNCTION has_fk ( NAME )
RETURNS TEXT AS $$
    SELECT has_fk( $1, 'Table ' || quote_ident($1) || ' should have a foreign key constraint' );
$$ LANGUAGE sql;

-- hasnt_fk( schema, table, description )
CREATE OR REPLACE FUNCTION hasnt_fk ( NAME, NAME, TEXT )
RETURNS TEXT AS $$
    SELECT ok( NOT _hasc( $1, $2, 'f' ), $3 );
$$ LANGUAGE sql;

-- hasnt_fk( table, description )
CREATE OR REPLACE FUNCTION hasnt_fk ( NAME, TEXT )
RETURNS TEXT AS $$
    SELECT ok( NOT _hasc( $1, 'f' ), $2 );
$$ LANGUAGE sql;

-- hasnt_fk( table )
CREATE OR REPLACE FUNCTION hasnt_fk ( NAME )
RETURNS TEXT AS $$
    SELECT hasnt_fk( $1, 'Table ' || quote_ident($1) || ' should not have a foreign key constraint' );
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION _fkexists ( NAME, NAME, NAME[] )
RETURNS BOOLEAN AS $$
    SELECT EXISTS(
        SELECT TRUE
           FROM pg_all_foreign_keys
          WHERE fk_schema_name    = $1
            AND quote_ident(fk_table_name)     = quote_ident($2)
            AND fk_columns = $3
    );
$$ LANGUAGE SQL;

CREATE OR REPLACE FUNCTION _fkexists ( NAME, NAME[] )
RETURNS BOOLEAN AS $$
    SELECT EXISTS(
        SELECT TRUE
           FROM pg_all_foreign_keys
          WHERE quote_ident(fk_table_name)     = quote_ident($1)
            AND fk_columns = $2
    );
$$ LANGUAGE SQL;

-- col_is_fk( schema, table, column, description )
CREATE OR REPLACE FUNCTION col_is_fk ( NAME, NAME, NAME[], TEXT )
RETURNS TEXT AS $$
DECLARE
    names text[];
BEGIN
    IF _fkexists($1, $2, $3) THEN
        RETURN pass( $4 );
    END IF;

    -- Try to show the columns.
    SELECT ARRAY(
        SELECT _ident_array_to_string(fk_columns, ', ')
          FROM pg_all_foreign_keys
         WHERE fk_schema_name = $1
           AND fk_table_name  = $2
         ORDER BY fk_columns
    ) INTO names;

    IF names[1] IS NOT NULL THEN
        RETURN fail($4) || E'\n' || diag(
            '    Table ' || quote_ident($1) || '.' || quote_ident($2) || E' has foreign key constraints on these columns:\n        '
            ||  array_to_string( names, E'\n        ' )
        );
    END IF;

    -- No FKs in this table.
    RETURN fail($4) || E'\n' || diag(
        '    Table ' || quote_ident($1) || '.' || quote_ident($2) || ' has no foreign key columns'
    );
END;
$$ LANGUAGE plpgsql;

-- col_is_fk( table, column, description )
CREATE OR REPLACE FUNCTION col_is_fk ( NAME, NAME[], TEXT )
RETURNS TEXT AS $$
DECLARE
    names text[];
BEGIN
    IF _fkexists($1, $2) THEN
        RETURN pass( $3 );
    END IF;

    -- Try to show the columns.
    SELECT ARRAY(
        SELECT _ident_array_to_string(fk_columns, ', ')
          FROM pg_all_foreign_keys
         WHERE fk_table_name  = $1
         ORDER BY fk_columns
    ) INTO names;

    IF NAMES[1] IS NOT NULL THEN
        RETURN fail($3) || E'\n' || diag(
            '    Table ' || quote_ident($1) || E' has foreign key constraints on these columns:\n        '
            || array_to_string( names, E'\n        ' )
        );
    END IF;

    -- No FKs in this table.
    RETURN fail($3) || E'\n' || diag(
        '    Table ' || quote_ident($1) || ' has no foreign key columns'
    );
END;
$$ LANGUAGE plpgsql;

-- col_is_fk( table, column[] )
CREATE OR REPLACE FUNCTION col_is_fk ( NAME, NAME[] )
RETURNS TEXT AS $$
    SELECT col_is_fk( $1, $2, 'Columns ' || quote_ident($1) || '(' || _ident_array_to_string($2, ', ') || ') should be a foreign key' );
$$ LANGUAGE sql;

-- col_is_fk( schema, table, column, description )
CREATE OR REPLACE FUNCTION col_is_fk ( NAME, NAME, NAME, TEXT )
RETURNS TEXT AS $$
    SELECT col_is_fk( $1, $2, ARRAY[$3], $4 );
$$ LANGUAGE sql;

-- col_is_fk( table, column, description )
CREATE OR REPLACE FUNCTION col_is_fk ( NAME, NAME, TEXT )
RETURNS TEXT AS $$
    SELECT col_is_fk( $1, ARRAY[$2], $3 );
$$ LANGUAGE sql;

-- col_is_fk( table, column )
CREATE OR REPLACE FUNCTION col_is_fk ( NAME, NAME )
RETURNS TEXT AS $$
    SELECT col_is_fk( $1, $2, 'Column ' || quote_ident($1) || '(' || quote_ident($2) || ') should be a foreign key' );
$$ LANGUAGE sql;

-- col_isnt_fk( schema, table, column, description )
CREATE OR REPLACE FUNCTION col_isnt_fk ( NAME, NAME, NAME[], TEXT )
RETURNS TEXT AS $$
    SELECT ok( NOT _fkexists( $1, $2, $3 ), $4 );
$$ LANGUAGE SQL;

-- col_isnt_fk( table, column, description )
CREATE OR REPLACE FUNCTION col_isnt_fk ( NAME, NAME[], TEXT )
RETURNS TEXT AS $$
    SELECT ok( NOT _fkexists( $1, $2 ), $3 );
$$ LANGUAGE SQL;

-- col_isnt_fk( table, column[] )
CREATE OR REPLACE FUNCTION col_isnt_fk ( NAME, NAME[] )
RETURNS TEXT AS $$
    SELECT col_isnt_fk( $1, $2, 'Columns ' || quote_ident($1) || '(' || _ident_array_to_string($2, ', ') || ') should not be a foreign key' );
$$ LANGUAGE sql;

-- col_isnt_fk( schema, table, column, description )
CREATE OR REPLACE FUNCTION col_isnt_fk ( NAME, NAME, NAME, TEXT )
RETURNS TEXT AS $$
    SELECT col_isnt_fk( $1, $2, ARRAY[$3], $4 );
$$ LANGUAGE sql;

-- col_isnt_fk( table, column, description )
CREATE OR REPLACE FUNCTION col_isnt_fk ( NAME, NAME, TEXT )
RETURNS TEXT AS $$
    SELECT col_isnt_fk( $1, ARRAY[$2], $3 );
$$ LANGUAGE sql;

-- col_isnt_fk( table, column )
CREATE OR REPLACE FUNCTION col_isnt_fk ( NAME, NAME )
RETURNS TEXT AS $$
    SELECT col_isnt_fk( $1, $2, 'Column ' || quote_ident($1) || '(' || quote_ident($2) || ') should not be a foreign key' );
$$ LANGUAGE sql;

-- has_unique( schema, table, description )
CREATE OR REPLACE FUNCTION has_unique ( TEXT, TEXT, TEXT )
RETURNS TEXT AS $$
    SELECT ok( _hasc( $1, $2, 'u' ), $3 );
$$ LANGUAGE sql;

-- has_unique( table, description )
CREATE OR REPLACE FUNCTION has_unique ( TEXT, TEXT )
RETURNS TEXT AS $$
    SELECT ok( _hasc( $1, 'u' ), $2 );
$$ LANGUAGE sql;

-- has_unique( table )
CREATE OR REPLACE FUNCTION has_unique ( TEXT )
RETURNS TEXT AS $$
    SELECT has_unique( $1, 'Table ' || quote_ident($1) || ' should have a unique constraint' );
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION _constraint ( NAME, NAME, CHAR, NAME[], TEXT, TEXT )
RETURNS TEXT AS $$
DECLARE
    akey NAME[];
    keys TEXT[] := '{}';
    have TEXT;
BEGIN
    FOR akey IN SELECT * FROM _keys($1, $2, $3) LOOP
        IF akey = $4 THEN RETURN pass($5); END IF;
        keys = keys || akey::text;
    END LOOP;
    IF array_upper(keys, 0) = 1 THEN
        have := 'No ' || $6 || ' constriants';
    ELSE
        have := array_to_string(keys, E'\n              ');
    END IF;

    RETURN fail($5) || E'\n' || diag(
             '        have: ' || have
       || E'\n        want: ' || CASE WHEN $4 IS NULL THEN 'NULL' ELSE $4::text END
    );
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION _constraint ( NAME, CHAR, NAME[], TEXT, TEXT )
RETURNS TEXT AS $$
DECLARE
    akey NAME[];
    keys TEXT[] := '{}';
    have TEXT;
BEGIN
    FOR akey IN SELECT * FROM _keys($1, $2) LOOP
        IF akey = $3 THEN RETURN pass($4); END IF;
        keys = keys || akey::text;
    END LOOP;
    IF array_upper(keys, 0) = 1 THEN
        have := 'No ' || $5 || ' constriants';
    ELSE
        have := array_to_string(keys, E'\n              ');
    END IF;

    RETURN fail($4) || E'\n' || diag(
             '        have: ' || have
       || E'\n        want: ' || CASE WHEN $3 IS NULL THEN 'NULL' ELSE $3::text END
    );
END;
$$ LANGUAGE plpgsql;

-- col_is_unique( schema, table, column, description )
CREATE OR REPLACE FUNCTION col_is_unique ( NAME, NAME, NAME[], TEXT )
RETURNS TEXT AS $$
    SELECT _constraint( $1, $2, 'u', $3, $4, 'unique' );
$$ LANGUAGE sql;

-- col_is_unique( table, column, description )
CREATE OR REPLACE FUNCTION col_is_unique ( NAME, NAME[], TEXT )
RETURNS TEXT AS $$
    SELECT _constraint( $1, 'u', $2, $3, 'unique' );
$$ LANGUAGE sql;

-- col_is_unique( table, column[] )
CREATE OR REPLACE FUNCTION col_is_unique ( NAME, NAME[] )
RETURNS TEXT AS $$
    SELECT col_is_unique( $1, $2, 'Columns ' || quote_ident($1) || '(' || _ident_array_to_string($2, ', ') || ') should have a unique constraint' );
$$ LANGUAGE sql;

-- col_is_unique( schema, table, column, description )
CREATE OR REPLACE FUNCTION col_is_unique ( NAME, NAME, NAME, TEXT )
RETURNS TEXT AS $$
    SELECT col_is_unique( $1, $2, ARRAY[$3], $4 );
$$ LANGUAGE sql;

-- col_is_unique( table, column, description )
CREATE OR REPLACE FUNCTION col_is_unique ( NAME, NAME, TEXT )
RETURNS TEXT AS $$
    SELECT col_is_unique( $1, ARRAY[$2], $3 );
$$ LANGUAGE sql;

-- col_is_unique( table, column )
CREATE OR REPLACE FUNCTION col_is_unique ( NAME, NAME )
RETURNS TEXT AS $$
    SELECT col_is_unique( $1, $2, 'Column ' || quote_ident($1) || '(' || quote_ident($2) || ') should have a unique constraint' );
$$ LANGUAGE sql;

-- has_check( schema, table, description )
CREATE OR REPLACE FUNCTION has_check ( NAME, NAME, TEXT )
RETURNS TEXT AS $$
    SELECT ok( _hasc( $1, $2, 'c' ), $3 );
$$ LANGUAGE sql;

-- has_check( table, description )
CREATE OR REPLACE FUNCTION has_check ( NAME, TEXT )
RETURNS TEXT AS $$
    SELECT ok( _hasc( $1, 'c' ), $2 );
$$ LANGUAGE sql;

-- has_check( table )
CREATE OR REPLACE FUNCTION has_check ( NAME )
RETURNS TEXT AS $$
    SELECT has_check( $1, 'Table ' || quote_ident($1) || ' should have a check constraint' );
$$ LANGUAGE sql;

-- col_has_check( schema, table, column, description )
CREATE OR REPLACE FUNCTION col_has_check ( NAME, NAME, NAME[], TEXT )
RETURNS TEXT AS $$
    SELECT _constraint( $1, $2, 'c', $3, $4, 'check' );
$$ LANGUAGE sql;

-- col_has_check( table, column, description )
CREATE OR REPLACE FUNCTION col_has_check ( NAME, NAME[], TEXT )
RETURNS TEXT AS $$
    SELECT _constraint( $1, 'c', $2, $3, 'check' );
$$ LANGUAGE sql;

-- col_has_check( table, column[] )
CREATE OR REPLACE FUNCTION col_has_check ( NAME, NAME[] )
RETURNS TEXT AS $$
    SELECT col_has_check( $1, $2, 'Columns ' || quote_ident($1) || '(' || _ident_array_to_string($2, ', ') || ') should have a check constraint' );
$$ LANGUAGE sql;

-- col_has_check( schema, table, column, description )
CREATE OR REPLACE FUNCTION col_has_check ( NAME, NAME, NAME, TEXT )
RETURNS TEXT AS $$
    SELECT col_has_check( $1, $2, ARRAY[$3], $4 );
$$ LANGUAGE sql;

-- col_has_check( table, column, description )
CREATE OR REPLACE FUNCTION col_has_check ( NAME, NAME, TEXT )
RETURNS TEXT AS $$
    SELECT col_has_check( $1, ARRAY[$2], $3 );
$$ LANGUAGE sql;

-- col_has_check( table, column )
CREATE OR REPLACE FUNCTION col_has_check ( NAME, NAME )
RETURNS TEXT AS $$
    SELECT col_has_check( $1, $2, 'Column ' || quote_ident($1) || '(' || quote_ident($2) || ') should have a check constraint' );
$$ LANGUAGE sql;

-- fk_ok( fk_schema, fk_table, fk_column[], pk_schema, pk_table, pk_column[], description )
CREATE OR REPLACE FUNCTION fk_ok ( NAME, NAME, NAME[], NAME, NAME, NAME[], TEXT )
RETURNS TEXT AS $$
DECLARE
    sch  name;
    tab  name;
    cols name[];
BEGIN
    SELECT pk_schema_name, pk_table_name, pk_columns
      FROM pg_all_foreign_keys
      WHERE fk_schema_name = $1
        AND fk_table_name  = $2
        AND fk_columns     = $3
      INTO sch, tab, cols;

    RETURN is(
        -- have
        quote_ident($1) || '.' || quote_ident($2) || '(' || _ident_array_to_string( $3, ', ' )
        || ') REFERENCES ' || COALESCE ( sch || '.' || tab || '(' || _ident_array_to_string( cols, ', ' ) || ')', 'NOTHING' ),
        -- want
        quote_ident($1) || '.' || quote_ident($2) || '(' || _ident_array_to_string( $3, ', ' )
        || ') REFERENCES ' ||
        $4 || '.' || $5 || '(' || _ident_array_to_string( $6, ', ' ) || ')',
        $7
    );
END;
$$ LANGUAGE plpgsql;

-- fk_ok( fk_table, fk_column[], pk_table, pk_column[], description )
CREATE OR REPLACE FUNCTION fk_ok ( NAME, NAME[], NAME, NAME[], TEXT )
RETURNS TEXT AS $$
DECLARE
    tab  name;
    cols name[];
BEGIN
    SELECT pk_table_name, pk_columns
      FROM pg_all_foreign_keys
      WHERE fk_table_name = $1
        AND fk_columns    = $2
      INTO tab, cols;

    RETURN is(
        -- have
        $1 || '(' || _ident_array_to_string( $2, ', ' )
        || ') REFERENCES ' || COALESCE( tab || '(' || _ident_array_to_string( cols, ', ' ) || ')', 'NOTHING'),
        -- want
        $1 || '(' || _ident_array_to_string( $2, ', ' )
        || ') REFERENCES ' ||
        $3 || '(' || _ident_array_to_string( $4, ', ' ) || ')',
        $5
    );
END;
$$ LANGUAGE plpgsql;

-- fk_ok( fk_schema, fk_table, fk_column[], fk_schema, pk_table, pk_column[] )
CREATE OR REPLACE FUNCTION fk_ok ( NAME, NAME, NAME[], NAME, NAME, NAME[] )
RETURNS TEXT AS $$
    SELECT fk_ok( $1, $2, $3, $4, $5, $6,
        quote_ident($1) || '.' || quote_ident($2) || '(' || _ident_array_to_string( $3, ', ' )
        || ') should reference ' ||
        $4 || '.' || $5 || '(' || _ident_array_to_string( $6, ', ' ) || ')'
    );
$$ LANGUAGE sql;

-- fk_ok( fk_table, fk_column[], pk_table, pk_column[] )
CREATE OR REPLACE FUNCTION fk_ok ( NAME, NAME[], NAME, NAME[] )
RETURNS TEXT AS $$
    SELECT fk_ok( $1, $2, $3, $4,
        $1 || '(' || _ident_array_to_string( $2, ', ' )
        || ') should reference ' ||
        $3 || '(' || _ident_array_to_string( $4, ', ' ) || ')'
    );
$$ LANGUAGE sql;

-- fk_ok( fk_schema, fk_table, fk_column, pk_schema, pk_table, pk_column, description )
CREATE OR REPLACE FUNCTION fk_ok ( NAME, NAME, NAME, NAME, NAME, NAME, TEXT )
RETURNS TEXT AS $$
    SELECT fk_ok( $1, $2, ARRAY[$3], $4, $5, ARRAY[$6], $7 );
$$ LANGUAGE sql;

-- fk_ok( fk_schema, fk_table, fk_column, pk_schema, pk_table, pk_column )
CREATE OR REPLACE FUNCTION fk_ok ( NAME, NAME, NAME, NAME, NAME, TEXT )
RETURNS TEXT AS $$
    SELECT fk_ok( $1, $2, ARRAY[$3], $4, $5, ARRAY[$6] );
$$ LANGUAGE sql;

-- fk_ok( fk_table, fk_column, pk_table, pk_column, description )
CREATE OR REPLACE FUNCTION fk_ok ( NAME, NAME, NAME, NAME, TEXT )
RETURNS TEXT AS $$
    SELECT fk_ok( $1, ARRAY[$2], $3, ARRAY[$4], $5 );
$$ LANGUAGE sql;

-- fk_ok( fk_table, fk_column, pk_table, pk_column )
CREATE OR REPLACE FUNCTION fk_ok ( NAME, NAME, NAME, NAME )
RETURNS TEXT AS $$
    SELECT fk_ok( $1, ARRAY[$2], $3, ARRAY[$4] );
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION _ikeys( NAME, NAME, NAME)
RETURNS NAME[] AS $$
    SELECT ARRAY(
        SELECT a.attname
          FROM pg_catalog.pg_index x
          JOIN pg_catalog.pg_class ct    ON ct.oid = x.indrelid
          JOIN pg_catalog.pg_class ci    ON ci.oid = x.indexrelid
          JOIN pg_catalog.pg_namespace n ON n.oid = ct.relnamespace
          JOIN pg_catalog.pg_attribute a ON ct.oid = a.attrelid
          JOIN generate_series(0, current_setting('max_index_keys')::int - 1) s(i)
            ON a.attnum = x.indkey[s.i]
         WHERE ct.relname = $2
           AND ci.relname = $3
           AND n.nspname  = $1
         ORDER BY s.i
    );
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION _ikeys( NAME, NAME)
RETURNS NAME[] AS $$
    SELECT ARRAY(
        SELECT a.attname
          FROM pg_catalog.pg_index x
          JOIN pg_catalog.pg_class ct    ON ct.oid = x.indrelid
          JOIN pg_catalog.pg_class ci    ON ci.oid = x.indexrelid
          JOIN pg_catalog.pg_attribute a ON ct.oid = a.attrelid
          JOIN generate_series(0, current_setting('max_index_keys')::int - 1) s(i)
            ON a.attnum = x.indkey[s.i]
         WHERE ct.relname = $1
           AND ci.relname = $2
           AND pg_catalog.pg_table_is_visible(ct.oid)
         ORDER BY s.i
    );
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION _have_index( NAME, NAME, NAME)
RETURNS BOOLEAN AS $$
    SELECT EXISTS (
    SELECT TRUE
      FROM pg_catalog.pg_index x
      JOIN pg_catalog.pg_class ct    ON ct.oid = x.indrelid
      JOIN pg_catalog.pg_class ci    ON ci.oid = x.indexrelid
      JOIN pg_catalog.pg_namespace n ON n.oid = ct.relnamespace
     WHERE ct.relname = $2
       AND ci.relname = $3
       AND n.nspname  = $1
    );
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION _have_index( NAME, NAME)
RETURNS BOOLEAN AS $$
    SELECT EXISTS (
    SELECT TRUE
      FROM pg_catalog.pg_index x
      JOIN pg_catalog.pg_class ct    ON ct.oid = x.indrelid
      JOIN pg_catalog.pg_class ci    ON ci.oid = x.indexrelid
     WHERE ct.relname = $1
       AND ci.relname = $2
    );
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION _iexpr( NAME, NAME, NAME)
RETURNS TEXT AS $$
    SELECT pg_catalog.pg_get_expr( x.indexprs, ct.oid )
      FROM pg_catalog.pg_index x
      JOIN pg_catalog.pg_class ct    ON ct.oid = x.indrelid
      JOIN pg_catalog.pg_class ci    ON ci.oid = x.indexrelid
      JOIN pg_catalog.pg_namespace n ON n.oid = ct.relnamespace
     WHERE ct.relname = $2
       AND ci.relname = $3
       AND n.nspname  = $1
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION _iexpr( NAME, NAME)
RETURNS TEXT AS $$
    SELECT pg_catalog.pg_get_expr( x.indexprs, ct.oid )
      FROM pg_catalog.pg_index x
      JOIN pg_catalog.pg_class ct ON ct.oid = x.indrelid
      JOIN pg_catalog.pg_class ci ON ci.oid = x.indexrelid
     WHERE ct.relname = $1
       AND ci.relname = $2
       AND pg_catalog.pg_table_is_visible(ct.oid)
$$ LANGUAGE sql;

-- has_index( schema, table, index, columns[], description )
CREATE OR REPLACE FUNCTION has_index ( NAME, NAME, NAME, NAME[], text )
RETURNS TEXT AS $$
DECLARE
     index_cols name[];
BEGIN
    index_cols := _ikeys($1, $2, $3 );

    IF index_cols IS NULL OR index_cols = '{}'::name[] THEN
        RETURN ok( false, $5 ) || E'\n'
            || diag( 'Index ' || quote_ident($3) || ' ON ' || quote_ident($1) || '.' || quote_ident($2) || ' not found');
    END IF;

    RETURN is(
        quote_ident($3) || ' ON ' || quote_ident($1) || '.' || quote_ident($2) || '(' || _ident_array_to_string( index_cols, ', ' ) || ')',
        quote_ident($3) || ' ON ' || quote_ident($1) || '.' || quote_ident($2) || '(' || _ident_array_to_string( $4, ', ' ) || ')',
        $5
    );
END;
$$ LANGUAGE plpgsql;

-- has_index( schema, table, index, columns[] )
CREATE OR REPLACE FUNCTION has_index ( NAME, NAME, NAME, NAME[] )
RETURNS TEXT AS $$
   SELECT has_index( $1, $2, $3, $4, 'Index ' || quote_ident($3) || ' should exist' );
$$ LANGUAGE sql;

-- has_index( schema, table, index, column/expression, description )
CREATE OR REPLACE FUNCTION has_index ( NAME, NAME, NAME, NAME, text )
RETURNS TEXT AS $$
DECLARE
    expr text;
BEGIN
    IF $4 NOT LIKE '%(%' THEN
        -- Not a functional index.
        RETURN has_index( $1, $2, $3, ARRAY[$4], $5 );
    END IF;

    -- Get the functional expression.
    expr := _iexpr($1, $2, $3);

    IF expr IS NULL THEN
        RETURN ok( false, $5 ) || E'\n'
            || diag( 'Index ' || quote_ident($3) || ' ON ' || quote_ident($1) || '.' || quote_ident($2) || ' not found');
    END IF;

    RETURN is(
        quote_ident($3) || ' ON ' || quote_ident($1) || '.' || quote_ident($2) || '(' || expr || ')',
        quote_ident($3) || ' ON ' || quote_ident($1) || '.' || quote_ident($2) || '(' || $4 || ')',
        $5
    );
END;
$$ LANGUAGE plpgsql;

-- has_index( schema, table, index, columns/expression )
CREATE OR REPLACE FUNCTION has_index ( NAME, NAME, NAME, NAME )
RETURNS TEXT AS $$
   SELECT has_index( $1, $2, $3, $4, 'Index ' || quote_ident($3) || ' should exist' );
$$ LANGUAGE sql;

-- has_index( table, index, columns[], description )
CREATE OR REPLACE FUNCTION has_index ( NAME, NAME, NAME[], text )
RETURNS TEXT AS $$
DECLARE
     index_cols name[];
BEGIN
    index_cols := _ikeys($1, $2 );

    IF index_cols IS NULL OR index_cols = '{}'::name[] THEN
        RETURN ok( false, $4 ) || E'\n'
            || diag( 'Index ' || quote_ident($2) || ' ON ' || quote_ident($1) || ' not found');
    END IF;

    RETURN is(
        quote_ident($2) || ' ON ' || quote_ident($1) || '(' || _ident_array_to_string( index_cols, ', ' ) || ')',
        quote_ident($2) || ' ON ' || quote_ident($1) || '(' || _ident_array_to_string( $3, ', ' ) || ')',
        $4
    );
END;
$$ LANGUAGE plpgsql;

-- has_index( table, index, columns[], description )
CREATE OR REPLACE FUNCTION has_index ( NAME, NAME, NAME[] )
RETURNS TEXT AS $$
   SELECT has_index( $1, $2, $3, 'Index ' || quote_ident($2) || ' should exist' );
$$ LANGUAGE sql;

-- _is_schema( schema )
CREATE OR REPLACE FUNCTION _is_schema( NAME )
returns boolean AS $$
    SELECT EXISTS(
        SELECT true
          FROM pg_catalog.pg_namespace
          WHERE nspname = $1
    );
$$ LANGUAGE sql;

-- has_index( table, index, column/expression, description )
-- has_index( schema, table, index, column/expression )
CREATE OR REPLACE FUNCTION has_index ( NAME, NAME, NAME, text )
RETURNS TEXT AS $$
DECLARE
    want_expr text;
    descr text;
    have_expr text;
    idx name;
    tab text;
BEGIN
    IF $3 NOT LIKE '%(%' THEN
        -- Not a functional index.
        IF _is_schema( $1 ) THEN
            -- Looking for schema.table index.
            RETURN ok ( _have_index( $1, $2, $3 ), $4);
        END IF;
        -- Looking for particular columns.
        RETURN has_index( $1, $2, ARRAY[$3], $4 );
    END IF;

    -- Get the functional expression.
    IF _is_schema( $1 ) THEN
        -- Looking for an index within a schema.
        have_expr := _iexpr($1, $2, $3);
        want_expr := $4;
        descr     := 'Index ' || quote_ident($3) || ' should exist';
        idx       := $3;
        tab       := quote_ident($1) || '.' || quote_ident($2);
    ELSE
        -- Looking for an index without a schema spec.
        have_expr := _iexpr($1, $2);
        want_expr := $3;
        descr     := $4;
        idx       := $2;
        tab       := quote_ident($1);
    END IF;

    IF have_expr IS NULL THEN
        RETURN ok( false, descr ) || E'\n'
            || diag( 'Index ' || idx || ' ON ' || tab || ' not found');
    END IF;

    RETURN is(
        quote_ident(idx) || ' ON ' || tab || '(' || have_expr || ')',
        quote_ident(idx) || ' ON ' || tab || '(' || want_expr || ')',
        descr
    );
END;
$$ LANGUAGE plpgsql;

-- has_index( table, index, column/expression )
-- has_index( schema, table, index )
CREATE OR REPLACE FUNCTION has_index ( NAME, NAME, NAME )
RETURNS TEXT AS $$
BEGIN
   IF _is_schema($1) THEN
       -- ( schema, table, index )
       RETURN ok( _have_index( $1, $2, $3 ), 'Index ' || quote_ident($3) || ' should exist' );
   ELSE
       -- ( table, index, column/expression )
       RETURN has_index( $1, $2, $3, 'Index ' || quote_ident($2) || ' should exist' );
   END IF;
END;
$$ LANGUAGE plpgsql;

-- has_index( table, index, description )
CREATE OR REPLACE FUNCTION has_index ( NAME, NAME, text )
RETURNS TEXT AS $$
    SELECT CASE WHEN $3 LIKE '%(%'
           THEN has_index( $1, $2, $3::name )
           ELSE ok( _have_index( $1, $2 ), $3 )
           END;
$$ LANGUAGE sql;

-- has_index( table, index )
CREATE OR REPLACE FUNCTION has_index ( NAME, NAME )
RETURNS TEXT AS $$
    SELECT ok( _have_index( $1, $2 ), 'Index ' || quote_ident($2) || ' should exist' );
$$ LANGUAGE sql;

-- hasnt_index( schema, table, index, description )
CREATE OR REPLACE FUNCTION hasnt_index ( NAME, NAME, NAME, TEXT )
RETURNS TEXT AS $$
BEGIN
    RETURN ok( NOT _have_index( $1, $2, $3 ), $4 );
END;
$$ LANGUAGE plpgSQL;

-- hasnt_index( schema, table, index )
CREATE OR REPLACE FUNCTION hasnt_index ( NAME, NAME, NAME )
RETURNS TEXT AS $$
    SELECT ok(
        NOT _have_index( $1, $2, $3 ),
        'Index ' || quote_ident($3) || ' should not exist'
    );
$$ LANGUAGE SQL;

-- hasnt_index( table, index, description )
CREATE OR REPLACE FUNCTION hasnt_index ( NAME, NAME, TEXT )
RETURNS TEXT AS $$
    SELECT ok( NOT _have_index( $1, $2 ), $3 );
$$ LANGUAGE SQL;

-- hasnt_index( table, index )
CREATE OR REPLACE FUNCTION hasnt_index ( NAME, NAME )
RETURNS TEXT AS $$
    SELECT ok(
        NOT _have_index( $1, $2 ),
        'Index ' || quote_ident($2) || ' should not exist'
    );
$$ LANGUAGE SQL;

-- index_is_unique( schema, table, index, description )
CREATE OR REPLACE FUNCTION index_is_unique ( NAME, NAME, NAME, text )
RETURNS TEXT AS $$
DECLARE
    res boolean;
BEGIN
    SELECT x.indisunique
      FROM pg_catalog.pg_index x
      JOIN pg_catalog.pg_class ct    ON ct.oid = x.indrelid
      JOIN pg_catalog.pg_class ci    ON ci.oid = x.indexrelid
      JOIN pg_catalog.pg_namespace n ON n.oid = ct.relnamespace
     WHERE ct.relname = $2
       AND ci.relname = $3
       AND n.nspname  = $1
      INTO res;

      RETURN ok( COALESCE(res, false), $4 );
END;
$$ LANGUAGE plpgsql;

-- index_is_unique( schema, table, index )
CREATE OR REPLACE FUNCTION index_is_unique ( NAME, NAME, NAME )
RETURNS TEXT AS $$
    SELECT index_is_unique(
        $1, $2, $3,
        'Index ' || quote_ident($3) || ' should be unique'
    );
$$ LANGUAGE sql;

-- index_is_unique( table, index )
CREATE OR REPLACE FUNCTION index_is_unique ( NAME, NAME )
RETURNS TEXT AS $$
DECLARE
    res boolean;
BEGIN
    SELECT x.indisunique
      FROM pg_catalog.pg_index x
      JOIN pg_catalog.pg_class ct ON ct.oid = x.indrelid
      JOIN pg_catalog.pg_class ci ON ci.oid = x.indexrelid
     WHERE ct.relname = $1
       AND ci.relname = $2
       AND pg_catalog.pg_table_is_visible(ct.oid)
      INTO res;

      RETURN ok(
          COALESCE(res, false),
          'Index ' || quote_ident($2) || ' should be unique'
      );
END;
$$ LANGUAGE plpgsql;

-- index_is_unique( index )
CREATE OR REPLACE FUNCTION index_is_unique ( NAME )
RETURNS TEXT AS $$
DECLARE
    res boolean;
BEGIN
    SELECT x.indisunique
      FROM pg_catalog.pg_index x
      JOIN pg_catalog.pg_class ci ON ci.oid = x.indexrelid
      JOIN pg_catalog.pg_class ct ON ct.oid = x.indrelid
     WHERE ci.relname = $1
       AND pg_catalog.pg_table_is_visible(ct.oid)
      INTO res;

      RETURN ok(
          COALESCE(res, false),
          'Index ' || quote_ident($1) || ' should be unique'
      );
END;
$$ LANGUAGE plpgsql;

-- index_is_primary( schema, table, index, description )
CREATE OR REPLACE FUNCTION index_is_primary ( NAME, NAME, NAME, text )
RETURNS TEXT AS $$
DECLARE
    res boolean;
BEGIN
    SELECT x.indisprimary
      FROM pg_catalog.pg_index x
      JOIN pg_catalog.pg_class ct    ON ct.oid = x.indrelid
      JOIN pg_catalog.pg_class ci    ON ci.oid = x.indexrelid
      JOIN pg_catalog.pg_namespace n ON n.oid = ct.relnamespace
     WHERE ct.relname = $2
       AND ci.relname = $3
       AND n.nspname  = $1
      INTO res;

      RETURN ok( COALESCE(res, false), $4 );
END;
$$ LANGUAGE plpgsql;

-- index_is_primary( schema, table, index )
CREATE OR REPLACE FUNCTION index_is_primary ( NAME, NAME, NAME )
RETURNS TEXT AS $$
    SELECT index_is_primary(
        $1, $2, $3,
        'Index ' || quote_ident($3) || ' should be on a primary key'
    );
$$ LANGUAGE sql;

-- index_is_primary( table, index )
CREATE OR REPLACE FUNCTION index_is_primary ( NAME, NAME )
RETURNS TEXT AS $$
DECLARE
    res boolean;
BEGIN
    SELECT x.indisprimary
      FROM pg_catalog.pg_index x
      JOIN pg_catalog.pg_class ct ON ct.oid = x.indrelid
      JOIN pg_catalog.pg_class ci ON ci.oid = x.indexrelid
     WHERE ct.relname = $1
       AND ci.relname = $2
       AND pg_catalog.pg_table_is_visible(ct.oid)
     INTO res;

      RETURN ok(
          COALESCE(res, false),
          'Index ' || quote_ident($2) || ' should be on a primary key'
      );
END;
$$ LANGUAGE plpgsql;

-- index_is_primary( index )
CREATE OR REPLACE FUNCTION index_is_primary ( NAME )
RETURNS TEXT AS $$
DECLARE
    res boolean;
BEGIN
    SELECT x.indisprimary
      FROM pg_catalog.pg_index x
      JOIN pg_catalog.pg_class ci ON ci.oid = x.indexrelid
      JOIN pg_catalog.pg_class ct ON ct.oid = x.indrelid
     WHERE ci.relname = $1
       AND pg_catalog.pg_table_is_visible(ct.oid)
      INTO res;

      RETURN ok(
          COALESCE(res, false),
          'Index ' || quote_ident($1) || ' should be on a primary key'
      );
END;
$$ LANGUAGE plpgsql;

-- is_clustered( schema, table, index, description )
CREATE OR REPLACE FUNCTION is_clustered ( NAME, NAME, NAME, text )
RETURNS TEXT AS $$
DECLARE
    res boolean;
BEGIN
    SELECT x.indisclustered
      FROM pg_catalog.pg_index x
      JOIN pg_catalog.pg_class ct    ON ct.oid = x.indrelid
      JOIN pg_catalog.pg_class ci    ON ci.oid = x.indexrelid
      JOIN pg_catalog.pg_namespace n ON n.oid = ct.relnamespace
     WHERE ct.relname = $2
       AND ci.relname = $3
       AND n.nspname  = $1
      INTO res;

      RETURN ok( COALESCE(res, false), $4 );
END;
$$ LANGUAGE plpgsql;

-- is_clustered( schema, table, index )
CREATE OR REPLACE FUNCTION is_clustered ( NAME, NAME, NAME )
RETURNS TEXT AS $$
    SELECT is_clustered(
        $1, $2, $3,
        'Table ' || quote_ident($1) || '.' || quote_ident($2) ||
        ' should be clustered on index ' || quote_ident($3)
    );
$$ LANGUAGE sql;

-- is_clustered( table, index )
CREATE OR REPLACE FUNCTION is_clustered ( NAME, NAME )
RETURNS TEXT AS $$
DECLARE
    res boolean;
BEGIN
    SELECT x.indisclustered
      FROM pg_catalog.pg_index x
      JOIN pg_catalog.pg_class ct ON ct.oid = x.indrelid
      JOIN pg_catalog.pg_class ci ON ci.oid = x.indexrelid
     WHERE ct.relname = $1
       AND ci.relname = $2
      INTO res;

      RETURN ok(
          COALESCE(res, false),
          'Table ' || quote_ident($1) || ' should be clustered on index ' || quote_ident($2)
      );
END;
$$ LANGUAGE plpgsql;

-- is_clustered( index )
CREATE OR REPLACE FUNCTION is_clustered ( NAME )
RETURNS TEXT AS $$
DECLARE
    res boolean;
BEGIN
    SELECT x.indisclustered
      FROM pg_catalog.pg_index x
      JOIN pg_catalog.pg_class ci ON ci.oid = x.indexrelid
     WHERE ci.relname = $1
      INTO res;

      RETURN ok(
          COALESCE(res, false),
          'Table should be clustered on index ' || quote_ident($1)
      );
END;
$$ LANGUAGE plpgsql;

-- index_is_type( schema, table, index, type, description )
CREATE OR REPLACE FUNCTION index_is_type ( NAME, NAME, NAME, NAME, text )
RETURNS TEXT AS $$
DECLARE
    aname name;
BEGIN
    SELECT am.amname
      FROM pg_catalog.pg_index x
      JOIN pg_catalog.pg_class ct    ON ct.oid = x.indrelid
      JOIN pg_catalog.pg_class ci    ON ci.oid = x.indexrelid
      JOIN pg_catalog.pg_namespace n ON n.oid = ct.relnamespace
      JOIN pg_catalog.pg_am am       ON ci.relam = am.oid
     WHERE ct.relname = $2
       AND ci.relname = $3
       AND n.nspname  = $1
      INTO aname;

      return is( aname, $4, $5 );
END;
$$ LANGUAGE plpgsql;

-- index_is_type( schema, table, index, type )
CREATE OR REPLACE FUNCTION index_is_type ( NAME, NAME, NAME, NAME )
RETURNS TEXT AS $$
    SELECT index_is_type(
        $1, $2, $3, $4,
        'Index ' || quote_ident($3) || ' should be a ' || quote_ident($4) || ' index'
    );
$$ LANGUAGE SQL;

-- index_is_type( table, index, type )
CREATE OR REPLACE FUNCTION index_is_type ( NAME, NAME, NAME )
RETURNS TEXT AS $$
DECLARE
    aname name;
BEGIN
    SELECT am.amname
      FROM pg_catalog.pg_index x
      JOIN pg_catalog.pg_class ct ON ct.oid = x.indrelid
      JOIN pg_catalog.pg_class ci ON ci.oid = x.indexrelid
      JOIN pg_catalog.pg_am am    ON ci.relam = am.oid
     WHERE ct.relname = $1
       AND ci.relname = $2
      INTO aname;

      return is(
          aname, $3,
          'Index ' || quote_ident($2) || ' should be a ' || quote_ident($3) || ' index'
      );
END;
$$ LANGUAGE plpgsql;

-- index_is_type( index, type )
CREATE OR REPLACE FUNCTION index_is_type ( NAME, NAME )
RETURNS TEXT AS $$
DECLARE
    aname name;
BEGIN
    SELECT am.amname
      FROM pg_catalog.pg_index x
      JOIN pg_catalog.pg_class ci ON ci.oid = x.indexrelid
      JOIN pg_catalog.pg_am am    ON ci.relam = am.oid
     WHERE ci.relname = $1
      INTO aname;

      return is(
          aname, $2,
          'Index ' || quote_ident($1) || ' should be a ' || quote_ident($2) || ' index'
      );
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION _trig ( NAME, NAME, NAME )
RETURNS BOOLEAN AS $$
    SELECT EXISTS(
        SELECT true
          FROM pg_catalog.pg_trigger t
          JOIN pg_catalog.pg_class c     ON c.oid = t.tgrelid
          JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
         WHERE n.nspname = $1
           AND c.relname = $2
           AND t.tgname  = $3
    );
$$ LANGUAGE SQL;

CREATE OR REPLACE FUNCTION _trig ( NAME, NAME )
RETURNS BOOLEAN AS $$
    SELECT EXISTS(
        SELECT true
          FROM pg_catalog.pg_trigger t
          JOIN pg_catalog.pg_class c     ON c.oid = t.tgrelid
         WHERE c.relname = $1
           AND t.tgname  = $2
    );
$$ LANGUAGE SQL;

-- has_trigger( schema, table, trigger, description )
CREATE OR REPLACE FUNCTION has_trigger ( NAME, NAME, NAME, TEXT )
RETURNS TEXT AS $$
    SELECT ok( _trig($1, $2, $3), $4);
$$ LANGUAGE SQL;

-- has_trigger( schema, table, trigger )
CREATE OR REPLACE FUNCTION has_trigger ( NAME, NAME, NAME )
RETURNS TEXT AS $$
    SELECT has_trigger(
        $1, $2, $3,
        'Table ' || quote_ident($1) || '.' || quote_ident($2) || ' should have trigger ' || quote_ident($3)
    );
$$ LANGUAGE sql;

-- has_trigger( table, trigger, description )
CREATE OR REPLACE FUNCTION has_trigger ( NAME, NAME, TEXT )
RETURNS TEXT AS $$
    SELECT ok( _trig($1, $2), $3);
$$ LANGUAGE sql;

-- has_trigger( table, trigger )
CREATE OR REPLACE FUNCTION has_trigger ( NAME, NAME )
RETURNS TEXT AS $$
    SELECT ok( _trig($1, $2), 'Table ' || quote_ident($1) || ' should have trigger ' || quote_ident($2));
$$ LANGUAGE SQL;

-- hasnt_trigger( schema, table, trigger, description )
CREATE OR REPLACE FUNCTION hasnt_trigger ( NAME, NAME, NAME, TEXT )
RETURNS TEXT AS $$
    SELECT ok( NOT _trig($1, $2, $3), $4);
$$ LANGUAGE SQL;

-- hasnt_trigger( schema, table, trigger )
CREATE OR REPLACE FUNCTION hasnt_trigger ( NAME, NAME, NAME )
RETURNS TEXT AS $$
    SELECT ok(
        NOT _trig($1, $2, $3),
        'Table ' || quote_ident($1) || '.' || quote_ident($2) || ' should not have trigger ' || quote_ident($3)
    );
$$ LANGUAGE sql;

-- hasnt_trigger( table, trigger, description )
CREATE OR REPLACE FUNCTION hasnt_trigger ( NAME, NAME, TEXT )
RETURNS TEXT AS $$
    SELECT ok( NOT _trig($1, $2), $3);
$$ LANGUAGE sql;

-- hasnt_trigger( table, trigger )
CREATE OR REPLACE FUNCTION hasnt_trigger ( NAME, NAME )
RETURNS TEXT AS $$
    SELECT ok( NOT _trig($1, $2), 'Table ' || quote_ident($1) || ' should not have trigger ' || quote_ident($2));
$$ LANGUAGE SQL;

-- trigger_is( schema, table, trigger, schema, function, description )
CREATE OR REPLACE FUNCTION trigger_is ( NAME, NAME, NAME, NAME, NAME, text )
RETURNS TEXT AS $$
DECLARE
    pname text;
BEGIN
    SELECT quote_ident(ni.nspname) || '.' || quote_ident(p.proname)
      FROM pg_catalog.pg_trigger t
      JOIN pg_catalog.pg_class ct     ON ct.oid = t.tgrelid
      JOIN pg_catalog.pg_namespace nt ON nt.oid = ct.relnamespace
      JOIN pg_catalog.pg_proc p       ON p.oid = t.tgfoid
      JOIN pg_catalog.pg_namespace ni ON ni.oid = p.pronamespace
     WHERE nt.nspname = $1
       AND ct.relname = $2
       AND t.tgname   = $3
      INTO pname;

    RETURN is( pname, quote_ident($4) || '.' || quote_ident($5), $6 );
END;
$$ LANGUAGE plpgsql;

-- trigger_is( schema, table, trigger, schema, function )
CREATE OR REPLACE FUNCTION trigger_is ( NAME, NAME, NAME, NAME, NAME )
RETURNS TEXT AS $$
    SELECT trigger_is(
        $1, $2, $3, $4, $5,
        'Trigger ' || quote_ident($3) || ' should call ' || quote_ident($4) || '.' || quote_ident($5) || '()'
    );
$$ LANGUAGE sql;

-- trigger_is( table, trigger, function, description )
CREATE OR REPLACE FUNCTION trigger_is ( NAME, NAME, NAME, text )
RETURNS TEXT AS $$
DECLARE
    pname text;
BEGIN
    SELECT p.proname
      FROM pg_catalog.pg_trigger t
      JOIN pg_catalog.pg_class ct ON ct.oid = t.tgrelid
      JOIN pg_catalog.pg_proc p   ON p.oid = t.tgfoid
     WHERE ct.relname = $1
       AND t.tgname   = $2
       AND pg_catalog.pg_table_is_visible(ct.oid)
      INTO pname;

    RETURN is( pname, $3::text, $4 );
END;
$$ LANGUAGE plpgsql;

-- trigger_is( table, trigger, function )
CREATE OR REPLACE FUNCTION trigger_is ( NAME, NAME, NAME )
RETURNS TEXT AS $$
    SELECT trigger_is(
        $1, $2, $3,
        'Trigger ' || quote_ident($2) || ' should call ' || quote_ident($3) || '()'
    );
$$ LANGUAGE sql;

-- has_schema( schema, description )
CREATE OR REPLACE FUNCTION has_schema( NAME, TEXT )
RETURNS TEXT AS $$
    SELECT ok(
        EXISTS(
            SELECT true
              FROM pg_catalog.pg_namespace
             WHERE nspname = $1
        ), $2
    );
$$ LANGUAGE sql;

-- has_schema( schema )
CREATE OR REPLACE FUNCTION has_schema( NAME )
RETURNS TEXT AS $$
    SELECT has_schema( $1, 'Schema ' || quote_ident($1) || ' should exist' );
$$ LANGUAGE sql;

-- hasnt_schema( schema, description )
CREATE OR REPLACE FUNCTION hasnt_schema( NAME, TEXT )
RETURNS TEXT AS $$
    SELECT ok(
        NOT EXISTS(
            SELECT true
              FROM pg_catalog.pg_namespace
             WHERE nspname = $1
        ), $2
    );
$$ LANGUAGE sql;

-- hasnt_schema( schema )
CREATE OR REPLACE FUNCTION hasnt_schema( NAME )
RETURNS TEXT AS $$
    SELECT hasnt_schema( $1, 'Schema ' || quote_ident($1) || ' should not exist' );
$$ LANGUAGE sql;

-- has_tablespace( tablespace, location, description )
CREATE OR REPLACE FUNCTION has_tablespace( NAME, TEXT, TEXT )
RETURNS TEXT AS $$
    SELECT ok(
        EXISTS(
            SELECT true
              FROM pg_catalog.pg_tablespace
             WHERE spcname = $1
               AND spclocation = $2
        ), $3
    );
$$ LANGUAGE sql;

-- has_tablespace( tablespace, description )
CREATE OR REPLACE FUNCTION has_tablespace( NAME, TEXT )
RETURNS TEXT AS $$
    SELECT ok(
        EXISTS(
            SELECT true
              FROM pg_catalog.pg_tablespace
             WHERE spcname = $1
        ), $2
    );
$$ LANGUAGE sql;

-- has_tablespace( tablespace )
CREATE OR REPLACE FUNCTION has_tablespace( NAME )
RETURNS TEXT AS $$
    SELECT has_tablespace( $1, 'Tablespace ' || quote_ident($1) || ' should exist' );
$$ LANGUAGE sql;

-- hasnt_tablespace( tablespace, description )
CREATE OR REPLACE FUNCTION hasnt_tablespace( NAME, TEXT )
RETURNS TEXT AS $$
    SELECT ok(
        NOT EXISTS(
            SELECT true
              FROM pg_catalog.pg_tablespace
             WHERE spcname = $1
        ), $2
    );
$$ LANGUAGE sql;

-- hasnt_tablespace( tablespace )
CREATE OR REPLACE FUNCTION hasnt_tablespace( NAME )
RETURNS TEXT AS $$
    SELECT hasnt_tablespace( $1, 'Tablespace ' || quote_ident($1) || ' should not exist' );
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION _has_role( NAME )
RETURNS BOOLEAN AS $$
    SELECT EXISTS(
        SELECT true
          FROM pg_catalog.pg_roles
         WHERE rolname = $1
    );
$$ LANGUAGE sql STRICT;

-- has_role( role, description )
CREATE OR REPLACE FUNCTION has_role( NAME, TEXT )
RETURNS TEXT AS $$
    SELECT ok( _has_role($1), $2 );
$$ LANGUAGE sql;

-- has_role( role )
CREATE OR REPLACE FUNCTION has_role( NAME )
RETURNS TEXT AS $$
    SELECT ok( _has_role($1), 'Role ' || quote_ident($1) || ' should exist' );
$$ LANGUAGE sql;

-- hasnt_role( role, description )
CREATE OR REPLACE FUNCTION hasnt_role( NAME, TEXT )
RETURNS TEXT AS $$
    SELECT ok( NOT _has_role($1), $2 );
$$ LANGUAGE sql;

-- hasnt_role( role )
CREATE OR REPLACE FUNCTION hasnt_role( NAME )
RETURNS TEXT AS $$
    SELECT ok( NOT _has_role($1), 'Role ' || quote_ident($1) || ' should not exist' );
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION _has_user( NAME )
RETURNS BOOLEAN AS $$
    SELECT EXISTS( SELECT true FROM pg_catalog.pg_user WHERE usename = $1);
$$ LANGUAGE sql STRICT;

-- has_user( user, description )
CREATE OR REPLACE FUNCTION has_user( NAME, TEXT )
RETURNS TEXT AS $$
    SELECT ok( _has_user($1), $2 );
$$ LANGUAGE sql;

-- has_user( user )
CREATE OR REPLACE FUNCTION has_user( NAME )
RETURNS TEXT AS $$
    SELECT ok( _has_user( $1 ), 'User ' || quote_ident($1) || ' should exist');
$$ LANGUAGE sql;

-- hasnt_user( user, description )
CREATE OR REPLACE FUNCTION hasnt_user( NAME, TEXT )
RETURNS TEXT AS $$
    SELECT ok( NOT _has_user($1), $2 );
$$ LANGUAGE sql;

-- hasnt_user( user )
CREATE OR REPLACE FUNCTION hasnt_user( NAME )
RETURNS TEXT AS $$
    SELECT ok( NOT _has_user( $1 ), 'User ' || quote_ident($1) || ' should not exist');
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION _is_super( NAME )
RETURNS BOOLEAN AS $$
    SELECT rolsuper
      FROM pg_catalog.pg_roles
     WHERE rolname = $1
$$ LANGUAGE sql STRICT;

-- is_superuser( user, description )
CREATE OR REPLACE FUNCTION is_superuser( NAME, TEXT )
RETURNS TEXT AS $$
DECLARE
    is_super boolean := _is_super($1);
BEGIN
    IF is_super IS NULL THEN
        RETURN fail( $2 ) || E'\n' || diag( '    User ' || quote_ident($1) || ' does not exist') ;
    END IF;
    RETURN ok( is_super, $2 );
END;
$$ LANGUAGE plpgsql;

-- is_superuser( user )
CREATE OR REPLACE FUNCTION is_superuser( NAME )
RETURNS TEXT AS $$
    SELECT is_superuser( $1, 'User ' || quote_ident($1) || ' should be a super user' );
$$ LANGUAGE sql;

-- isnt_superuser( user, description )
CREATE OR REPLACE FUNCTION isnt_superuser( NAME, TEXT )
RETURNS TEXT AS $$
DECLARE
    is_super boolean := _is_super($1);
BEGIN
    IF is_super IS NULL THEN
        RETURN fail( $2 ) || E'\n' || diag( '    User ' || quote_ident($1) || ' does not exist') ;
    END IF;
    RETURN ok( NOT is_super, $2 );
END;
$$ LANGUAGE plpgsql;

-- isnt_superuser( user )
CREATE OR REPLACE FUNCTION isnt_superuser( NAME )
RETURNS TEXT AS $$
    SELECT isnt_superuser( $1, 'User ' || quote_ident($1) || ' should not be a super user' );
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION _has_group( NAME )
RETURNS BOOLEAN AS $$
    SELECT EXISTS(
        SELECT true
          FROM pg_catalog.pg_group
         WHERE groname = $1
    );
$$ LANGUAGE sql STRICT;

-- has_group( group, description )
CREATE OR REPLACE FUNCTION has_group( NAME, TEXT )
RETURNS TEXT AS $$
    SELECT ok( _has_group($1), $2 );
$$ LANGUAGE sql;

-- has_group( group )
CREATE OR REPLACE FUNCTION has_group( NAME )
RETURNS TEXT AS $$
    SELECT ok( _has_group($1), 'Group ' || quote_ident($1) || ' should exist' );
$$ LANGUAGE sql;

-- hasnt_group( group, description )
CREATE OR REPLACE FUNCTION hasnt_group( NAME, TEXT )
RETURNS TEXT AS $$
    SELECT ok( NOT _has_group($1), $2 );
$$ LANGUAGE sql;

-- hasnt_group( group )
CREATE OR REPLACE FUNCTION hasnt_group( NAME )
RETURNS TEXT AS $$
    SELECT ok( NOT _has_group($1), 'Group ' || quote_ident($1) || ' should not exist' );
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION _grolist ( NAME )
RETURNS oid[] AS $$
    SELECT ARRAY(
        SELECT member
          FROM pg_catalog.pg_auth_members m
          JOIN pg_catalog.pg_roles r ON m.roleid = r.oid
         WHERE r.rolname =  $1
    );
$$ LANGUAGE sql;

-- is_member_of( group, user[], description )
CREATE OR REPLACE FUNCTION is_member_of( NAME, NAME[], TEXT )
RETURNS TEXT AS $$
DECLARE
    missing text[];
BEGIN
    IF NOT _has_role($1) THEN
        RETURN fail( $3 ) || E'\n' || diag (
            '    Role ' || quote_ident($1) || ' does not exist'
        );
    END IF;

    SELECT ARRAY(
        SELECT quote_ident($2[i])
          FROM generate_series(1, array_upper($2, 1)) s(i)
          LEFT JOIN pg_catalog.pg_user ON usename = $2[i]
         WHERE usesysid IS NULL
            OR NOT usesysid = ANY ( _grolist($1) )
         ORDER BY s.i
    ) INTO missing;
    IF missing[1] IS NULL THEN
        RETURN ok( true, $3 );
    END IF;
    RETURN ok( false, $3 ) || E'\n' || diag(
        '    Users missing from the ' || quote_ident($1) || E' group:\n        ' ||
        array_to_string( missing, E'\n        ')
    );
END;
$$ LANGUAGE plpgsql;

-- is_member_of( group, user, description )
CREATE OR REPLACE FUNCTION is_member_of( NAME, NAME, TEXT )
RETURNS TEXT AS $$
    SELECT is_member_of( $1, ARRAY[$2], $3 );
$$ LANGUAGE SQL;

-- is_member_of( group, user[] )
CREATE OR REPLACE FUNCTION is_member_of( NAME, NAME[] )
RETURNS TEXT AS $$
    SELECT is_member_of( $1, $2, 'Should have members of group ' || quote_ident($1) );
$$ LANGUAGE SQL;

-- is_member_of( group, user )
CREATE OR REPLACE FUNCTION is_member_of( NAME, NAME )
RETURNS TEXT AS $$
    SELECT is_member_of( $1, ARRAY[$2] );
$$ LANGUAGE SQL;

CREATE OR REPLACE FUNCTION _are ( text, name[], name[], TEXT )
RETURNS TEXT AS $$
DECLARE
    what    ALIAS FOR $1;
    extras  ALIAS FOR $2;
    missing ALIAS FOR $3;
    descr   ALIAS FOR $4;
    msg     TEXT    := '';
    res     BOOLEAN := TRUE;
BEGIN
    IF extras[1] IS NOT NULL THEN
        res = FALSE;
        msg := E'\n' || diag(
            '    Extra ' || what || E':\n        '
            ||  _ident_array_to_string( extras, E'\n        ' )
        );
    END IF;
    IF missing[1] IS NOT NULL THEN
        res = FALSE;
        msg := msg || E'\n' || diag(
            '    Missing ' || what || E':\n        '
            ||  _ident_array_to_string( missing, E'\n        ' )
        );
    END IF;

    RETURN ok(res, descr) || msg;
END;
$$ LANGUAGE plpgsql;

-- tablespaces_are( tablespaces, description )
CREATE OR REPLACE FUNCTION tablespaces_are ( NAME[], TEXT )
RETURNS TEXT AS $$
    SELECT _are(
        'tablespaces',
        ARRAY(
            SELECT spcname
              FROM pg_catalog.pg_tablespace
            EXCEPT
            SELECT $1[i]
              FROM generate_series(1, array_upper($1, 1)) s(i)
        ),
        ARRAY(
            SELECT $1[i]
               FROM generate_series(1, array_upper($1, 1)) s(i)
            EXCEPT
            SELECT spcname
              FROM pg_catalog.pg_tablespace
        ),
        $2
    );
$$ LANGUAGE SQL;

-- tablespaces_are( tablespaces )
CREATE OR REPLACE FUNCTION tablespaces_are ( NAME[] )
RETURNS TEXT AS $$
    SELECT tablespaces_are( $1, 'There should be the correct tablespaces' );
$$ LANGUAGE SQL;

-- schemas_are( schemas, description )
CREATE OR REPLACE FUNCTION schemas_are ( NAME[], TEXT )
RETURNS TEXT AS $$
    SELECT _are(
        'schemas',
        ARRAY(
            SELECT nspname
              FROM pg_catalog.pg_namespace
             WHERE nspname NOT LIKE 'pg_%'
               AND nspname <> 'information_schema'
             EXCEPT
            SELECT $1[i]
              FROM generate_series(1, array_upper($1, 1)) s(i)
        ),
        ARRAY(
            SELECT $1[i]
              FROM generate_series(1, array_upper($1, 1)) s(i)
            EXCEPT
            SELECT nspname
              FROM pg_catalog.pg_namespace
             WHERE nspname NOT LIKE 'pg_%'
               AND nspname <> 'information_schema'
        ),
        $2
    );
$$ LANGUAGE SQL;

-- schemas_are( schemas )
CREATE OR REPLACE FUNCTION schemas_are ( NAME[] )
RETURNS TEXT AS $$
    SELECT schemas_are( $1, 'There should be the correct schemas' );
$$ LANGUAGE SQL;

CREATE OR REPLACE FUNCTION _extras ( CHAR, NAME, NAME[] )
RETURNS NAME[] AS $$
    SELECT ARRAY(
        SELECT c.relname
          FROM pg_catalog.pg_namespace n
          JOIN pg_catalog.pg_class c ON n.oid = c.relnamespace
         WHERE c.relkind = $1
           AND n.nspname = $2
           AND c.relname NOT IN('pg_all_foreign_keys', 'tap_funky', '__tresults___numb_seq', '__tcache___id_seq')
        EXCEPT
        SELECT $3[i]
          FROM generate_series(1, array_upper($3, 1)) s(i)
    );
$$ LANGUAGE SQL;

CREATE OR REPLACE FUNCTION _extras ( CHAR, NAME[] )
RETURNS NAME[] AS $$
    SELECT ARRAY(
        SELECT c.relname
          FROM pg_catalog.pg_namespace n
          JOIN pg_catalog.pg_class c ON n.oid = c.relnamespace
         WHERE pg_catalog.pg_table_is_visible(c.oid)
           AND n.nspname <> 'pg_catalog'
           AND c.relkind = $1
           AND c.relname NOT IN ('__tcache__', '__tresults__', 'pg_all_foreign_keys', 'tap_funky', '__tresults___numb_seq', '__tcache___id_seq')
        EXCEPT
        SELECT $2[i]
          FROM generate_series(1, array_upper($2, 1)) s(i)
    );
$$ LANGUAGE SQL;

CREATE OR REPLACE FUNCTION _missing ( CHAR, NAME, NAME[] )
RETURNS NAME[] AS $$
    SELECT ARRAY(
        SELECT $3[i]
          FROM generate_series(1, array_upper($3, 1)) s(i)
        EXCEPT
        SELECT c.relname
          FROM pg_catalog.pg_namespace n
          JOIN pg_catalog.pg_class c ON n.oid = c.relnamespace
         WHERE c.relkind = $1
           AND n.nspname = $2
    );
$$ LANGUAGE SQL;

CREATE OR REPLACE FUNCTION _missing ( CHAR, NAME[] )
RETURNS NAME[] AS $$
    SELECT ARRAY(
        SELECT $2[i]
          FROM generate_series(1, array_upper($2, 1)) s(i)
        EXCEPT
        SELECT c.relname
          FROM pg_catalog.pg_namespace n
          JOIN pg_catalog.pg_class c ON n.oid = c.relnamespace
         WHERE pg_catalog.pg_table_is_visible(c.oid)
           AND n.nspname NOT IN ('pg_catalog', 'information_schema')
           AND c.relkind = $1
    );
$$ LANGUAGE SQL;

-- tables_are( schema, tables, description )
CREATE OR REPLACE FUNCTION tables_are ( NAME, NAME[], TEXT )
RETURNS TEXT AS $$
    SELECT _are( 'tables', _extras('r', $1, $2), _missing('r', $1, $2), $3);
$$ LANGUAGE SQL;

-- tables_are( tables, description )
CREATE OR REPLACE FUNCTION tables_are ( NAME[], TEXT )
RETURNS TEXT AS $$
    SELECT _are( 'tables', _extras('r', $1), _missing('r', $1), $2);
$$ LANGUAGE SQL;

-- tables_are( schema, tables )
CREATE OR REPLACE FUNCTION tables_are ( NAME, NAME[] )
RETURNS TEXT AS $$
    SELECT _are(
        'tables', _extras('r', $1, $2), _missing('r', $1, $2),
        'Schema ' || quote_ident($1) || ' should have the correct tables'
    );
$$ LANGUAGE SQL;

-- tables_are( tables )
CREATE OR REPLACE FUNCTION tables_are ( NAME[] )
RETURNS TEXT AS $$
    SELECT _are(
        'tables', _extras('r', $1), _missing('r', $1),
        'Search path ' || pg_catalog.current_setting('search_path') || ' should have the correct tables'
    );
$$ LANGUAGE SQL;

-- views_are( schema, views, description )
CREATE OR REPLACE FUNCTION views_are ( NAME, NAME[], TEXT )
RETURNS TEXT AS $$
    SELECT _are( 'views', _extras('v', $1, $2), _missing('v', $1, $2), $3);
$$ LANGUAGE SQL;

-- views_are( views, description )
CREATE OR REPLACE FUNCTION views_are ( NAME[], TEXT )
RETURNS TEXT AS $$
    SELECT _are( 'views', _extras('v', $1), _missing('v', $1), $2);
$$ LANGUAGE SQL;

-- views_are( schema, views )
CREATE OR REPLACE FUNCTION views_are ( NAME, NAME[] )
RETURNS TEXT AS $$
    SELECT _are(
        'views', _extras('v', $1, $2), _missing('v', $1, $2),
        'Schema ' || quote_ident($1) || ' should have the correct views'
    );
$$ LANGUAGE SQL;

-- views_are( views )
CREATE OR REPLACE FUNCTION views_are ( NAME[] )
RETURNS TEXT AS $$
    SELECT _are(
        'views', _extras('v', $1), _missing('v', $1),
        'Search path ' || pg_catalog.current_setting('search_path') || ' should have the correct views'
    );
$$ LANGUAGE SQL;

-- sequences_are( schema, sequences, description )
CREATE OR REPLACE FUNCTION sequences_are ( NAME, NAME[], TEXT )
RETURNS TEXT AS $$
    SELECT _are( 'sequences', _extras('S', $1, $2), _missing('S', $1, $2), $3);
$$ LANGUAGE SQL;

-- sequences_are( sequences, description )
CREATE OR REPLACE FUNCTION sequences_are ( NAME[], TEXT )
RETURNS TEXT AS $$
    SELECT _are( 'sequences', _extras('S', $1), _missing('S', $1), $2);
$$ LANGUAGE SQL;

-- sequences_are( schema, sequences )
CREATE OR REPLACE FUNCTION sequences_are ( NAME, NAME[] )
RETURNS TEXT AS $$
    SELECT _are(
        'sequences', _extras('S', $1, $2), _missing('S', $1, $2),
        'Schema ' || quote_ident($1) || ' should have the correct sequences'
    );
$$ LANGUAGE SQL;

-- sequences_are( sequences )
CREATE OR REPLACE FUNCTION sequences_are ( NAME[] )
RETURNS TEXT AS $$
    SELECT _are(
        'sequences', _extras('S', $1), _missing('S', $1),
        'Search path ' || pg_catalog.current_setting('search_path') || ' should have the correct sequences'
    );
$$ LANGUAGE SQL;

-- functions_are( schema, functions[], description )
CREATE OR REPLACE FUNCTION functions_are ( NAME, NAME[], TEXT )
RETURNS TEXT AS $$
    SELECT _are(
        'functions',
        ARRAY(
            SELECT name FROM tap_funky WHERE schema = $1
            EXCEPT
            SELECT $2[i]
              FROM generate_series(1, array_upper($2, 1)) s(i)
        ),
        ARRAY(
            SELECT $2[i]
               FROM generate_series(1, array_upper($2, 1)) s(i)
            EXCEPT
            SELECT name FROM tap_funky WHERE schema = $1
        ),
        $3
    );
$$ LANGUAGE SQL;

-- functions_are( schema, functions[] )
CREATE OR REPLACE FUNCTION functions_are ( NAME, NAME[] )
RETURNS TEXT AS $$
    SELECT functions_are( $1, $2, 'Schema ' || quote_ident($1) || ' should have the correct functions' );
$$ LANGUAGE SQL;

-- functions_are( functions[], description )
CREATE OR REPLACE FUNCTION functions_are ( NAME[], TEXT )
RETURNS TEXT AS $$
    SELECT _are(
        'functions',
        ARRAY(
            SELECT name FROM tap_funky WHERE is_visible
            AND schema NOT IN ('pg_catalog', 'information_schema')
            EXCEPT
            SELECT $1[i]
              FROM generate_series(1, array_upper($1, 1)) s(i)
        ),
        ARRAY(
            SELECT $1[i]
               FROM generate_series(1, array_upper($1, 1)) s(i)
            EXCEPT
            SELECT name FROM tap_funky WHERE is_visible
            AND schema NOT IN ('pg_catalog', 'information_schema')
        ),
        $2
    );
$$ LANGUAGE SQL;

-- functions_are( functions[] )
CREATE OR REPLACE FUNCTION functions_are ( NAME[] )
RETURNS TEXT AS $$
    SELECT functions_are( $1, 'Search path ' || pg_catalog.current_setting('search_path') || ' should have the correct functions' );
$$ LANGUAGE SQL;

-- indexes_are( schema, table, indexes[], description )
CREATE OR REPLACE FUNCTION indexes_are( NAME, NAME, NAME[], TEXT )
RETURNS TEXT AS $$
    SELECT _are(
        'indexes',
        ARRAY(
            SELECT ci.relname
              FROM pg_catalog.pg_index x
              JOIN pg_catalog.pg_class ct    ON ct.oid = x.indrelid
              JOIN pg_catalog.pg_class ci    ON ci.oid = x.indexrelid
              JOIN pg_catalog.pg_namespace n ON n.oid = ct.relnamespace
             WHERE ct.relname = $2
               AND n.nspname  = $1
            EXCEPT
            SELECT $3[i]
              FROM generate_series(1, array_upper($3, 1)) s(i)
        ),
        ARRAY(
            SELECT $3[i]
              FROM generate_series(1, array_upper($3, 1)) s(i)
            EXCEPT
            SELECT ci.relname
              FROM pg_catalog.pg_index x
              JOIN pg_catalog.pg_class ct    ON ct.oid = x.indrelid
              JOIN pg_catalog.pg_class ci    ON ci.oid = x.indexrelid
              JOIN pg_catalog.pg_namespace n ON n.oid = ct.relnamespace
             WHERE ct.relname = $2
               AND n.nspname  = $1
        ),
        $4
    );
$$ LANGUAGE SQL;

-- indexes_are( schema, table, indexes[] )
CREATE OR REPLACE FUNCTION indexes_are( NAME, NAME, NAME[] )
RETURNS TEXT AS $$
    SELECT indexes_are( $1, $2, $3, 'Table ' || quote_ident($1) || '.' || quote_ident($2) || ' should have the correct indexes' );
$$ LANGUAGE SQL;

-- indexes_are( table, indexes[], description )
CREATE OR REPLACE FUNCTION indexes_are( NAME, NAME[], TEXT )
RETURNS TEXT AS $$
    SELECT _are(
        'indexes',
        ARRAY(
            SELECT ci.relname
              FROM pg_catalog.pg_index x
              JOIN pg_catalog.pg_class ct ON ct.oid = x.indrelid
              JOIN pg_catalog.pg_class ci ON ci.oid = x.indexrelid
              JOIN pg_catalog.pg_namespace n ON n.oid = ct.relnamespace
             WHERE ct.relname = $1
               AND pg_catalog.pg_table_is_visible(ct.oid)
               AND n.nspname NOT IN ('pg_catalog', 'information_schema')
            EXCEPT
            SELECT $2[i]
              FROM generate_series(1, array_upper($2, 1)) s(i)
        ),
        ARRAY(
            SELECT $2[i]
              FROM generate_series(1, array_upper($2, 1)) s(i)
            EXCEPT
            SELECT ci.relname
              FROM pg_catalog.pg_index x
              JOIN pg_catalog.pg_class ct ON ct.oid = x.indrelid
              JOIN pg_catalog.pg_class ci ON ci.oid = x.indexrelid
              JOIN pg_catalog.pg_namespace n ON n.oid = ct.relnamespace
             WHERE ct.relname = $1
               AND pg_catalog.pg_table_is_visible(ct.oid)
               AND n.nspname NOT IN ('pg_catalog', 'information_schema')
        ),
        $3
    );
$$ LANGUAGE SQL;

-- indexes_are( table, indexes[] )
CREATE OR REPLACE FUNCTION indexes_are( NAME, NAME[] )
RETURNS TEXT AS $$
    SELECT indexes_are( $1, $2, 'Table ' || quote_ident($1) || ' should have the correct indexes' );
$$ LANGUAGE SQL;

-- users_are( users[], description )
CREATE OR REPLACE FUNCTION users_are( NAME[], TEXT )
RETURNS TEXT AS $$
    SELECT _are(
        'users',
        ARRAY(
            SELECT usename
              FROM pg_catalog.pg_user
            EXCEPT
            SELECT $1[i]
              FROM generate_series(1, array_upper($1, 1)) s(i)
        ),
        ARRAY(
            SELECT $1[i]
              FROM generate_series(1, array_upper($1, 1)) s(i)
            EXCEPT
            SELECT usename
              FROM pg_catalog.pg_user
        ),
        $2
    );
$$ LANGUAGE SQL;

-- users_are( users[] )
CREATE OR REPLACE FUNCTION users_are( NAME[] )
RETURNS TEXT AS $$
    SELECT users_are( $1, 'There should be the correct users' );
$$ LANGUAGE SQL;

-- groups_are( groups[], description )
CREATE OR REPLACE FUNCTION groups_are( NAME[], TEXT )
RETURNS TEXT AS $$
    SELECT _are(
        'groups',
        ARRAY(
            SELECT groname
              FROM pg_catalog.pg_group
            EXCEPT
            SELECT $1[i]
              FROM generate_series(1, array_upper($1, 1)) s(i)
        ),
        ARRAY(
            SELECT $1[i]
              FROM generate_series(1, array_upper($1, 1)) s(i)
            EXCEPT
            SELECT groname
              FROM pg_catalog.pg_group
        ),
        $2
    );
$$ LANGUAGE SQL;

-- groups_are( groups[] )
CREATE OR REPLACE FUNCTION groups_are( NAME[] )
RETURNS TEXT AS $$
    SELECT groups_are( $1, 'There should be the correct groups' );
$$ LANGUAGE SQL;

-- languages_are( languages[], description )
CREATE OR REPLACE FUNCTION languages_are( NAME[], TEXT )
RETURNS TEXT AS $$
    SELECT _are(
        'languages',
        ARRAY(
            SELECT lanname
              FROM pg_catalog.pg_language
             WHERE lanispl
            EXCEPT
            SELECT $1[i]
              FROM generate_series(1, array_upper($1, 1)) s(i)
        ),
        ARRAY(
            SELECT $1[i]
              FROM generate_series(1, array_upper($1, 1)) s(i)
            EXCEPT
            SELECT lanname
              FROM pg_catalog.pg_language
             WHERE lanispl
        ),
        $2
    );
$$ LANGUAGE SQL;

-- languages_are( languages[] )
CREATE OR REPLACE FUNCTION languages_are( NAME[] )
RETURNS TEXT AS $$
    SELECT languages_are( $1, 'There should be the correct procedural languages' );
$$ LANGUAGE SQL;

CREATE OR REPLACE FUNCTION opclasses_are ( NAME, NAME[], TEXT )
RETURNS TEXT AS $$
    SELECT _are(
        'operator classes',
        ARRAY(
            SELECT oc.opcname
              FROM pg_catalog.pg_opclass oc
              JOIN pg_catalog.pg_namespace n ON oc.opcnamespace = n.oid
             WHERE n.nspname  = $1
            EXCEPT
            SELECT $2[i]
              FROM generate_series(1, array_upper($2, 1)) s(i)
        ),
        ARRAY(
            SELECT $2[i]
               FROM generate_series(1, array_upper($2, 1)) s(i)
            EXCEPT
            SELECT oc.opcname
              FROM pg_catalog.pg_opclass oc
              JOIN pg_catalog.pg_namespace n ON oc.opcnamespace = n.oid
             WHERE n.nspname  = $1
        ),
        $3
    );
$$ LANGUAGE SQL;

-- opclasses_are( schema, opclasses[] )
CREATE OR REPLACE FUNCTION opclasses_are ( NAME, NAME[] )
RETURNS TEXT AS $$
    SELECT opclasses_are( $1, $2, 'Schema ' || quote_ident($1) || ' should have the correct operator classes' );
$$ LANGUAGE SQL;

-- opclasses_are( opclasses[], description )
CREATE OR REPLACE FUNCTION opclasses_are ( NAME[], TEXT )
RETURNS TEXT AS $$
    SELECT _are(
        'operator classes',
        ARRAY(
            SELECT oc.opcname
              FROM pg_catalog.pg_opclass oc
              JOIN pg_catalog.pg_namespace n ON oc.opcnamespace = n.oid
               AND n.nspname NOT IN ('pg_catalog', 'information_schema')
               AND pg_catalog.pg_opclass_is_visible(oc.oid)
            EXCEPT
            SELECT $1[i]
              FROM generate_series(1, array_upper($1, 1)) s(i)
        ),
        ARRAY(
            SELECT $1[i]
               FROM generate_series(1, array_upper($1, 1)) s(i)
            EXCEPT
            SELECT oc.opcname
              FROM pg_catalog.pg_opclass oc
              JOIN pg_catalog.pg_namespace n ON oc.opcnamespace = n.oid
               AND n.nspname NOT IN ('pg_catalog', 'information_schema')
               AND pg_catalog.pg_opclass_is_visible(oc.oid)
        ),
        $2
    );
$$ LANGUAGE SQL;

-- opclasses_are( opclasses[] )
CREATE OR REPLACE FUNCTION opclasses_are ( NAME[] )
RETURNS TEXT AS $$
    SELECT opclasses_are( $1, 'Search path ' || pg_catalog.current_setting('search_path') || ' should have the correct operator classes' );
$$ LANGUAGE SQL;

-- rules_are( schema, table, rules[], description )
CREATE OR REPLACE FUNCTION rules_are( NAME, NAME, NAME[], TEXT )
RETURNS TEXT AS $$
    SELECT _are(
        'rules',
        ARRAY(
            SELECT r.rulename
              FROM pg_catalog.pg_rewrite r
              JOIN pg_catalog.pg_class c     ON c.oid = r.ev_class
              JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid
             WHERE c.relname = $2
               AND n.nspname = $1
            EXCEPT
            SELECT $3[i]
              FROM generate_series(1, array_upper($3, 1)) s(i)
        ),
        ARRAY(
            SELECT $3[i]
              FROM generate_series(1, array_upper($3, 1)) s(i)
            EXCEPT
            SELECT r.rulename
              FROM pg_catalog.pg_rewrite r
              JOIN pg_catalog.pg_class c     ON c.oid = r.ev_class
              JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid
             WHERE c.relname = $2
               AND n.nspname = $1
        ),
        $4
    );
$$ LANGUAGE SQL;

-- rules_are( schema, table, rules[] )
CREATE OR REPLACE FUNCTION rules_are( NAME, NAME, NAME[] )
RETURNS TEXT AS $$
    SELECT rules_are( $1, $2, $3, 'Relation ' || quote_ident($1) || '.' || quote_ident($2) || ' should have the correct rules' );
$$ LANGUAGE SQL;

-- rules_are( table, rules[], description )
CREATE OR REPLACE FUNCTION rules_are( NAME, NAME[], TEXT )
RETURNS TEXT AS $$
    SELECT _are(
        'rules',
        ARRAY(
            SELECT r.rulename
              FROM pg_catalog.pg_rewrite r
              JOIN pg_catalog.pg_class c     ON c.oid = r.ev_class
              JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid
             WHERE c.relname = $1
               AND n.nspname NOT IN ('pg_catalog', 'information_schema')
               AND pg_catalog.pg_table_is_visible(c.oid)
            EXCEPT
            SELECT $2[i]
              FROM generate_series(1, array_upper($2, 1)) s(i)
        ),
        ARRAY(
            SELECT $2[i]
              FROM generate_series(1, array_upper($2, 1)) s(i)
            EXCEPT
            SELECT r.rulename
              FROM pg_catalog.pg_rewrite r
              JOIN pg_catalog.pg_class c     ON c.oid = r.ev_class
              JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid
               AND c.relname = $1
               AND n.nspname NOT IN ('pg_catalog', 'information_schema')
               AND pg_catalog.pg_table_is_visible(c.oid)
        ),
        $3
    );
$$ LANGUAGE SQL;

-- rules_are( table, rules[] )
CREATE OR REPLACE FUNCTION rules_are( NAME, NAME[] )
RETURNS TEXT AS $$
    SELECT rules_are( $1, $2, 'Relation ' || quote_ident($1) || ' should have the correct rules' );
$$ LANGUAGE SQL;

CREATE OR REPLACE FUNCTION _is_instead( NAME, NAME, NAME )
RETURNS BOOLEAN AS $$
    SELECT r.is_instead
      FROM pg_catalog.pg_rewrite r
      JOIN pg_catalog.pg_class c     ON c.oid = r.ev_class
      JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid
     WHERE r.rulename = $3
       AND c.relname  = $2
       AND n.nspname  = $1
$$ LANGUAGE SQL;

CREATE OR REPLACE FUNCTION _is_instead( NAME, NAME )
RETURNS BOOLEAN AS $$
    SELECT r.is_instead
      FROM pg_catalog.pg_rewrite r
      JOIN pg_catalog.pg_class c     ON c.oid = r.ev_class
     WHERE r.rulename = $2
       AND c.relname  = $1
       AND pg_catalog.pg_table_is_visible(c.oid)
$$ LANGUAGE SQL;

-- has_rule( schema, table, rule, description )
CREATE OR REPLACE FUNCTION has_rule( NAME, NAME, NAME, TEXT )
RETURNS TEXT AS $$
    SELECT ok( _is_instead($1, $2, $3) IS NOT NULL, $4 );
$$ LANGUAGE SQL;

-- has_rule( schema, table, rule )
CREATE OR REPLACE FUNCTION has_rule( NAME, NAME, NAME )
RETURNS TEXT AS $$
    SELECT ok( _is_instead($1, $2, $3) IS NOT NULL, 'Relation ' || quote_ident($1) || '.' || quote_ident($2) || ' should have rule ' || quote_ident($3) );
$$ LANGUAGE SQL;

-- has_rule( table, rule, description )
CREATE OR REPLACE FUNCTION has_rule( NAME, NAME, TEXT )
RETURNS TEXT AS $$
    SELECT ok( _is_instead($1, $2) IS NOT NULL, $3 );
$$ LANGUAGE SQL;

-- has_rule( table, rule )
CREATE OR REPLACE FUNCTION has_rule( NAME, NAME )
RETURNS TEXT AS $$
    SELECT ok( _is_instead($1, $2) IS NOT NULL, 'Relation ' || quote_ident($1) || ' should have rule ' || quote_ident($2) );
$$ LANGUAGE SQL;

-- hasnt_rule( schema, table, rule, description )
CREATE OR REPLACE FUNCTION hasnt_rule( NAME, NAME, NAME, TEXT )
RETURNS TEXT AS $$
    SELECT ok( _is_instead($1, $2, $3) IS NULL, $4 );
$$ LANGUAGE SQL;

-- hasnt_rule( schema, table, rule )
CREATE OR REPLACE FUNCTION hasnt_rule( NAME, NAME, NAME )
RETURNS TEXT AS $$
    SELECT ok( _is_instead($1, $2, $3) IS NULL, 'Relation ' || quote_ident($1) || '.' || quote_ident($2) || ' should not have rule ' || quote_ident($3) );
$$ LANGUAGE SQL;

-- hasnt_rule( table, rule, description )
CREATE OR REPLACE FUNCTION hasnt_rule( NAME, NAME, TEXT )
RETURNS TEXT AS $$
    SELECT ok( _is_instead($1, $2) IS NULL, $3 );
$$ LANGUAGE SQL;

-- hasnt_rule( table, rule )
CREATE OR REPLACE FUNCTION hasnt_rule( NAME, NAME )
RETURNS TEXT AS $$
    SELECT ok( _is_instead($1, $2) IS NULL, 'Relation ' || quote_ident($1) || ' should not have rule ' || quote_ident($2) );
$$ LANGUAGE SQL;

-- rule_is_instead( schema, table, rule, description )
CREATE OR REPLACE FUNCTION rule_is_instead( NAME, NAME, NAME, TEXT )
RETURNS TEXT AS $$
DECLARE
    is_it boolean := _is_instead($1, $2, $3);
BEGIN
    IF is_it IS NOT NULL THEN RETURN ok( is_it, $4 ); END IF;
    RETURN ok( FALSE, $4 ) || E'\n' || diag(
        '    Rule ' || quote_ident($3) || ' does not exist'
    );
END;
$$ LANGUAGE plpgsql;

-- rule_is_instead( schema, table, rule )
CREATE OR REPLACE FUNCTION rule_is_instead( NAME, NAME, NAME )
RETURNS TEXT AS $$
    SELECT rule_is_instead( $1, $2, $3, 'Rule ' || quote_ident($3) || ' on relation ' || quote_ident($1) || '.' || quote_ident($2) || ' should be an INSTEAD rule' );
$$ LANGUAGE SQL;

-- rule_is_instead( table, rule, description )
CREATE OR REPLACE FUNCTION rule_is_instead( NAME, NAME, TEXT )
RETURNS TEXT AS $$
DECLARE
    is_it boolean := _is_instead($1, $2);
BEGIN
    IF is_it IS NOT NULL THEN RETURN ok( is_it, $3 ); END IF;
    RETURN ok( FALSE, $3 ) || E'\n' || diag(
        '    Rule ' || quote_ident($2) || ' does not exist'
    );
END;
$$ LANGUAGE plpgsql;

-- rule_is_instead( table, rule )
CREATE OR REPLACE FUNCTION rule_is_instead( NAME, NAME )
RETURNS TEXT AS $$
    SELECT rule_is_instead($1, $2, 'Rule ' || quote_ident($2) || ' on relation ' || quote_ident($1) || ' should be an INSTEAD rule' );
$$ LANGUAGE SQL;

CREATE OR REPLACE FUNCTION _expand_on( char )
RETURNS text AS $$
   SELECT CASE $1
          WHEN '1' THEN 'SELECT'
          WHEN '2' THEN 'UPDATE'
          WHEN '3' THEN 'INSERT'
          WHEN '4' THEN 'DELETE'
          ELSE          'UNKNOWN' END
$$ LANGUAGE SQL IMMUTABLE;

CREATE OR REPLACE FUNCTION _contract_on( TEXT )
RETURNS "char" AS $$
   SELECT CASE substring(LOWER($1) FROM 1 FOR 1)
          WHEN 's' THEN '1'::"char"
          WHEN 'u' THEN '2'::"char"
          WHEN 'i' THEN '3'::"char"
          WHEN 'd' THEN '4'::"char"
          ELSE          '0'::"char" END
$$ LANGUAGE SQL IMMUTABLE;

CREATE OR REPLACE FUNCTION _rule_on( NAME, NAME, NAME )
RETURNS "char" AS $$
    SELECT r.ev_type
      FROM pg_catalog.pg_rewrite r
      JOIN pg_catalog.pg_class c     ON c.oid = r.ev_class
      JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid
     WHERE r.rulename = $3
       AND c.relname  = $2
       AND n.nspname  = $1
$$ LANGUAGE SQL;

CREATE OR REPLACE FUNCTION _rule_on( NAME, NAME )
RETURNS "char" AS $$
    SELECT r.ev_type
      FROM pg_catalog.pg_rewrite r
      JOIN pg_catalog.pg_class c     ON c.oid = r.ev_class
     WHERE r.rulename = $2
       AND c.relname  = $1
$$ LANGUAGE SQL;

-- rule_is_on( schema, table, rule, event, description )
CREATE OR REPLACE FUNCTION rule_is_on( NAME, NAME, NAME, TEXT, TEXT )
RETURNS TEXT AS $$
DECLARE
    want char := _contract_on($4);
    have char := _rule_on($1, $2, $3);
BEGIN
    IF have IS NOT NULL THEN
        RETURN is( _expand_on(have), _expand_on(want), $5 );
    END IF;

    RETURN ok( false, $5 ) || E'\n' || diag(
        '    Rule ' || quote_ident($3) || ' does not exist on '
        || quote_ident($1) || '.' || quote_ident($2)
    );
END;
$$ LANGUAGE plpgsql;

-- rule_is_on( schema, table, rule, event )
CREATE OR REPLACE FUNCTION rule_is_on( NAME, NAME, NAME, TEXT )
RETURNS TEXT AS $$
    SELECT rule_is_on(
        $1, $2, $3, $4,
        'Rule ' || quote_ident($3) || ' should be on ' || _expand_on(_contract_on($4)::char)
        || ' to ' || quote_ident($1) || '.' || quote_ident($2)
    );
$$ LANGUAGE SQL;

-- rule_is_on( table, rule, event, description )
CREATE OR REPLACE FUNCTION rule_is_on( NAME, NAME, TEXT, TEXT )
RETURNS TEXT AS $$
DECLARE
    want char := _contract_on($3);
    have char := _rule_on($1, $2);
BEGIN
    IF have IS NOT NULL THEN
        RETURN is( _expand_on(have), _expand_on(want), $4 );
    END IF;

    RETURN ok( false, $4 ) || E'\n' || diag(
        '    Rule ' || quote_ident($2) || ' does not exist on '
        || quote_ident($1)
    );
END;
$$ LANGUAGE plpgsql;

-- rule_is_on( table, rule, event )
CREATE OR REPLACE FUNCTION rule_is_on( NAME, NAME, TEXT )
RETURNS TEXT AS $$
    SELECT rule_is_on(
        $1, $2, $3,
        'Rule ' || quote_ident($2) || ' should be on '
        || _expand_on(_contract_on($3)::char) || ' to ' || quote_ident($1)
    );
$$ LANGUAGE SQL;

CREATE OR REPLACE FUNCTION check_test( TEXT, BOOLEAN, TEXT, TEXT, TEXT, BOOLEAN )
RETURNS SETOF TEXT AS $$
DECLARE
    tnumb   INTEGER;
    aok     BOOLEAN;
    adescr  TEXT;
    res     BOOLEAN;
    descr   TEXT;
    adiag   TEXT;
    have    ALIAS FOR $1;
    eok     ALIAS FOR $2;
    name    ALIAS FOR $3;
    edescr  ALIAS FOR $4;
    ediag   ALIAS FOR $5;
    matchit ALIAS FOR $6;
BEGIN
    -- What test was it that just ran?
    tnumb := currval('__tresults___numb_seq');

    -- Fetch the results.
    EXECUTE 'SELECT aok, descr FROM __tresults__ WHERE numb = ' || tnumb
       INTO aok, adescr;

    -- Now delete those results.
    EXECUTE 'DELETE FROM __tresults__ WHERE numb = ' || tnumb;
    EXECUTE 'ALTER SEQUENCE __tresults___numb_seq RESTART WITH ' || tnumb;

    -- Set up the description.
    descr := coalesce( name || ' ', 'Test ' ) || 'should ';

    -- So, did the test pass?
    RETURN NEXT is(
        aok,
        eok,
        descr || CASE eok WHEN true then 'pass' ELSE 'fail' END
    );

    -- Was the description as expected?
    IF edescr IS NOT NULL THEN
        RETURN NEXT is(
            adescr,
            edescr,
            descr || 'have the proper description'
        );
    END IF;

    -- Were the diagnostics as expected?
    IF ediag IS NOT NULL THEN
        -- Remove ok and the test number.
        adiag := substring(
            have
            FROM CASE WHEN aok THEN 4 ELSE 9 END + char_length(tnumb::text)
        );

        -- Remove the description, if there is one.
        IF adescr <> '' THEN
            adiag := substring( adiag FROM 3 + char_length( diag( adescr ) ) );
        END IF;

        -- Remove failure message from ok().
        IF NOT aok THEN
           adiag := substring(
               adiag
               FROM 14 + char_length(tnumb::text)
                       + CASE adescr WHEN '' THEN 3 ELSE 3 + char_length( diag( adescr ) ) END
           );
        END IF;

        -- Remove the #s.
        adiag := replace( substring(adiag from 3), E'\n# ', E'\n' );

        -- Now compare the diagnostics.
        IF matchit THEN
            RETURN NEXT matches(
                adiag,
                ediag,
                descr || 'have the proper diagnostics'
            );
        ELSE
            RETURN NEXT is(
                adiag,
                ediag,
                descr || 'have the proper diagnostics'
            );
        END IF;
    END IF;

    -- And we're done
    RETURN;
END;
$$ LANGUAGE plpgsql;

-- check_test( test_output, pass, name, description, diag )
CREATE OR REPLACE FUNCTION check_test( TEXT, BOOLEAN, TEXT, TEXT, TEXT )
RETURNS SETOF TEXT AS $$
    SELECT * FROM check_test( $1, $2, $3, $4, $5, FALSE );
$$ LANGUAGE sql;

-- check_test( test_output, pass, name, description )
CREATE OR REPLACE FUNCTION check_test( TEXT, BOOLEAN, TEXT, TEXT )
RETURNS SETOF TEXT AS $$
    SELECT * FROM check_test( $1, $2, $3, $4, NULL, FALSE );
$$ LANGUAGE sql;

-- check_test( test_output, pass, name )
CREATE OR REPLACE FUNCTION check_test( TEXT, BOOLEAN, TEXT )
RETURNS SETOF TEXT AS $$
    SELECT * FROM check_test( $1, $2, $3, NULL, NULL, FALSE );
$$ LANGUAGE sql;

-- check_test( test_output, pass )
CREATE OR REPLACE FUNCTION check_test( TEXT, BOOLEAN )
RETURNS SETOF TEXT AS $$
    SELECT * FROM check_test( $1, $2, NULL, NULL, NULL, FALSE );
$$ LANGUAGE sql;


CREATE OR REPLACE FUNCTION diag_test_name(TEXT)
RETURNS TEXT AS $$
    SELECT diag($1 || '()');
$$ LANGUAGE SQL;

CREATE OR REPLACE FUNCTION roles_are( NAME[], TEXT )
RETURNS TEXT AS $$
    SELECT _are(
        'roles',
        ARRAY(
            SELECT rolname
              FROM pg_catalog.pg_roles
            EXCEPT
            SELECT $1[i]
              FROM generate_series(1, array_upper($1, 1)) s(i)
        ),
        ARRAY(
            SELECT $1[i]
              FROM generate_series(1, array_upper($1, 1)) s(i)
            EXCEPT
            SELECT rolname
              FROM pg_catalog.pg_roles
        ),
        $2
    );
$$ LANGUAGE SQL;

-- roles_are( roles[] )
CREATE OR REPLACE FUNCTION roles_are( NAME[] )
RETURNS TEXT AS $$
    SELECT roles_are( $1, 'There should be the correct roles' );
$$ LANGUAGE SQL;

CREATE OR REPLACE FUNCTION _types_are ( NAME, NAME[], TEXT, CHAR[] )
RETURNS TEXT AS $$
    SELECT _are(
        'types',
        ARRAY(
            SELECT t.typname
              FROM pg_catalog.pg_type t
              LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace
             WHERE (
                     t.typrelid = 0
                 OR (SELECT c.relkind = 'c' FROM pg_catalog.pg_class c WHERE c.oid = t.typrelid)
             )
               AND NOT EXISTS(SELECT 1 FROM pg_catalog.pg_type el WHERE el.oid = t.typelem AND el.typarray = t.oid)
               AND n.nspname = $1
               AND t.typtype = ANY( COALESCE($4, ARRAY['b', 'c', 'd', 'p', 'e']) )
            EXCEPT
            SELECT $2[i]
              FROM generate_series(1, array_upper($2, 1)) s(i)
        ),
        ARRAY(
            SELECT $2[i]
               FROM generate_series(1, array_upper($2, 1)) s(i)
            EXCEPT
            SELECT t.typname
              FROM pg_catalog.pg_type t
              LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace
             WHERE (
                     t.typrelid = 0
                 OR (SELECT c.relkind = 'c' FROM pg_catalog.pg_class c WHERE c.oid = t.typrelid)
             )
               AND NOT EXISTS(SELECT 1 FROM pg_catalog.pg_type el WHERE el.oid = t.typelem AND el.typarray = t.oid)
               AND n.nspname = $1
               AND t.typtype = ANY( COALESCE($4, ARRAY['b', 'c', 'd', 'p', 'e']) )
        ),
        $3
    );
$$ LANGUAGE SQL;

-- types_are( schema, types[], description )
CREATE OR REPLACE FUNCTION types_are ( NAME, NAME[], TEXT )
RETURNS TEXT AS $$
    SELECT _types_are( $1, $2, $3, NULL );
$$ LANGUAGE SQL;

-- types_are( schema, types[] )
CREATE OR REPLACE FUNCTION types_are ( NAME, NAME[] )
RETURNS TEXT AS $$
    SELECT _types_are( $1, $2, 'Schema ' || quote_ident($1) || ' should have the correct types', NULL );
$$ LANGUAGE SQL;

-- types_are( types[], description )
CREATE OR REPLACE FUNCTION _types_are ( NAME[], TEXT, CHAR[] )
RETURNS TEXT AS $$
    SELECT _are(
        'types',
        ARRAY(
            SELECT t.typname
              FROM pg_catalog.pg_type t
              LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace
             WHERE (
                     t.typrelid = 0
                 OR (SELECT c.relkind = 'c' FROM pg_catalog.pg_class c WHERE c.oid = t.typrelid)
             )
               AND NOT EXISTS(SELECT 1 FROM pg_catalog.pg_type el WHERE el.oid = t.typelem AND el.typarray = t.oid)
               AND n.nspname NOT IN ('pg_catalog', 'information_schema')
               AND pg_catalog.pg_type_is_visible(t.oid)
               AND t.typtype = ANY( COALESCE($3, ARRAY['b', 'c', 'd', 'p', 'e']) )
            EXCEPT
            SELECT $1[i]
              FROM generate_series(1, array_upper($1, 1)) s(i)
        ),
        ARRAY(
            SELECT $1[i]
               FROM generate_series(1, array_upper($1, 1)) s(i)
            EXCEPT
            SELECT t.typname
              FROM pg_catalog.pg_type t
              LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace
             WHERE (
                     t.typrelid = 0
                 OR (SELECT c.relkind = 'c' FROM pg_catalog.pg_class c WHERE c.oid = t.typrelid)
             )
               AND NOT EXISTS(SELECT 1 FROM pg_catalog.pg_type el WHERE el.oid = t.typelem AND el.typarray = t.oid)
               AND n.nspname NOT IN ('pg_catalog', 'information_schema')
               AND pg_catalog.pg_type_is_visible(t.oid)
               AND t.typtype = ANY( COALESCE($3, ARRAY['b', 'c', 'd', 'p', 'e']) )
        ),
        $2
    );
$$ LANGUAGE SQL;


-- types_are( types[], description )
CREATE OR REPLACE FUNCTION types_are ( NAME[], TEXT )
RETURNS TEXT AS $$
    SELECT _types_are( $1, $2, NULL );
$$ LANGUAGE SQL;

-- types_are( types[] )
CREATE OR REPLACE FUNCTION types_are ( NAME[] )
RETURNS TEXT AS $$
    SELECT _types_are( $1, 'Search path ' || pg_catalog.current_setting('search_path') || ' should have the correct types', NULL );
$$ LANGUAGE SQL;

-- domains_are( schema, domains[], description )
CREATE OR REPLACE FUNCTION domains_are ( NAME, NAME[], TEXT )
RETURNS TEXT AS $$
    SELECT _types_are( $1, $2, $3, ARRAY['d'] );
$$ LANGUAGE SQL;

-- domains_are( schema, domains[] )
CREATE OR REPLACE FUNCTION domains_are ( NAME, NAME[] )
RETURNS TEXT AS $$
    SELECT _types_are( $1, $2, 'Schema ' || quote_ident($1) || ' should have the correct domains', ARRAY['d'] );
$$ LANGUAGE SQL;

-- domains_are( domains[], description )
CREATE OR REPLACE FUNCTION domains_are ( NAME[], TEXT )
RETURNS TEXT AS $$
    SELECT _types_are( $1, $2, ARRAY['d'] );
$$ LANGUAGE SQL;

-- domains_are( domains[] )
CREATE OR REPLACE FUNCTION domains_are ( NAME[] )
RETURNS TEXT AS $$
    SELECT _types_are( $1, 'Search path ' || pg_catalog.current_setting('search_path') || ' should have the correct domains', ARRAY['d'] );
$$ LANGUAGE SQL;

-- enums_are( schema, enums[], description )
CREATE OR REPLACE FUNCTION enums_are ( NAME, NAME[], TEXT )
RETURNS TEXT AS $$
    SELECT _types_are( $1, $2, $3, ARRAY['e'] );
$$ LANGUAGE SQL;

-- enums_are( schema, enums[] )
CREATE OR REPLACE FUNCTION enums_are ( NAME, NAME[] )
RETURNS TEXT AS $$
    SELECT _types_are( $1, $2, 'Schema ' || quote_ident($1) || ' should have the correct enums', ARRAY['e'] );
$$ LANGUAGE SQL;

-- enums_are( enums[], description )
CREATE OR REPLACE FUNCTION enums_are ( NAME[], TEXT )
RETURNS TEXT AS $$
    SELECT _types_are( $1, $2, ARRAY['e'] );
$$ LANGUAGE SQL;

-- enums_are( enums[] )
CREATE OR REPLACE FUNCTION enums_are ( NAME[] )
RETURNS TEXT AS $$
    SELECT _types_are( $1, 'Search path ' || pg_catalog.current_setting('search_path') || ' should have the correct enums', ARRAY['e'] );
$$ LANGUAGE SQL;

-- _dexists( schema, domain )
CREATE OR REPLACE FUNCTION triggers_are( NAME, NAME, NAME[], TEXT )
RETURNS TEXT AS $$
    SELECT _are(
        'triggers',
        ARRAY(
            SELECT t.tgname
              FROM pg_catalog.pg_trigger t
              JOIN pg_catalog.pg_class c     ON c.oid = t.tgrelid
              JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
             WHERE n.nspname = $1
               AND c.relname = $2
            EXCEPT
            SELECT $3[i]
              FROM generate_series(1, array_upper($3, 1)) s(i)
        ),
        ARRAY(
            SELECT $3[i]
              FROM generate_series(1, array_upper($3, 1)) s(i)
            EXCEPT
            SELECT t.tgname
              FROM pg_catalog.pg_trigger t
              JOIN pg_catalog.pg_class c     ON c.oid = t.tgrelid
              JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
             WHERE n.nspname = $1
               AND c.relname = $2
        ),
        $4
    );
$$ LANGUAGE SQL;

-- triggers_are( schema, table, triggers[] )
CREATE OR REPLACE FUNCTION triggers_are( NAME, NAME, NAME[] )
RETURNS TEXT AS $$
    SELECT triggers_are( $1, $2, $3, 'Table ' || quote_ident($1) || '.' || quote_ident($2) || ' should have the correct triggers' );
$$ LANGUAGE SQL;

-- triggers_are( table, triggers[], description )
CREATE OR REPLACE FUNCTION triggers_are( NAME, NAME[], TEXT )
RETURNS TEXT AS $$
    SELECT _are(
        'triggers',
        ARRAY(
            SELECT t.tgname
              FROM pg_catalog.pg_trigger t
              JOIN pg_catalog.pg_class c ON c.oid = t.tgrelid
              JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
             WHERE c.relname = $1
               AND n.nspname NOT IN ('pg_catalog', 'information_schema')
            EXCEPT
            SELECT $2[i]
              FROM generate_series(1, array_upper($2, 1)) s(i)
        ),
        ARRAY(
            SELECT $2[i]
              FROM generate_series(1, array_upper($2, 1)) s(i)
            EXCEPT
            SELECT t.tgname
              FROM pg_catalog.pg_trigger t
              JOIN pg_catalog.pg_class c ON c.oid = t.tgrelid
              JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
               AND n.nspname NOT IN ('pg_catalog', 'information_schema')
        ),
        $3
    );
$$ LANGUAGE SQL;

-- triggers_are( table, triggers[] )
CREATE OR REPLACE FUNCTION triggers_are( NAME, NAME[] )
RETURNS TEXT AS $$
    SELECT triggers_are( $1, $2, 'Table ' || quote_ident($1) || ' should have the correct triggers' );
$$ LANGUAGE SQL;

CREATE OR REPLACE FUNCTION _areni ( text, text[], text[], TEXT )
RETURNS TEXT AS $$
DECLARE
    what    ALIAS FOR $1;
    extras  ALIAS FOR $2;
    missing ALIAS FOR $3;
    descr   ALIAS FOR $4;
    msg     TEXT    := '';
    res     BOOLEAN := TRUE;
BEGIN
    IF extras[1] IS NOT NULL THEN
        res = FALSE;
        msg := E'\n' || diag(
            '    Extra ' || what || E':\n        '
            ||  array_to_string( extras, E'\n        ' )
        );
    END IF;
    IF missing[1] IS NOT NULL THEN
        res = FALSE;
        msg := msg || E'\n' || diag(
            '    Missing ' || what || E':\n        '
            ||  array_to_string( missing, E'\n        ' )
        );
    END IF;

    RETURN ok(res, descr) || msg;
END;
$$ LANGUAGE plpgsql;


-- casts_are( casts[], description )
CREATE OR REPLACE FUNCTION casts_are ( TEXT[], TEXT )
RETURNS TEXT AS $$
    SELECT _areni(
        'casts',
        ARRAY(
            SELECT display_type(castsource, NULL) || ' AS ' || display_type(casttarget, NULL)
              FROM pg_catalog.pg_cast c
            EXCEPT
            SELECT $1[i]
              FROM generate_series(1, array_upper($1, 1)) s(i)
        ),
        ARRAY(
            SELECT $1[i]
              FROM generate_series(1, array_upper($1, 1)) s(i)
            EXCEPT
            SELECT display_type(castsource, NULL) || ' AS ' || display_type(casttarget, NULL)
              FROM pg_catalog.pg_cast c
        ),
        $2
    );
$$ LANGUAGE sql;

-- casts_are( casts[] )
CREATE OR REPLACE FUNCTION casts_are ( TEXT[] )
RETURNS TEXT AS $$
    SELECT casts_are( $1, 'There should be the correct casts');
$$ LANGUAGE SQL;

CREATE OR REPLACE FUNCTION display_oper ( NAME, OID )
RETURNS TEXT AS $$
    SELECT $1 || substring($2::regoperator::text, '[(][^)]+[)]$')
$$ LANGUAGE SQL;

-- operators_are( schema, operators[], description )
CREATE OR REPLACE FUNCTION operators_are( NAME, TEXT[], TEXT )
RETURNS TEXT AS $$
    SELECT _areni(
        'operators',
        ARRAY(
            SELECT display_oper(o.oprname, o.oid) || ' RETURNS ' || o.oprresult::regtype
              FROM pg_catalog.pg_operator o
              JOIN pg_catalog.pg_namespace n ON o.oprnamespace = n.oid
             WHERE n.nspname = $1
            EXCEPT
            SELECT $2[i]
              FROM generate_series(1, array_upper($2, 1)) s(i)
        ),
        ARRAY(
            SELECT $2[i]
              FROM generate_series(1, array_upper($2, 1)) s(i)
            EXCEPT
            SELECT display_oper(o.oprname, o.oid) || ' RETURNS ' || o.oprresult::regtype
              FROM pg_catalog.pg_operator o
              JOIN pg_catalog.pg_namespace n ON o.oprnamespace = n.oid
             WHERE n.nspname = $1
        ),
        $3
    );
$$ LANGUAGE SQL;

-- operators_are( schema, operators[] )
CREATE OR REPLACE FUNCTION operators_are ( NAME, TEXT[] )
RETURNS TEXT AS $$
    SELECT operators_are($1, $2, 'Schema ' || quote_ident($1) || ' should have the correct operators' );
$$ LANGUAGE SQL;

-- operators_are( operators[], description )
CREATE OR REPLACE FUNCTION operators_are( TEXT[], TEXT )
RETURNS TEXT AS $$
    SELECT _areni(
        'operators',
        ARRAY(
            SELECT display_oper(o.oprname, o.oid) || ' RETURNS ' || o.oprresult::regtype
              FROM pg_catalog.pg_operator o
              JOIN pg_catalog.pg_namespace n ON o.oprnamespace = n.oid
             WHERE pg_catalog.pg_operator_is_visible(o.oid)
               AND n.nspname NOT IN ('pg_catalog', 'information_schema')
            EXCEPT
            SELECT $1[i]
              FROM generate_series(1, array_upper($1, 1)) s(i)
        ),
        ARRAY(
            SELECT $1[i]
              FROM generate_series(1, array_upper($1, 1)) s(i)
            EXCEPT
            SELECT display_oper(o.oprname, o.oid) || ' RETURNS ' || o.oprresult::regtype
              FROM pg_catalog.pg_operator o
              JOIN pg_catalog.pg_namespace n ON o.oprnamespace = n.oid
             WHERE pg_catalog.pg_operator_is_visible(o.oid)
               AND n.nspname NOT IN ('pg_catalog', 'information_schema')
        ),
        $2
    );
$$ LANGUAGE SQL;

-- operators_are( operators[] )
CREATE OR REPLACE FUNCTION operators_are ( TEXT[] )
RETURNS TEXT AS $$
    SELECT operators_are($1, 'There should be the correct operators')
$$ LANGUAGE SQL;

-- columns_are( schema, table, columns[], description )
CREATE OR REPLACE FUNCTION columns_are( NAME, NAME, NAME[], TEXT )
RETURNS TEXT AS $$
    SELECT _are(
        'columns',
        ARRAY(
            SELECT a.attname
              FROM pg_catalog.pg_namespace n
              JOIN pg_catalog.pg_class c ON n.oid = c.relnamespace
              JOIN pg_catalog.pg_attribute a ON c.oid = a.attrelid
             WHERE n.nspname = $1
               AND c.relname = $2
               AND a.attnum > 0
               AND NOT a.attisdropped
            EXCEPT
            SELECT $3[i]
              FROM generate_series(1, array_upper($3, 1)) s(i)
        ),
        ARRAY(
            SELECT $3[i]
              FROM generate_series(1, array_upper($3, 1)) s(i)
            EXCEPT
            SELECT a.attname
              FROM pg_catalog.pg_namespace n
              JOIN pg_catalog.pg_class c ON n.oid = c.relnamespace
              JOIN pg_catalog.pg_attribute a ON c.oid = a.attrelid
             WHERE n.nspname = $1
               AND c.relname = $2
               AND a.attnum > 0
               AND NOT a.attisdropped
        ),
        $4
    );
$$ LANGUAGE SQL;

-- columns_are( schema, table, columns[] )
CREATE OR REPLACE FUNCTION columns_are( NAME, NAME, NAME[] )
RETURNS TEXT AS $$
    SELECT columns_are( $1, $2, $3, 'Table ' || quote_ident($1) || '.' || quote_ident($2) || ' should have the correct columns' );
$$ LANGUAGE SQL;

-- columns_are( table, columns[], description )
CREATE OR REPLACE FUNCTION columns_are( NAME, NAME[], TEXT )
RETURNS TEXT AS $$
    SELECT _are(
        'columns',
        ARRAY(
            SELECT a.attname
              FROM pg_catalog.pg_namespace n
              JOIN pg_catalog.pg_class c ON n.oid = c.relnamespace
              JOIN pg_catalog.pg_attribute a ON c.oid = a.attrelid
             WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
               AND pg_catalog.pg_table_is_visible(c.oid)
               AND c.relname = $1
               AND a.attnum > 0
               AND NOT a.attisdropped
            EXCEPT
            SELECT $2[i]
              FROM generate_series(1, array_upper($2, 1)) s(i)
        ),
        ARRAY(
            SELECT $2[i]
              FROM generate_series(1, array_upper($2, 1)) s(i)
            EXCEPT
            SELECT a.attname
              FROM pg_catalog.pg_namespace n
              JOIN pg_catalog.pg_class c ON n.oid = c.relnamespace
              JOIN pg_catalog.pg_attribute a ON c.oid = a.attrelid
             WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
               AND pg_catalog.pg_table_is_visible(c.oid)
               AND c.relname = $1
               AND a.attnum > 0
               AND NOT a.attisdropped
        ),
        $3
    );
$$ LANGUAGE SQL;

-- columns_are( table, columns[] )
CREATE OR REPLACE FUNCTION columns_are( NAME, NAME[] )
RETURNS TEXT AS $$
    SELECT columns_are( $1, $2, 'Table ' || quote_ident($1) || ' should have the correct columns' );
$$ LANGUAGE SQL;

-- _get_db_owner( dbname )
CREATE OR REPLACE FUNCTION _get_db_owner( NAME )
RETURNS NAME AS $$
    SELECT pg_catalog.pg_get_userbyid(datdba)
      FROM pg_catalog.pg_database
     WHERE datname = $1;
$$ LANGUAGE SQL;

-- db_owner_is ( dbname, user, description )
CREATE OR REPLACE FUNCTION db_owner_is ( NAME, NAME, TEXT )
RETURNS TEXT AS $$
DECLARE
    dbowner NAME := _get_db_owner($1);
BEGIN
    -- Make sure the database exists.
    IF dbowner IS NULL THEN
        RETURN ok(FALSE, $3) || E'\n' || diag(
            E'    Database ' || quote_ident($1) || ' does not exist'
        );
    END IF;

    RETURN is(dbowner, $2, $3);
END;
$$ LANGUAGE plpgsql;

-- db_owner_is ( dbname, user )
CREATE OR REPLACE FUNCTION db_owner_is ( NAME, NAME )
RETURNS TEXT AS $$
    SELECT db_owner_is(
        $1, $2,
        'Database ' || quote_ident($1) || ' should be owned by ' || quote_ident($2)
    );
$$ LANGUAGE sql;