Re: Debugging a function - what's the best way to do this quickly?

On Tue, Dec 19, 2017 at 2:24 PM, Kevin Burke <kev(at)inburke(dot)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.