I'd like to build up some test code for various objects in my Postgres 11.5 database, and am wondering if there is a straightforward way to implement simple checks in straight SQL. As an example, I've got a simple domain that accepts any of three values, case-blind. This is just one example I've picked because the test cases are finite and simple.
DROP DOMAIN IF EXISTS domains.test_outcome;
CREATE DOMAIN domains.test_outcome AS
citext
NOT NULL
CONSTRAINT test_outcome_legal_values
CHECK(
VALUE IN ('pass','warning','fail')
);
COMMENT ON DOMAIN domains.test_outcome IS
'The test_outcome must be pass, warning, or fail, case-insensitive.';
Below are some simple test statements that exercise good and bad values:
-- Good values
select 'warning'::test_outcome;
select 'fail'::test_outcome;
select 'PASS'::test_outcome;
select 'WARNING'::test_outcome;
select 'FAIL'::test_outcome;
-- Bad values
select NULL::test_outcome;
select ''::test_outcome;
select 'foo'::test_outcome;
What I'd like to do, is get a single line for each case, and not blow up on an error. I've tried using a DO block, and could use a PL/PgSQL function. Here's the DO code I've tried:
DO $TESTS$
BEGIN
-- Good values
select 'warning'::test_outcome;
select 'fail'::test_outcome;
select 'PASS'::test_outcome;
select 'WARNING'::test_outcome;
select 'FAIL'::test_outcome;
-- Bad values
select NULL::test_outcome;
select ''::test_outcome;
select 'foo'::test_outcome;
EXCEPTION WHEN OTHERS THEN
RAISE NOTICE 'Error Name:%', SQLERRM;
RAISE NOTICE 'Error State:%', SQLSTATE;
END;
$TESTS$
What I get back are a couple of notices. I can't tie those back to the specific statement, as far as I can tell, and I get nothing for success cases.
NOTICE: Error Name:query has no destination for result data
NOTICE: Error State:42601
DO
Command completed successfully. (Line 20)
I've seen that there are some unit testing frameworks for PG around, using languages we don't use. Is there any way to do simple tests like the ones above in straight SQL or PL/PgSQL?
Aucun commentaire:
Enregistrer un commentaire