AnalyticDB for PostgreSQL:pldbgapi

Overview

PL/pgSQL is an extensible programming 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

• You cannot manually create the pldbgapi extension. To install or upgrade the extension, contact technical support.

• 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 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. Create the pldbgapi extension.

   Note

   You cannot manually create the pldbgapi extension. To install or upgrade the extension, contact technical support.

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 is available.

   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 a and b are set to 2, and the value of a 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.