Trying Out Functions and Procedures in PL/pgSQL on Amazon Aurora DSQL

Trying Out Functions and Procedures in PL/pgSQL on Amazon Aurora DSQL

With PL/pgSQL now supported in Amazon Aurora DSQL, I built a procedure using IF/ELSE, loops and variable assignment to update three tables. Running the same 100 items via one CALL versus 300 separate SQL statements showed a shorter runtime.
2026.11.20

This page has been translated by machine translation. View original

Introduction

On November 19, 2026, PL/pgSQL support was added to Amazon Aurora DSQL.

https://aws.amazon.com/about-aws/whats-new/2026/11/aurora-dsql-plpgsql-support/

With PL/pgSQL, you can create and run functions, procedures, and anonymous blocks inside Aurora DSQL. You can write conditional branching, loops, and variable assignment combined with SQL, and by processing logic on the database, you can reduce the number of SQL statements executed from the client.

In this article, I create a cluster, run functions and procedures, and compare the elapsed time against running the same processing as individual SQL statements.

Test Environment

  • Aurora DSQL region: ap-northeast-1 (single-Region cluster)
  • Execution environment: local PC (Linux 7.2.12, aarch64)
  • psql: 18.6
  • AWS CLI: 2.41.2

Creating and Connecting to the Cluster

I created the Aurora DSQL cluster with the following command.

aws dsql create-cluster --region ap-northeast-1 --tags Name=devio-plpgsql-verify --no-deletion-protection-enabled

I confirmed with the following command that the status had become ACTIVE.

aws dsql get-cluster --region ap-northeast-1 --identifier <クラスター識別子>

For connecting, I created dsql-env.sh, which sets environment variables, with the following contents, loaded it with source, and then connected with psql.

export DSQL_ENDPOINT=<クラスター識別子>.dsql.ap-northeast-1.on.aws
export PGPASSWORD=$(aws dsql generate-db-connect-admin-auth-token --hostname "$DSQL_ENDPOINT" --region ap-northeast-1)
export PGSSLMODE=verify-full
export PGSSLROOTCERT=system
export PGHOST=$DSQL_ENDPOINT PGUSER=admin PGDATABASE=postgres
source dsql-env.sh
psql

Creating and Running Functions, Procedures, and Anonymous Blocks

I ran CREATE FUNCTION, CREATE PROCEDURE, CALL, and DO.

CREATE TABLE my_table (id integer PRIMARY KEY, value text);
CREATE TABLE
CREATE FUNCTION increment(val integer) RETURNS integer
LANGUAGE plpgsql AS $$
BEGIN
  RETURN val + 1;
END;
$$;
CREATE FUNCTION
SELECT increment(41);
 increment 
-----------
        42
(1 行)

CREATE PROCEDURE insert_row(p_id integer, p_value text)
LANGUAGE plpgsql AS $$
BEGIN
  INSERT INTO my_table VALUES (p_id, p_value);
END;
$$;
CREATE PROCEDURE
CALL insert_row(1, 'hello');
CALL
DO $$
BEGIN
  INSERT INTO my_table VALUES (2, 'from do block');
END;
$$;
DO
SELECT * FROM my_table ORDER BY id;
 id |     value     
----+---------------
  1 | hello
  2 | from do block
(2 行)

A Procedure That Operates on Multiple Tables

As a sample DB imitating simple order processing, I prepared products (items), inventory (stock), and order history (order_log). I tested whether processing that spans multiple tables can be run in a procedure.

Procedure Definition

I ran the following SQL.

Table and procedure definitions
CREATE TABLE items (item_id integer PRIMARY KEY, name text, price integer);
CREATE TABLE stock (item_id integer PRIMARY KEY, qty integer);
CREATE TABLE order_log (
  order_id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
  batch text,
  item_id integer,
  qty integer,
  amount integer
);

INSERT INTO items VALUES (1, 'pen', 100), (2, 'notebook', 250), (3, 'bag', 1200);
INSERT INTO stock VALUES (1, 1000000), (2, 1000000), (3, 1000000);

CREATE PROCEDURE process_orders(p_batch text, p_count integer)
LANGUAGE plpgsql AS $$
DECLARE
  v_item   integer;
  v_qty    integer;
  v_price  integer;
  v_amount integer;
BEGIN
  FOR i IN 1..p_count LOOP
    v_item := (i % 3) + 1;
    v_qty  := (i % 12) + 1;

    SELECT price INTO v_price FROM items WHERE item_id = v_item;

    IF v_qty >= 10 THEN
      v_amount := v_price * v_qty * 9 / 10;
    ELSE
      v_amount := v_price * v_qty;
    END IF;

    UPDATE stock SET qty = qty - v_qty WHERE item_id = v_item;
    INSERT INTO order_log (batch, item_id, qty, amount)
      VALUES (p_batch, v_item, v_qty, v_amount);
  END LOOP;
END;
$$;

Execution Results

I ran a CALL that processes 5 orders, then checked the contents of stock and order_log afterward.

CALL process_orders('smoke', 5);
CALL
SELECT * FROM stock ORDER BY item_id;
 item_id |  qty   
---------+--------
       1 | 999996
       2 | 999993
       3 | 999991
(3 行)

SELECT batch, item_id, qty, amount FROM order_log WHERE batch = 'smoke' ORDER BY item_id, qty;
 batch | item_id | qty | amount 
-------+---------+-----+--------
 smoke |       1 |   4 |    400
 smoke |       2 |   2 |    500
 smoke |       2 |   5 |   1250
 smoke |       3 |   3 |   3600
 smoke |       3 |   6 |   7200
(5 行)

Elapsed Time: Procedure vs. Individual SQL Statements

I ran the same 100-order processing with a single CALL and with individual SQL statements, and compared the elapsed times.

The Three Methods Compared

  • A single CALL (pass 100 orders to process_orders)
  • Run individual SQL statements (300 statements) with psql -f (auto-commit per statement)
  • Run the same 300 statements with psql --single-transaction -f (one transaction overall)

The SQL for the single CALL is as follows.

CALL process_orders('call-r1', 100);

The individual statements are three per order: SELECT, UPDATE, and INSERT. Every method uses a single connection. The elapsed time is the difference between the times obtained with the date command before the psql process starts and after it ends.

In the individual-statement approach, the SELECT result is not passed to subsequent statements. Therefore the IF determination is done in the generation script, and the price is retrieved from items with INSERT ... SELECT. The generation script is as follows.

Generation script for individual SQL statements 03-gen-statements.sh
#!/usr/bin/env bash
# 使い方: 03-gen-statements.sh <batch> <count>
# process_orders と同じ処理を、個別の SQL 文(1 回あたり 3 文)で標準出力へ書く。
# IF(数量 10 以上は 1 割引)はここで判定し、割引後の式を INSERT へ埋め込む。
set -euo pipefail
batch=$1; count=$2
for ((i = 1; i <= count; i++)); do
  item=$(( i % 3 + 1 ))
  qty=$(( i % 12 + 1 ))
  if (( qty >= 10 )); then expr="price * $qty * 9 / 10"; else expr="price * $qty"; fi
  echo "SELECT price FROM items WHERE item_id = $item;"
  echo "UPDATE stock SET qty = qty - $qty WHERE item_id = $item;"
  echo "INSERT INTO order_log (batch, item_id, qty, amount) SELECT '$batch', $item, $qty, $expr FROM items WHERE item_id = $item;"
done

The following are the commands run in round 1. For rounds 2 to 5, I changed r1 in the batch names to r2 to r5 and ran them.

# Generate the files of individual SQL statements (300 statements)
./03-gen-statements.sh "auto-r1" 100 > "/tmp/auto-r1.sql"
./03-gen-statements.sh "single-r1" 100 > "/tmp/single-r1.sql"

# A single CALL
psql -q -o /dev/null -v ON_ERROR_STOP=1 -c "CALL process_orders('call-r1', 100)"

# Individual SQL statements (auto-commit per statement)
psql -q -o /dev/null -v ON_ERROR_STOP=1 -f "/tmp/auto-r1.sql"

# Individual SQL statements (one transaction overall)
psql -q -o /dev/null -v ON_ERROR_STOP=1 --single-transaction -f "/tmp/single-r1.sql"

Measurement Results

The unit is seconds. In each round, I measured each method once.

Round CALL (1 statement) psql -f (300 statements, auto-commit per statement) --single-transaction (300 statements, one transaction overall)
1 0.574 6.646 5.402
2 0.251 6.651 3.495
3 0.219 6.447 3.717
4 0.244 6.447 3.589
5 0.219 4.918 4.938
Median 0.244 6.447 3.717

COMMIT and ROLLBACK Inside a Procedure

The user guide explains that when a procedure is called at the top level, it can include COMMIT and ROLLBACK. I ran, with a top-level CALL, a procedure that places an INSERT before COMMIT, between COMMIT and ROLLBACK, and after ROLLBACK.

CREATE PROCEDURE txn_demo()
LANGUAGE plpgsql AS $$
BEGIN
  INSERT INTO order_log (batch, item_id, qty, amount) VALUES ('txn-1-before-commit', 1, 1, 100);
  COMMIT;
  INSERT INTO order_log (batch, item_id, qty, amount) VALUES ('txn-2-before-rollback', 1, 1, 100);
  ROLLBACK;
  INSERT INTO order_log (batch, item_id, qty, amount) VALUES ('txn-3-after', 1, 1, 100);
END;
$$;
CREATE PROCEDURE
CALL txn_demo();
CALL
SELECT batch FROM order_log WHERE batch LIKE 'txn-%' ORDER BY batch;
        batch        
---------------------
 txn-1-before-commit
 txn-3-after
(2 行)

Only the txn-2 row, inserted immediately before ROLLBACK, was rolled back, confirming the behavior of COMMIT and ROLLBACK inside a procedure.

Unsupported Features

Unsupported features are listed in the user guide.

https://docs.aws.amazon.com/aurora-dsql/latest/userguide/working-with-plpgsql.html

Summary

I confirmed that functions and procedures can be created and run with PL/pgSQL in Aurora DSQL.

In the measurements of this 100-order processing, I confirmed that turning it into a procedure reduced the processing time.

With Aurora DSQL, the network cost of each SQL execution tends to be high. As one way to improve the performance of applications involving multiple steps or transaction processing, please try this update.

Share this article

AWSのお困り事はクラスメソッドへ