AnalyticDB:pldbgapi

Introduction

PL/pgSQL is an extension language that you can use to write stored procedures and user-defined functions (UDFs) in AnalyticDB for PostgreSQL. PL/pgSQL supports the data query and processing capabilities of SQL, provides procedure management and programming, and allows complex logic and operations based on variables, conditions, loops, and troubleshooting operations.

The pldbgapi extension allows you to use the function debugging feature of PL/pgSQL in the same manner that you use the GNU Debugger (GDB) to debug C programs. The pldbgapi extension supports program breakpoints, single-step debugging, and variable values. You can use the PL/pgSQL features of the pldbgapi extension to develop and debug functions in a simpler manner.

Usage notes

• Only AnalyticDB for PostgreSQL V6.0 instances of V6.3.10.19 or later support the pldbgapi extension. For information about how to view the minor version of an AnalyticDB for PostgreSQL instance, see View the minor engine version.

• You can use the function debugging feature of PL/pgSQL on a pgAdmin client. We recommend that you use version 6.21 of the pgAdmin client. Later versions of the pgAdmin client are not compatible with AnalyticDB for PostgreSQL. For information about how to download the pgAdmin client, see pgAdmin.

Procedure

1. Install the pldbgapi extension on the Extensions page of the AnalyticDB for PostgreSQL instance in which you want to use the extension. For more information, see Install, update, and uninstall extensions.

2. Connect to an AnalyticDB for PostgreSQL database and create a function for PL/pgSQL debugging. 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. Query the table data to verify that the function works as expected.

   SELECT add_numbers(1, 3);

4. Start pgAdmin. In the top navigation bar, choose Object > Register > Server.

5. On the General tab of the Register-Server page, specify a server name. Example: test.

6. On the Connection tab of the Register-Server page, configure the parameters that are described in the following table and click Save to create a server.

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

7. In the left-side server list, select the server that you want to manage and choose test > Databases > postgres > Schemas > public > Functions.

8. Right-click the function that you want to debug and choose Debugging > Debug in the shortcut menu.

9. On the Debugger page, specify input parameters for the function and click Debug. In this example, the values of the a and b parameters are set to 2, and the value of the a parameter is not null.

10. In the upper-left corner of the page, click the icon.

11. After the debugging is complete, view the debugging results on the Messages and Result tabs.

    The Messages tab displays the output text messages during the execution process of the function.

    The Result tab displays the return value after the function is executed.