I'm writing a function that looks a little like this: DROP FUNCTION IF EXISTS myfunction; CREATE OR REPLACE FUNCTION myfunction(arg1 uuid, _symbol text, _start timestamp with time zone, _end timestamp with time zone ) RETURNS TABLE (arg5 date, arg6 float) AS $$ WITH cte1 AS ( ... ), cte2 AS ( ... ), cte3 AS ( ... ), cte4 AS ( ... ), cte5 AS ( ... ) SELECT X as arg5, Y as arg6 FROM cte5; $$
The function is not returning the correct results; I think the problem is in cte2 or cte3. What's the easiest way to debug this? I would like to send some test inputs through the program, observe the output from cte3, and modify the values and see if I get the correct new answers. Here are the approaches I know right now: - Modify the function return to contain the columns for cte3. (I don't think there is a way to indicate RETURNS * or similar wildcard) - Reload the function. - Call the function with the test arguments, and view the resulting table. Modify/reload/rerun as appropriate. Or: - Copy the function to another file. - Delete the function prologue and epilogue - Replace every use of the input arguments with the hardcoded values I want to test with - Run the file, making changes as necessary. This seems pretty cumbersome. Is there an easier way I am missing? Specifically it would be neat if it was easier to visualize the intermediate steps in the query production. If there are professional tools that help with this I would appreciate pointers to those as well. -- Kevin Burke 925.271.7005 | kev.inburke.com