
Trying Out Functions and Procedures in PL/pgSQL on Amazon Aurora DSQL
This page has been translated by machine translation. View original
Introduction
On November 19, 2026, PL/pgSQL support was added to Amazon Aurora DSQL.
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.
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.
