PgTAP

PostgreSQL 테스팅 프레임워크에서 사용하는 함수입니다.

TAP ?

stands for Test Anything Protocol. It is an framework which aims to simplify the error reporting during testing.

Testing tables

begin;
select plan( 1 );

select has_table( 'profiles' );

select * from finish();
rollback;

has_table(): Tests whether or not a table exists in the database
has_index(): Checks for the existence of a named index associated with the named table.
has_relation(): Tests whether or not a relation exists in the database.

Testing columns

begin;
select plan( 2 );

select has_column( 'profiles', 'id' ); -- test that the "id" column exists in the "profiles" table
select col_is_pk( 'profiles', 'id' ); -- test that the "id" column is a primary key

select * from finish();
rollback;

has_column(): Tests whether or not a column exists in a given table, view, materialized view or composite type.
col_is_pk(): Tests whether the specified column or columns in a table is/are the primary key for that table.

Testing RLS policies

begin;
select plan( 1 );

select policies_are(
  'public',
  'profiles',
  ARRAY [
    'Profiles are public', -- Test that there is a policy called  "Profiles are public" on the "profiles" table.
    'Profiles can only be updated by the owner'  -- Test that there is a policy called  "Profiles can only be updated by the owner" on the "profiles" table.
  ]
);

select * from finish();
rollback;

policies_are(): Tests that all of the policies on the named table are only the policies that should be on that table.
policy_roles_are(): Tests whether the roles to which policy applies are only the roles that should be on that policy.
policy_cmd_is(): Tests whether the command to which policy applies is same as command that is given in function arguments.

You can also use the results_eq() method to test that a Policy returns the correct data:

begin;
select plan( 1 );

select results_eq(
    'select * from profiles()',
    $$VALUES ( 1, 'Anna'), (2, 'Bruce'), (3, 'Caryn')$$,
    'profiles() should return all users'
);


select * from finish();
rollback;

results_eq()
results_ne()

Testing functions

prepare hello_expr as select 'hello'

begin;
select plan(3);
-- You'll need to create a hello_world and is_even function
select function_returns( 'hello_world', 'text' );                   -- test if the function "hello_world" returns text
select function_returns( 'is_even', ARRAY['integer'], 'boolean' );  -- test if the function "is_even" returns a boolean
select results_eq('select * from hello_world()', 'hello_expr');          -- test if the function "hello_world" returns "hello"

select * from finish();
rollback;

function_returns(): Tests that a particular function returns a particular data type
is_definer(): Tests that a function is a security definer (that is, a setuid function).