Error Header Image

How to Debug Postgres Functions in pgAdmin

It isn’t always easy to debug Postgres functions, but with this post, I hope to shed some light on my simple approach to this problem.

Do Block

The basic set up of code you will want in your SQL file will be a “do” block:

do $$
  declare
    -------------------------------
    -- Declare your variables here
    -------------------------------
  begin
    -------------------------------
    -- Test code here
    -------------------------------
  end;
$$
language plpgsql;

A “do” block is treated as the body of a function, taking no parameters and returning void. You can set the language at the end. Most functions are written in the procedural language “plpgsql”, which is why I added it at the end of the block. However, the language could be set to something else such as “SQL”.

Calling a Function

You can trigger a function using “Perform” or “Select”. Perform is used if you do not want to store what is returned by the function.

do $$
  declare
    l_return_var bigint;
  begin
    perform test_function();
    select * from test_function();
    l_return_var := test_function();
  end;
$$
language plpgsql;

The above examples in the code block above are all valid ways of calling a function. You can only return strings from a do block but that is not an issue. We only need it to run the code and show that everything is working, through string returns.

Note: The only time “select *” won’t work is when the return type is a record or a set of records. This is a generic return type that needs to be defined when called.

How To Debug Functions

When figuring out issues with functions I always find the best way to go is with the old-fashioned raise notice. This approach is very much akin to “rubber ducking“, in the sense that you can go line by line through the code and confirm that each variable has the correct value at any given time. I do this by extracting the function code and trying to run it in a do block.

Create Script

First of all, I get the function create script in pgAdmim.

From this script, copy from “declare” to “end;” and paste this into your do block. For simplicity, the example function does not take parameters but if yours does you will need to then add these to the declare section. The code below shows a very basic example. The commented declared variable is just an example of declaring an input parameter from your function. Also, if your function is returning a value that isn’t text (such as an integer) then you will need to comment out the return statement at the end of the block. Otherwise, the do block will error on running.

do $$
  declare
    --p_input_var_1 bigint := 5;  EXAMPLE OF DECLARING AN INPUT PARAMETER

    l_int_from_text bigint;
    l_text_from_int text;
  begin
    l_text_from_int := convert_int_to_text(5);
    l_int_from_text := convert_text_to_int(l_text_from_int);

    if l_int_from_text = 5 then
        return 'success';
    end if;
    return 'fail';
  end;
$$
language plpgsql;

When this code block runs it simply returns ‘fail’. This should not be the case as if the convert_int_to_text and then convert_text_to_int functions work correctly then the if statement would always return true. Which would lead to a ‘success’ return. Using raise notice makes it very obvious where the issue lies.

Raise Notice

The raise notice command writes a notice to the messages tab in pgAdmin. The syntax is set up to replace the literal % with a parameter supplied to the command after the initial string. So for this basic raise notice debugging you can set up the following raise notice:

l_text_from_int := convert_int_to_text(5);
raise notice 'l_text_from_int: %', l_text_from_int;

The % within the quotation marks is replaced with the value supplied after the comma. More information is in the documentation.

When running the do block with the raise notice added, in the messages output window you see the following response

This clearly shows that there is an issue with the convert_int_to_text function. So from just using a very simple approach, you can quickly diagnose the issue with the function. In this case, another function is not working. So you will need to fix that one first. You can just use the same approach on this failing function. Hopefully, it should fairly easily be able to diagnose and fix the issue.

Summary

In summary, I hope this post has clearly outlined my approach to debug Postgres functions. This method of creating a do block, then testing the code using raise notice is my go-to technique. It is very quick to set up a do block and the use of raise notice just makes it very clear where the issues are. It is surprising how effective such a simple approach is to debug Postgres functions but it always helps me get to the bottom of issues.

Leave a Reply