AnalyticDB:pldbgapi

Introduction

In AnalyticDB for PostgreSQL, PL/pgSQL is a procedural language for writing stored procedures and user-defined functions (UDFs). It extends SQL with programming capabilities, such as variables, conditional logic, loops, and exception handling.

The pldbgapi extension enables debugging for PL/pgSQL code, similar to using the GNU Debugger (GDB) for C programs. You can set breakpoints, step through code, and view variable values at runtime. This extension simplifies developing and debugging functions.

Usage notes

• The pldbgapi extension is supported only on AnalyticDB for PostgreSQL V6.0 instances with minor engine version v6.3.10.19 or later. To check your instance's minor engine version, see View the minor engine version.

• You must use a pgAdmin client to debug PL/pgSQL code. We recommend using pgAdmin client v6.21 because later versions are not compatible with the AnalyticDB for PostgreSQL kernel. To download the client, see pgAdmin.

Procedure

1. Go to the Extensions page for your AnalyticDB for PostgreSQL instance to install the pldbgapi extension. For detailed instructions, see Install, update, and uninstall extensions.

2. Connect to your AnalyticDB for PostgreSQL database and create a function for PL/pgSQL debugging. The following is a sample function.

   CREATE OR REPLACE FUNCTION add_numbers(a int, b int)
   RETURNS NUMERIC AS $$
   DECLARE
       t1_b_avg NUMERIC;
   BEGIN
       --DROP TABLE t1;
       CREATE TABLE t1 (a int, b int, c int, d int);
   	RAISE NOTICE 'Finish CREATE ';
       FOR i IN 1..10 LOOP
   	 INSERT INTO t1 VALUES (i, i, i, i);
   	END LOOP;
   	RAISE NOTICE 'Finish INSERT ';
       SELECT avg(t1.b) INTO t1_b_avg FROM t1 LIMIT 1;
   	RAISE NOTICE 'Finish SELECT: avg=[%] ', t1_b_avg;
   	DROP TABLE t1;
       RETURN a + b + t1_b_avg;
   END;
   $$ LANGUAGE plpgsql;

3. Call the function to verify that it works as expected.

   SELECT add_numbers(1, 3);

4. Open pgAdmin and choose Object > Register > Server.

5. On the General tab of the Register-Server dialog box, enter a server name, such as test.

6. On the Connection tab of the Register-Server dialog box, configure the following parameters and click Save to create the server.

   Parameter	Description
   Host name/address	The public endpoint of the AnalyticDB for PostgreSQL instance. For more information, see Manage public endpoints.
   Port	The port number of the AnalyticDB for PostgreSQL instance.
   Maintenance database	Set this parameter to postgres.
   Username	The database account for the AnalyticDB for PostgreSQL instance.
   Password	The password for the database account.

7. In the server list on the left, navigate to test > Databases > postgres > Schemas > public > Functions to locate the function to debug.

8. Right-click the function and select Debugging > Debug.

9. In the Debugger window, enter the input parameters for the function and click Debug. In this example, the values for parameters a and b are both set to 2.

10. In the debugging panel, click the debug icon in the upper-left corner.

11. After the debugging session ends, you can view the results on the Messages and Result tabs at the bottom of the debugging panel.

    Messages tab: Displays text output generated during function execution.

    Result tab: Displays the return value after function execution.