On Tue, Dec 19, 2017 at 1:24 PM, Kevin Burke <kev@xxxxxxxxxxx> wrote: > 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. If you have a lot of chained CTEs and the problem lies within that chain, copying the query and subbing arguments is likely the best option. For really nasty situations I tend to convert the CTEs, one at a time, to temp tables, reviewing the results on each step. I've scaled back my use of CTEs a lot lately for this and other reasons (mainly problems with statistics) although I really appreciate the lack of catalog bloat. I also heavily abuse 'RAISE NOTICE' for debugging purposes. Something like this: CREATE OR REPLACE FUNCTION Notice(TEXT) RETURNS VOID AS $$ BEGIN RAISE NOTICE '%', $1; END; $$ LANGUAGE PLPGSQL; CREATE OR REPLACE FUNCTION NoticeValue(anyelement) RETURNS anyelement AS $$ SELECT $1 FROM (SELECT NOTICE($1::TEXT)) q; $$ LANGUAGE SQL; 'NoticeValue' can be used just about anywhere, for example: SELECT a FROM foo WHERE... could be quickly converted to: SELECT NoticeValue(a) AS a FROM foo WHERE.... Don't forget, we can convert records to json and 'notice' them: SELECT a, NoticeValue(to_json(a)) FROM foo WHERE.... Dynamic SQL (via EXECUTE) can be a real pleasure to debug (not so much to write and review), particularly if you (securely) do your own parameterization since you can just print out the entire query. From a debugging standpoint, that's as good as it gets. Also, there is a pl/pgsql debugger. I don't have any experience with it, maybe somebody else can comment. I work exclusively with psql, and so tend to use techniques that work well there. merlin
