i use pgtap to test postgresql stored procedures. its results_eq function takes the result of a stored procedure, compares it to an expected result and reports failure if both are not equal.
this is the code i am running:
PREPARE result_have AS SELECT select_some_data(12345, 'test_string');
PREPARE result_want AS VALUES ('("2010-09-07 06:05:00+00",100.0)');
SELECT results_eq('result_have', 'result_want');
and this is the failure output:
not ok 21
# Failed test 21: "this should return a result"
# Columns differ between queries:
# have: ("(""2010-09-07 06:05:00+00"",100.0)")
# want: ("(""2010-09-07 06:05:00+00"",100.0)")
# Looks like you failed 1 test of 21
i might be really sleep-deprived, but want and have look pretty similar to me.
does anyone know why this is reported as failure ?
update concerning details: this is how i define the stored procedure in question:
CREATE OR REPLACE FUNCTION select_some_data
(
IN p_some_pkey integer,
IN p_some_code varchar(16)
)
RETURNS TABLE(timestamp_utc timestamp with time zone, value varchar)
...
so to follow peters advice, i tried changing my code, but had no success:
PREPARE result_have AS SELECT select_some_data(12345, 'test_string');
-- TODO: none of these work, syntax error at or near "TABLE"
-- PREPARE result_want AS VALUES ('("2010-09-07 06:05:00+00",100.0)'::TABLE(timestamp with time zone, varchar));
-- PREPARE result_want AS VALUES ('("2010-09-07 06:05:00+00",100.0)'::'TABLE(timestamp with time zone, varchar)');
-- this is the old code...
PREPARE result_want AS VALUES ('("2010-09-07 06:05:00+00",100.0)');
SELECT results_eq('result_have', 'result_want');
as you can probably tell, i'm pretty much stabbing in the dark even about basic postgresql syntax - and neither google nor the search on postgresql.org return anything useful when searching for ::
. i eventually hazarded a lucky guess that this might be an operator and found ::
being a type cast. The column_name
parameter documentation for CREATE FUNCTION says that 'RETURNS TABLE
also implies RETURNS SETOF
' which gets me e.g. here and maybe here and here. New attempts:
PREPARE result_have AS SELECT select_some_data(12345, 'test_string');
-- TODO: doesn't work, syntax error at or near "("
-- PREPARE result_want AS VALUES ('("2010-09-07 06:05:00+00",100.0)'::SETOF(timestamp with time zone, varchar));
-- TODO: doesn't work, syntax error at or near ","
-- PREPARE result_want AS VALUES ('("2010-09-07 06:05:00+00",100.0)'::SETOF RECORD(timestamp with time zone, varchar));
-- this is the old code...
PREPARE result_want AS VALUES ('("2010-09-07 06:05:00+00",100.0)');
SELECT results_eq('result_have', 'result_want');
this is pointless, i'm just doing guesswork here. can anybody help me out with the right syntax ? please also note that the function just RETURNS TABLE
because that was the very first thing i was able to get to work, so if there is a solution that would require changing that, i'm happy to change it.
update 2: RhodiumToad on the postgresql IRC channel(irc://irc.freenode.net/#postgresql) helped me out with the proper syntax. if i knew a little more about postgresql than i do, i could have figured that this only makes sense: two data types, two casts (DOH!) :o).
also, at the moment, there is only one data set in the test database, so the syntax as used above might still work. from all i understand, it will probably fail once there is more than one dataset returned, so it should be SELECT * FROM
, not just SELECT
:
PREPARE result_have AS SELECT * FROM select_some_data(12345, 'test_param_code');
PREPARE result_want AS VALUES ('2010-09-07 06:05:00+00'::timestamp with time zone, '100.0'::varchar);
SELECT results_eq('result_have', 'result_want', 'have and want should be equal');
now have and want results are compared as equal and the test passes. log output when the tests are run:
ok 21 - have and want should be equal
ok
All tests successful.
Files=1, Tests=21, 1 wallclock secs ( 0.02 usr 0.00 sys + 0.05 cusr 0.03 csys = 0.10 CPU)
Result: PASS
WOOT!! :-)
You don't give all the details, but I suspect it's a data type mismatch. pgTAP tends to require exact matches. Try this:
PREPARE result_want AS VALUES ('("2010-09-07 06:05:00+00",100.0)'::foo);
where foo
is the return type of the function select_some_data
.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With