AnalyticDB for PostgreSQL:Stored procedures

Benefits

The engine version of AnalyticDB for PostgreSQL V7.0 is upgraded to PostgreSQL 12. This facilitates the implementation of stored procedures. Stored procedures provide the following benefits:

• A stored procedure integrates a set of SQL statements that define specific business logic. This makes stored procedures easy to maintain and significantly improves the efficiency of database developers.

• The process of calling stored procedures is simple. Database developers can efficiently reuse stored procedures in different business scenarios.

• Different stored procedures can be granted different user permissions to help improve database security.

Usage notes

Stored procedures cannot be used in the Data Management (DMS) console. We recommend that you use stored procedures on the psql client.

Syntax and parameters

For more information, see CREATE PROCEDURE.

Differences between stored procedures and functions

AnalyticDB for PostgreSQL V6.0 can use functions to implement most features of stored procedures. We recommend that you use functions. However, stored procedures are still required by AnalyticDB for PostgreSQL users and PostgreSQL developers because of the following differences between stored procedures and functions:

• Stored procedures support the CREATE PROCEDURE statement and the call() function, which complies with SQL syntax. You can easily migrate data from a database that supports stored procedures to AnalyticDB for PostgreSQL.

• Stored procedures allow you to commit or roll back transactions based on internal transaction blocks. Functions allow you to commit or roll back only an entire transaction.

• Functions have return values, but stored procedures do not. You can obtain the results of stored procedures by using the output parameter.

Examples

Example 1

In this example, a stored procedure is created to control two transactions. One is committed and the other is rolled back.

1. Create a stored procedure and control different transactions in the stored procedure.

   CREATE PROCEDURE proc() LANGUAGE plpgsql
   AS $$
     BEGIN
       CREATE TABLE table_a (id int);
       INSERT INTO table_a VALUES(1);
       COMMIT;
   
       CREATE TABLE table_b (cid int);
       INSERT INTO table_b VALUES(1);
       ROLLBACK;
     END;
   $$;

2. Call the stored procedure.

    CALL proc();

3. Query the execution result of the stored procedure.

   Run the \d command on psql to query all tables in the current database. Sample result:

                   List of relations
    Schema |  Name   | Type  |   Owner    | Storage
   --------+---------+-------+------------+---------
    public | table_a | table | adbpgadmin | heap
   (1 row)

   Execute the following statement to query data in the table_a table:

   SELECT * FROM table_a;

   Sample result:

    id
   ----
     1
   (1 row)

   The execution result of the preceding stored procedure shows that the transaction on the table_a table is committed and the transaction on the table_b table is rolled back.

Example 2

In this example, a loop task is executed in both a stored procedure and a function, during which the system attempts to reclaim the storage of the relevant table. This example shows the difference between stored procedures and functions in committing transactions.

• Stored procedures allow you to commit transactions. The following code shows that a stored procedure can reclaim tablespace during the execution of a loop task:

  CREATE PROCEDURE run_procedure()  LANGUAGE plpgsql
  AS $$
   BEGIN
      FOR i in 1..10 LOOP
          INSERT INTO t VALUES(i);-- Execute multiple INSERT tasks on the t table. 
          TRUNCATE t;
          COMMIT;  -- Commit a TRUNCATE task within the loop to reclaim the physical storage of the t table. 
      END LOOP;
    END;
  $$;

• Functions cannot commit transactions during execution. They can be committed only after the function execution is complete. The following code shows that a function cannot reclaim tablespace during the execution of a loop task:

  CREATE FUNCTION run_function() RETURNS void LANGUAGE plpgsql
  AS $$
   BEGIN
      FOR i in 1..10 LOOP
          INSERT INTO t VALUES(i);-- Execute multiple INSERT tasks on the t table. 
          TRUNCATE t;
          -- COMMIT; Transactions cannot be committed. 
      END LOOP;
    END;
  $$;

References

Use AnalyticDB for PostgreSQL to help ColourData implement full-text search, data processing, and data analysis