I am using pgtap to test postgresql stored procedures. its results_eq function takes the result of the stored procedure, compares it with the expected result, and reports an error if both are not equal.
this is the code I'm 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 sleep deprived, but I want and look very much like me.
Does anyone know why this is reported as crashing?
update regarding details: this is how I define a stored procedure:
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) ...
to follow the advice of peters , I tried changing my code, but was unsuccessful:
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 probably can say, I wear very much in the dark even with respect to the basic syntax of postgresql - and neither Google nor the search on postgresql.org returns anything useful when searching for :: . In the end, I ventured to suggest that it could be a statement, and found :: type cast . The documentation for the column_name parameter for CREATE FUNCTION says that ' RETURNS TABLE also means RETURNS SETOF ', which gets me, for example. 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 speculating here. can anyone help me with the correct syntax? please also note that the function is just RETURNS TABLE , because it was the first thing I could work with, so if there is a solution that needs to be changed, I am happy to change it.
update 2 : RhodiumToad on the postgresql IRC channel (irc: //irc.freenode.net/#postgresql) helped me with the correct syntax. if I knew a little more about postgresql than I, I could assume that it makes sense: two data types, two castings (DOH!): o).
also, at the moment there is only one data set in the test database, so the syntax used above can still work. from all that I understand, it will most likely fail if more than one data set is 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 they have and want the results to be compared as equal, and the test passes. log output when running tests:
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 !! :-)