Stored procedures-AnalyticDB(AnalyticDB)-阿里云帮助中心

Stored procedures are named, precompiled sets of SQL statements that you call by name to run a defined sequence of operations. AnalyticDB for PostgreSQL V7.0 adds native stored procedure support, including the ability to commit and roll back transactions from within a procedure.

Stored procedures are not supported in the Data Management (DMS) console. Use the psql client instead.

When to use stored procedures

Stored procedures are useful when you need to run the same multi-step SQL logic repeatedly — for example, cleaning up stale records across multiple tables, processing batches of data on a schedule, or encapsulating complex business logic that multiple applications share.

Compared to functions, stored procedures offer two key advantages:

Transaction control inside the procedure. A stored procedure can commit or roll back individual transaction blocks during execution. Functions commit or roll back only when the entire function finishes.
SQL-standard syntax. Stored procedures use CREATE PROCEDURE and CALL, which complies with SQL syntax. This makes migrating existing stored procedure code from databases that support stored procedures to AnalyticDB for PostgreSQL straightforward — without the need to modify SQL statements.

Functions have return values; stored procedures do not. To return data from a stored procedure, use output parameters.

Capabilities

With stored procedures, you can:

Reuse business logic. Package SQL logic once and call it from any client or application that connects to the database.
Simplify maintenance. Update a stored procedure in one place instead of changing SQL logic across multiple applications.
Control access by procedure. Grant different user permissions to different stored procedures to help improve database security.

Syntax

Stored procedures in AnalyticDB for PostgreSQL follow PostgreSQL 12 syntax. For the full reference, see CREATE PROCEDURE.

Related SQL commands for managing stored procedures:

Command	Purpose
`CREATE PROCEDURE`	Define a new stored procedure
`CALL`	Execute a stored procedure

Examples

Control multiple transactions in a stored procedure

This example creates a stored procedure that runs two transactions: one that commits and one that rolls back.

Create 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;
$$;

Call the stored procedure.
```
CALL proc();
```
Verify the result. Run \d in psql to list all tables in the current database.
```
List of relations
 Schema |  Name   | Type  |   Owner    | Storage
--------+---------+-------+------------+---------
 public | table_a | table | adbpgadmin | heap
(1 row)
```
Only table_a appears. The COMMIT inside the procedure persisted its transaction; the ROLLBACK discarded table_b and its data.
Query table_a to confirm its contents.
```
SELECT * FROM table_a;
```
Expected output:
```
 id
----
  1
(1 row)
```

Commit within a loop to reclaim tablespace

This example compares a stored procedure and a function that both run a loop inserting and truncating rows. Because a function cannot commit during execution, it cannot release tablespace inside the loop.

Stored procedure — commits after each TRUNCATE, so the database reclaims physical storage on each iteration:

CREATE PROCEDURE run_procedure() LANGUAGE plpgsql
AS $$
  BEGIN
    FOR i IN 1..10 LOOP
      INSERT INTO t VALUES(i);  -- Insert a row into t.
      TRUNCATE t;
      COMMIT;  -- Commit the TRUNCATE to reclaim tablespace before the next iteration.
    END LOOP;
  END;
$$;

Function — cannot commit during execution, so the TRUNCATE does not release storage until the function returns:

CREATE FUNCTION run_function() RETURNS void LANGUAGE plpgsql
AS $$
  BEGIN
    FOR i IN 1..10 LOOP
      INSERT INTO t VALUES(i);  -- Insert a row into t.
      TRUNCATE t;
      -- COMMIT is not allowed here; transactions commit only when the function exits.
    END LOOP;
  END;
$$;

Use a stored procedure when you need to release resources incrementally inside a long-running loop.

What's next

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