> BEGIN -- Create dummy records INSERT INTO users (login_name) VALUES ('test1') RETURNING user_id INTO user_id; -- Run the proc PERFORM "inner".set_user_state(user_id); -- The proc MUST set users.state to 'active'; SELECT INTO user_rec * FROM users WHERE user_id = user_id; PERFORM test.assert_equal(user_rec.state, 'active'); END MAIN; -- ALWAYS RAISE EXCEPTION at the end of test procs to rollback! RAISE EXCEPTION '[OK]'; END; $$ LANGUAGE plpgsql; }}} == Assertion Functions == Epic.sql includes some functions to make tests easier to write (and shorter). === test.assert(assertion boolean, msg text) === This is the 'catch-all' to assert anything that can be evaluated to a boolean. For example: {{{ #!sql PERFORM test.assert(substring(a from b), b||" not found in "||a); }}} If the given `assertion` evaluates to False, the given message is raised as a user exception (P0001). Otherwise, returns VOID. The `assertion` argument may not be NULL. === test.assert_void(call text) === Raises an exception if the call does not return void; otherwise, returns VOID. The given call may be a table, view, procedure call, or a full SELECT. This is something of a necessary evil; it would be nice to just use `assert_equal`, but plpgsql doesn't allow one to pass void arguments to a function. === test.assert_equal(elem_1 anyelement, elem_2 anyelement) === Raises an exception if elem_1 is not equal to elem_2; otherwise, returns VOID. The two arguments must be of the same type, although they may both be NULL. If they are not of the same type, you will receive "ERROR: invalid input syntax ...". === test.assert_not_equal(elem_1 anyelement, elem_2 anyelement) === Raises an exception if elem_1 is equal to elem_2; otherwise, returns VOID. The two arguments must be of the same type, although they may both be NULL. If they are not of the same type, you will receive "ERROR: invalid input syntax ...". === test.assert_greater_than(elem_1 anyelement, elem_2 anyelement) === Raises an exception if not (elem_1 > elem_2); otherwise, returns VOID. The two arguments must be of the same type, although they may both be NULL. If they are not of the same type, you will receive "ERROR: invalid input syntax ...". === test.assert_greater_than_or_equal(elem_1 anyelement, elem_2 anyelement) === Raises an exception if not (elem_1 >= elem_2); otherwise, returns VOID. The two arguments must be of the same type, although they may both be NULL. If they are not of the same type, you will receive "ERROR: invalid input syntax ...". === test.assert_less_than(elem_1 anyelement, elem_2 anyelement) === Raises an exception if not (elem_1 < elem_2); otherwise, returns VOID. The two arguments must be of the same type, although they may both be NULL. If they are not of the same type, you will receive "ERROR: invalid input syntax ...". === test.assert_less_than_or_equal(elem_1 anyelement, elem_2 anyelement) === Raises an exception if not (elem_1 <= elem_2); otherwise, returns VOID. The two arguments must be of the same type, although they may both be NULL. If they are not of the same type, you will receive "ERROR: invalid input syntax ...". === test.assert_column(call text, expected anyarray[, colname text]) === Raises an exception if {{{SELECT colname FROM call}}} != expected; otherwise, returns VOID. The `call` argument can be any table, view, or procedure that returns records. The `colname` argument should be the name of a column in the given call string. If NULL or omitted, it will be taken from the first column of `call`'s output. The `expected` argument MUST be an array of the same type as colname. To pass an empty array, try `{}::int[]` (or whatever type you're comparing). The `call` and `expected` arguments MUST be sorted in the same order. If the `call` results are not sorted already, you can usually append 'ORDER BY 1' to it to sort by the selected column. Example: {{{ #!sql PERFORM test.assert_column('get_favorite_user_ids(' || user_id || ') ORDER BY 1', ARRAY[24, 10074, 87321], 'user_id'); }}} === test.assert_raises(call text[, errm text[, state text]]) === Raises an exception if the given call does not raise errm (if provided) or state (if provided); otherwise, returns VOID. The `call` argument can be any table, view, or procedure that returns records, or a full SELECT statement. Example: {{{ #!sql PERFORM test.assert_raises('get_transaction_by_id("a")', 'Bad argument', NULL); }}} If errm or state are NULL or omitted, that value will not be tested. This allows you to test by message alone (since the 5-char [http://www.postgresql.org/docs/8.2/static/errcodes-appendix.html#ERRCODES-TABLE SQLSTATE values] are cryptic), or trap a range of errors by SQLSTATE without regard for the exact message. If you don't know the message you want to trap, call this function with errm = "" and state = "". The resultant error will tell you the SQLSTATE and SQLERRM that were raised. === test.assert_rows(call_1 text, call_2 text) === Asserts that two sets of rows have equal values. Neither call_1 nor call_2 need to be sorted. Either may include a trailing semicolon. Either may be a bare tablename, view, or procedure call (without a SELECT), or a full SELECT statement. {{{ #!sql PERFORM test.assert_rows( 'SELECT tablename FROM pg_tables;', 'SELECT relname FROM pg_class where relkind = ''r'''); }}} For testing a single row, it is common for the second arg to be sans a FROM clause, and simply SELECT values: {{{ #!sql PERFORM test.assert_rows( 'SELECT birth_year, birth_month, birth_day FROM users WHERE user_id = 144;', 'SELECT 2007, 12, 31'); }}} === test.assert_values(call_1 text, call_2 text, columns text) === Raises an exception if SELECT columns FROM call_1 != SELECT columns FROM call_2. Either `call` argument can be any table, view, or procedure that returns records, or a full SELECT statement. Example: {{{ #!sql row_1 := global('get_favorite_user_ids(' || user_id || ')') PERFORM test.assert_values(row_1.tablename, 'users WHERE user_id = 355', 'last_name'); }}} === test.assert_empty((calls text[] | call text)) === Raises an exception if the given calls(s) have any rows. In addition to testing proper DELETE functionality, this is also useful to ensure that you're not running the tests in a production environment. Since all tests roll back, you're not at risk for damaging your data; however, if you designed your tests to be run on empty tables, they may run excruciatingly slowly on large populated tables. The `assert_empty` test can help you document and avoid that scenario. === test.assert_not_empty((calls text[] | call text)) === Raises an exception if the given calls(s) have no rows. == Call arguments == Several Epic functions take one or more 'call' args, each of which is a string (or array of strings) containing a SQL statement or fragment. Since no Postgres procedural languages allow us to pass anonymous records to functions, we must either make a separate version of, say, `assert_rows` for each and every table in our database under test (!) or we must pass SQL in strings to be EXECUTE'd. Epic chooses the latter. [It's possible to pass anonymous records to C functions, but why unleash yet another compiled library on an overworked world?] By passing SQL strings, we actually gain quite a bit of expressiveness. The `call` arguments to Epic functions may each take any of: * A complete SELECT statement: `SELECT a, b, c FROM x WHERE y ORDER BY z` * A bare tablename or JOIN: `users LEFT JOIN widgets ON users.user_id = widgets.creator_id` * A stored procedure call that returns a scalar or type: `my.get_widget_dimensions(7364)` * A stored procedure call that returns a SETOF records: `my.get_widgets_by_creator_id(11429)` * A SELECT statement with no FROM clause: `SELECT 1, 'Fred', '2008-01-15'::timestamptz` === global variables === However, since we still cannot pass anonymous records, we have a conundrum: {{{ #!sql DECLARE v_record1 record; v_record2 record; BEGIN v_record1 := test._make_widget(); v_record2 := my.get_widget(v_record1.widget_id); FOR colname in SELECT 'widget_id', 'creator_id', 'name', 'color', 'height', 'width', 'depth', 'price' LOOP PERFORM test.assert_values(v_record1, v_record2, colname); END LOOP; PERFORM test.assert_equal(v_record2.derived_value, 7348); }}} The above doesn't work, because we cannot pass the `v_record` variables (without knowing their concrete type). The `v_record` variables are essentially local to the function in which they are declared. We could pass the SQL to another function, but then we lose the ability to use the dot notation in our function for e.g. `v_record2.derived_value`). What we would like to have is either a way to pass them to another function, or to have "global" variables which we can access from any function. But plpgsql doesn't have a way to DECLARE variables outside of functions. What to do? The solution Epic uses is TEMP tables. The above can be rewritten clumsily as: {{{ #!sql DECLARE v_record1 record; v_record2 record; BEGIN CREATE TEMP TABLE abc AS SELECT * FROM test._make_widget(); v_record1 := SELECT * FROM abc; CREATE TEMP TABLE xyz AS SELECT * FROM my.get_widget(v_record1.widget_id); v_record2 := SELECT * FROM xyz; FOR colname in SELECT 'widget_id', 'creator_id', 'name', 'color', 'height', 'width', 'depth', 'price' LOOP PERFORM test.assert_values(abc, xyz, colname); END LOOP; PERFORM test.assert_equal(v_record2.derived_value, 7348); }}} But that's hardly better. Epic makes that much shorter and cleaner with its '''global''' function: {{{ #!sql DECLARE g2 text; v_record1 record; v_record2 record; BEGIN v_record1 := get(global('_make_widget()')); g2 := global('get_widget(' || v_record1.widget_id || ')'); FOR colname in SELECT 'widget_id', 'creator_id', 'name', 'color', 'height', 'width', 'depth', 'price' LOOP PERFORM test.assert_values(v_record1.__name__, g2, colname); END LOOP; v_record2 := get(g2); PERFORM test.assert_equal(v_record2.derived_value, 7348); }}} === test.global(call text, name text) === The `global` function takes a `call` argument (a SQL string); it both creates the TEMP table and returns its name. You can therefore pass it to any function which takes a `call` argument. If the `name` argument is NULL or omitted, a default name is constructed (using a sequence). In most cases, you should use this mechanism. It's mostly provided so that you can re-use an existing global TEMP table and reduce consumption in a large test. === test.get(p_tablename text, p_offset int) === Returns a record (the first one, if `p_offset` is 0, NULL, or omitted) from the given table. The returned record includes an extra attribute, `.__name__`, which is the exact value of the `p_tablename` arg. This allows you to call get(global('function()')) and have access to the intermediate TEMP table name, and pass it to functions that take a 'call text' argument, such as assert_column, assert_values, and assert_empty (since no procedural languages support passing records as args). === test.constructor(tablename text) === Returns the SQL statement used to construct the given global table. === test.len(tablename text) === Return the number of rows in the given table. === test.iter(tablename text) === Return SQL to retrieve all rows in the given table. === test.attributes(tablename text) === Return * (minus system columns) FROM pg_attribute for the given table. If the given table has no attributes, an exception is raised. === test.typename(elem anyelement) === Return the typename of the given element. == Timing == There's a `timing(call[, number])` function in Epic to help you test execution times. Simply pass it any [wiki:WritingTests#Callarguments call] as a string; it will be EXECUTEd in a loop from `1..number`, and the total time returned as an `interval`. If the `number` argument is NULL or missing, it defaults to 1,000,000. You can use this in a regression test to assert that future changes to a stored procedure don't violate an SLA. You can also use it to create live tests so you know when, for example, a table has become so large that lookups on it are slower than you can live with. Finally, you can use it directly to test that an optimization change actually makes the given call faster. Of course, you can use `EXPLAIN ANALYZE` for any of those, too, but that's harder to automate.