On Tue, Dec 19, 2017 at 2:24 PM, Kevin Burke <k...@inburke.com> 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. > > -- > Kevin Burke > 925.271.7005 <(925)%20271-7005> | kev.inburke.com > *You would probably want to debug the function in interactive mode to find out where you went wrong.* *Both PgAdmin III and PgAdmin 4 can use the debugger from EnterpriseDBhttps://www.pgadmin.org/docs/pgadmin3/1.22/debugger.html <https://www.pgadmin.org/docs/pgadmin3/1.22/debugger.html>https://www.pgadmin.org/docs/pgadmin4/dev/debugger.html <https://www.pgadmin.org/docs/pgadmin4/dev/debugger.html>* -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.
