pgTAP

CREATE EXTENSION IF NOT EXISTS pgtap;

postgres=# SELECT extname, extversion FROM pg_extension WHERE extname = 'pgtap';
 extname | extversion 
---------+------------
 pgtap   | 1.3.4
(1 row)

CREATE EXTENSION pgtap SCHEMA tap;

SELECT plan(3);  -- 声明将运行 3 个测试

-- ... 测试用例 ...

SELECT * FROM finish();

SELECT no_plan();

-- ... 任意数量的测试用例 ...

SELECT * FROM finish();

ok 1 - 测试描述
not ok 2 - 失败的测试描述

SELECT ok(1 + 1 = 2, '1 加 1 等于 2');

ok 1 - 1 加 1 等于 2

SELECT is(
    (SELECT count(*)::int FROM pg_tables WHERE schemaname = 'public'),
    5,
    'public schema 应有 5 张表'
);

SELECT cmp_ok(
    (SELECT count(*)::int FROM orders),
    '>=',
    100,
    '订单数应不少于 100'
);

SELECT matches(
    (SELECT version()),
    'PostgreSQL 1[4-9]',
    '版本号应为 PostgreSQL 14-19'
);

BEGIN;
SELECT plan(5);

-- 创建测试表
CREATE TABLE users (
    id         serial PRIMARY KEY,
    email      text NOT NULL,
    created_at timestamptz DEFAULT now()
);

-- 验证 users 表结构
SELECT has_table('public', 'users', 'users 表应存在');
SELECT has_column('users', 'id', 'users 表应有 id 列');
SELECT col_type_is('users', 'id', 'integer', 'id 列应为 integer 类型');
SELECT col_not_null('users', 'email', 'email 列应有非空约束');
SELECT col_has_default('users', 'created_at', 'created_at 应有默认值');

SELECT * FROM finish();
ROLLBACK;

1..5
ok 1 - users 表应存在
ok 2 - users 表应有 id 列
ok 3 - id 列应为 integer 类型
ok 4 - email 列应有非空约束
ok 5 - created_at 应有默认值

SELECT has_pk('users', 'users 表应有主键');
SELECT has_index('users', 'idx_users_email', 'users 表应有 email 索引');

SELECT has_function(
    'public', 'calculate_total', ARRAY['integer'],
    'calculate_total(int) 函数应存在'
);
SELECT function_returns(
    'public', 'calculate_total', ARRAY['integer'], 'numeric',
    '函数应返回 numeric 类型'
);

SELECT has_trigger('orders', 'trg_orders_updated', '应有更新触发器');

SELECT has_extension('pgtap', 'pgtap 扩展应已安装');
SELECT has_schema('public', 'public schema 应存在');

SELECT results_eq(
    'SELECT id, name FROM users ORDER BY id LIMIT 2',
    VALUES(1,′alice′),(2,′bob′),
    '前两个用户应为 alice 和 bob'
);

SELECT set_eq(
    'SELECT name FROM departments',
    VALUES(′Engineering′),(′Marketing′),(′Sales′),
    '应有三个部门'
);

SELECT set_has(
    'SELECT name FROM users',
    VALUES(′admin′),
    '用户列表应包含 admin'
);

SELECT throws_ok(
    'INSERT INTO users (id, name) VALUES (1, NULL)',
    '23502',
    NULL,
    '插入空 name 应触发非空约束违反'
);

SELECT lives_ok(
    'SELECT 1',
    'SELECT 1 应正常执行'
);

-- test_user_module.sql
BEGIN;

-- ========== 0. 创建测试对象 ==========
CREATE TABLE users (
    id         serial PRIMARY KEY,
    email      text NOT NULL,
    created_at timestamptz DEFAULT now()
);

CREATE OR REPLACE FUNCTION get_user_name(p_id integer)
RETURNS text
LANGUAGE plpgsql AS $$
DECLARE
    v_email text;
BEGIN
    SELECT email INTO v_email FROM users WHERE id = p_id;
    RETURN coalesce(v_email, '<unknown>');
END;
$$;

SELECT plan(12);

-- ========== 1. Schema 验证 ==========
SELECT has_table('public', 'users', 'users 表应存在');
SELECT has_column('users', 'id', '应有 id 列');
SELECT has_column('users', 'email', '应有 email 列');
SELECT has_column('users', 'created_at', '应有 created_at 列');
SELECT col_type_is('users', 'id', 'integer', 'id 应为 integer');
SELECT col_not_null('users', 'email', 'email 非空');
SELECT has_pk('users', '应有主键');

-- ========== 2. 函数验证 ==========
SELECT has_function('public', 'get_user_name', ARRAY['integer'], 'get_user_name(int) 存在');
SELECT function_returns('public', 'get_user_name', ARRAY['integer'], 'text', '返回 text');

-- ========== 3. 数据验证 ==========
SELECT cmp_ok(
    (SELECT count(*)::int FROM users),
    '>=', 0,
    'users 表行数 >= 0'
);

-- ========== 4. 结果集验证 ==========
SELECT results_eq(
    'SELECT 1 + 1',
    'SELECT 2',
    '1+1 应等于 2'
);

-- ========== 5. 异常验证 ==========
SELECT throws_ok(
    SELECT1/0,
    '22012',
    NULL,
    '除零应抛异常'
);

SELECT * FROM finish();
ROLLBACK;

psql -d mydb -f test_user_module.sql

1..12
ok 1 - users 表应存在
ok 2 - 应有 id 列
ok 3 - 应有 email 列
ok 4 - 应有 created_at 列
ok 5 - id 应为 integer
ok 6 - email 非空
ok 7 - 应有主键
ok 8 - get_user_name(int) 存在
ok 9 - 返回 text
ok 10 - users 表行数 >= 0
ok 11 - 1+1 应等于 2
ok 12 - 除零应抛异常