From: | Kevin Burke <kev(at)inburke(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Debugging a function - what's the best way to do this quickly? |
Date: | 2017-12-19 19:24:06 |
Message-ID: | CAGs5PJKgAzGyOgBjUtkazLmm4BQrHZZ2km36LU11gWUs=hRoWQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Andres Freund | 2017-12-19 19:31:03 | Re: AWS Aurora and PG 10 |
Previous Message | Stephen Frost | 2017-12-19 17:24:03 | Re: PostgreSQL suitable? |