============================================================================
NESTED_STATEMENTS Feature - Comprehensive Test Suite
============================================================================

This test suite demonstrates:
  1. Validation (NESTED_STATEMENTS requires ANALYZE)
  2. Simple PL/pgSQL function (all level 1)
  3. PERFORM pattern (creates deeper nesting levels)
  4. Expression assignment pattern (stays at same level)
  5. Comparison: PERFORM vs expression assignment
  6. SQL function nesting (true SQL nesting)
  7. Three-level chain with PERFORM
  8. Recursive function (increasing levels)
  9. Exception handling blocks
  10. No nested statements (plain query)
  11. Trigger-fired nested statements
  12. Combined with VERBOSE and BUFFERS options
  13. Statement numbering = completion order (triggers demo)
  14. BEGIN/ROLLBACK safety pattern

psql:comprehensive_nested_statements_test.sql:36: NOTICE:  table "products" does not exist, skipping
DROP TABLE
CREATE TABLE
INSERT 0 3
============================================================================
TEST 1: Validation - NESTED_STATEMENTS requires ANALYZE
============================================================================

Expected: ERROR message

psql:comprehensive_nested_statements_test.sql:53: ERROR:  EXPLAIN option NESTED_STATEMENTS requires ANALYZE

============================================================================
TEST 2: Simple PL/pgSQL Function - All Statements at Level 1
============================================================================

Purpose: SQL statements in a single function all execute at level 1
         because they run sequentially in the same executor context.

CREATE FUNCTION
Expected: All 4 statements at level 1

                            QUERY PLAN                             
-------------------------------------------------------------------
 Result (actual rows=1.00 loops=1)
   Buffers: shared hit=186
 
 Nested Plans:
 
   Nested Statement #1 (level 1):
   Query Text: SELECT COUNT(*)          FROM products
   Aggregate (actual rows=1.00 loops=1)
     Buffers: shared hit=1
     ->  Seq Scan on products (actual rows=3.00 loops=1)
           Buffers: shared hit=1
 
   Nested Statement #2 (level 1):
   Query Text: INSERT INTO products VALUES (10, 'Temp', 1, 'Temp')
   Insert on products (actual rows=0.00 loops=1)
     Buffers: shared hit=1
     ->  Result (actual rows=1.00 loops=1)
 
   Nested Statement #3 (level 1):
   Query Text: UPDATE products SET price = price + 1 WHERE id = 1
   Update on products (actual rows=0.00 loops=1)
     Buffers: shared hit=3
     ->  Seq Scan on products (actual rows=1.00 loops=1)
           Filter: (id = 1)
           Rows Removed by Filter: 3
           Buffers: shared hit=1
 
   Nested Statement #4 (level 1):
   Query Text: DELETE FROM products WHERE id = 10
   Delete on products (actual rows=0.00 loops=1)
     Buffers: shared hit=2
     ->  Seq Scan on products (actual rows=1.00 loops=1)
           Filter: (id = 10)
           Rows Removed by Filter: 3
           Buffers: shared hit=1
(35 rows)


============================================================================
TEST 3: PERFORM Pattern - Creates Deeper Nesting Levels
============================================================================

Purpose: PERFORM func() creates a new executor call (SELECT func()),
         so statements inside the called function run at a deeper level.

How it works internally:
  PERFORM func() → executes "SELECT func()" → ExecutorRun increments level
  → func() body runs its SQL at the elevated level

CREATE FUNCTION
CREATE FUNCTION
Expected:
  Statement #1 (level 1): SELECT COUNT in outer_perform
  Statement #2 (level 2): UPDATE in inner_perform
  Statement #3 (level 2): INSERT in inner_perform
  Statement #4 (level 2): DELETE in inner_perform
  Statement #5 (level 1): SELECT inner_perform() [the PERFORM call]
  Statement #6 (level 1): UPDATE in outer_perform

                             QUERY PLAN                              
---------------------------------------------------------------------
 Result (actual rows=1.00 loops=1)
   Buffers: shared hit=62
 
 Nested Plans:
 
   Nested Statement #1 (level 1):
   Query Text: SELECT COUNT(*)          FROM products
   Aggregate (actual rows=1.00 loops=1)
     Buffers: shared hit=1
     ->  Seq Scan on products (actual rows=3.00 loops=1)
           Buffers: shared hit=1
 
   Nested Statement #2 (level 2):
   Query Text: UPDATE products SET price = price + 1 WHERE id = 2
   Update on products (actual rows=0.00 loops=1)
     Buffers: shared hit=3
     ->  Seq Scan on products (actual rows=1.00 loops=1)
           Filter: (id = 2)
           Rows Removed by Filter: 2
           Buffers: shared hit=1
 
   Nested Statement #3 (level 2):
   Query Text: INSERT INTO products VALUES (20, 'Inner', 50, 'Test')
   Insert on products (actual rows=0.00 loops=1)
     Buffers: shared hit=1
     ->  Result (actual rows=1.00 loops=1)
 
   Nested Statement #4 (level 2):
   Query Text: DELETE FROM products WHERE id = 20
   Delete on products (actual rows=0.00 loops=1)
     Buffers: shared hit=2
     ->  Seq Scan on products (actual rows=1.00 loops=1)
           Filter: (id = 20)
           Rows Removed by Filter: 3
           Buffers: shared hit=1
 
   Nested Statement #5 (level 1):
   Query Text: SELECT inner_perform()
   Result (actual rows=1.00 loops=1)
     Buffers: shared hit=6
 
   Nested Statement #6 (level 1):
   Query Text: UPDATE products SET price = price - 1 WHERE id = 2
   Update on products (actual rows=0.00 loops=1)
     Buffers: shared hit=3
     ->  Seq Scan on products (actual rows=1.00 loops=1)
           Filter: (id = 2)
           Rows Removed by Filter: 2
           Buffers: shared hit=1
(49 rows)


============================================================================
TEST 4: Expression Assignment (result := func()) - Same Level
============================================================================

Purpose: result := func() evaluates the function as an expression via
         ExecEvalFunc WITHOUT creating a new executor call. Statements
         inside the called function run at the SAME level as the caller.

How it works internally:
  result := func() → ExecEvalFunc(func) → NO new ExecutorRun
  → func() body runs its SQL at the SAME level as caller

CREATE FUNCTION
CREATE FUNCTION
Expected: ALL statements at level 1 (no deeper nesting)
  Statement #1 (level 1): SELECT COUNT in outer_expr
  Statement #2 (level 1): SELECT SUM in inner_expr  ← same level!
  Statement #3 (level 1): UPDATE in inner_expr      ← same level!
  Statement #4 (level 1): DELETE in outer_expr

                            QUERY PLAN                            
------------------------------------------------------------------
 Result (actual rows=1.00 loops=1)
   Buffers: shared hit=203
 
 Nested Plans:
 
   Nested Statement #1 (level 1):
   Query Text: SELECT COUNT(*)          FROM products
   Aggregate (actual rows=1.00 loops=1)
     Buffers: shared hit=1
     ->  Seq Scan on products (actual rows=3.00 loops=1)
           Buffers: shared hit=1
 
   Nested Statement #2 (level 1):
   Query Text: SELECT SUM(price)            FROM products
   Aggregate (actual rows=1.00 loops=1)
     Buffers: shared hit=1
     ->  Seq Scan on products (actual rows=3.00 loops=1)
           Buffers: shared hit=1
 
   Nested Statement #3 (level 1):
   Query Text: UPDATE products SET price = price + 1 WHERE id = 3
   Update on products (actual rows=0.00 loops=1)
     Buffers: shared hit=3
     ->  Seq Scan on products (actual rows=1.00 loops=1)
           Filter: (id = 3)
           Rows Removed by Filter: 2
           Buffers: shared hit=1
 
   Nested Statement #4 (level 1):
   Query Text: DELETE FROM products WHERE price > 9999
   Delete on products (actual rows=0.00 loops=1)
     Buffers: shared hit=1
     ->  Seq Scan on products (actual rows=0.00 loops=1)
           Filter: (price > '9999'::numeric)
           Rows Removed by Filter: 3
           Buffers: shared hit=1
(36 rows)


============================================================================
TEST 5: PERFORM vs Expression Assignment - Side by Side
============================================================================

Purpose: Same inner function called two different ways to show the
         nesting level difference.

CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
--- Via PERFORM (expect level 2 for inner SELECT): ---

                                     QUERY PLAN                                      
-------------------------------------------------------------------------------------
 Result (actual rows=1.00 loops=1)
   Buffers: shared hit=30
 
 Nested Plans:
 
   Nested Statement #1 (level 2):
   Query Text: SELECT COUNT(*)          FROM products WHERE category = 'Electronics'
   Aggregate (actual rows=1.00 loops=1)
     Buffers: shared hit=1
     ->  Seq Scan on products (actual rows=2.00 loops=1)
           Filter: (category = 'Electronics'::text)
           Rows Removed by Filter: 1
           Buffers: shared hit=1
 
   Nested Statement #2 (level 1):
   Query Text: SELECT shared_inner()
   Result (actual rows=1.00 loops=1)
     Buffers: shared hit=21
(18 rows)


--- Via expression assignment (expect level 1 for inner SELECT): ---

                                     QUERY PLAN                                      
-------------------------------------------------------------------------------------
 Result (actual rows=1.00 loops=1)
   Buffers: shared hit=1
 
 Nested Plans:
 
   Nested Statement #1 (level 1):
   Query Text: SELECT COUNT(*)          FROM products WHERE category = 'Electronics'
   Aggregate (actual rows=1.00 loops=1)
     Buffers: shared hit=1
     ->  Seq Scan on products (actual rows=2.00 loops=1)
           Filter: (category = 'Electronics'::text)
           Rows Removed by Filter: 1
           Buffers: shared hit=1
(13 rows)


Notice: Same function, different nesting levels depending on call pattern.

============================================================================
TEST 6: SQL Functions - True SQL Execution Nesting
============================================================================

Purpose: SQL functions execute DURING the parent query, creating true
         SQL nesting where inner functions complete before outer.

psql:comprehensive_nested_statements_test.sql:245: NOTICE:  table "t1" does not exist, skipping
psql:comprehensive_nested_statements_test.sql:245: NOTICE:  table "t2" does not exist, skipping
psql:comprehensive_nested_statements_test.sql:245: NOTICE:  table "t3" does not exist, skipping
DROP TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
INSERT 0 1
INSERT 0 1
INSERT 0 1
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
Expected: Deepest-first execution order (3→2→1)
  Statement #1 (level 3): SELECT from t3 (sql_level3)
  Statement #2 (level 2): SELECT from t2 (sql_level2)
  Statement #3 (level 1): SELECT from t1 (plpgsql_sql_caller)

                                    QUERY PLAN                                     
-----------------------------------------------------------------------------------
 Result (actual rows=1.00 loops=1)
   Buffers: shared hit=36
 
 Nested Plans:
 
   Nested Statement #1 (level 3):
   Query Text: 
     SELECT data FROM t3 WHERE id = 1;
 
   Seq Scan on t3 (actual rows=1.00 loops=1)
     Filter: (id = 1)
     Buffers: shared hit=1
 
   Nested Statement #2 (level 2):
   Query Text: 
     SELECT data || '+' || sql_level3() FROM t2 WHERE id = 1;
 
   Seq Scan on t2 (actual rows=1.00 loops=1)
     Filter: (id = 1)
     Buffers: shared hit=8
 
   Nested Statement #3 (level 1):
   Query Text: SELECT data || '+' || sql_level2()             FROM t1 WHERE id = 1
   Seq Scan on t1 (actual rows=1.00 loops=1)
     Filter: (id = 1)
     Buffers: shared hit=15
(26 rows)


============================================================================
TEST 7: Three-Level PL/pgSQL Chain (via PERFORM)
============================================================================

Purpose: Each PERFORM adds one executor level

CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
Expected:
  Statement #1 (level 1): SELECT COUNT(*) FROM products
  Statement #2 (level 2): SELECT COUNT(*) WHERE id = 1
  Statement #3 (level 3): SELECT COUNT(*) WHERE category = Books
  Statement #4 (level 2): SELECT chain_level3()
  Statement #5 (level 1): SELECT chain_level2()

                              QUERY PLAN                              
----------------------------------------------------------------------
 Result (actual rows=1.00 loops=1)
   Buffers: shared hit=24
 
 Nested Plans:
 
   Nested Statement #1 (level 1):
   Query Text: SELECT COUNT(*) FROM products
   Aggregate (actual rows=1.00 loops=1)
     Buffers: shared hit=1
     ->  Seq Scan on products (actual rows=3.00 loops=1)
           Buffers: shared hit=1
 
   Nested Statement #2 (level 2):
   Query Text: SELECT COUNT(*) FROM products WHERE id = 1
   Aggregate (actual rows=1.00 loops=1)
     Buffers: shared hit=1
     ->  Seq Scan on products (actual rows=1.00 loops=1)
           Filter: (id = 1)
           Rows Removed by Filter: 2
           Buffers: shared hit=1
 
   Nested Statement #3 (level 3):
   Query Text: SELECT COUNT(*) FROM products WHERE category = 'Books'
   Aggregate (actual rows=1.00 loops=1)
     Buffers: shared hit=1
     ->  Seq Scan on products (actual rows=1.00 loops=1)
           Filter: (category = 'Books'::text)
           Rows Removed by Filter: 2
           Buffers: shared hit=1
 
   Nested Statement #4 (level 2):
   Query Text: SELECT chain_level3()
   Result (actual rows=1.00 loops=1)
     Buffers: shared hit=1
 
   Nested Statement #5 (level 1):
   Query Text: SELECT chain_level2()
   Result (actual rows=1.00 loops=1)
     Buffers: shared hit=11
(39 rows)


============================================================================
TEST 8: Recursive Function - Increasing Nesting Levels
============================================================================

Purpose: Recursive PERFORM calls increase the nesting level each time

psql:comprehensive_nested_statements_test.sql:333: NOTICE:  table "counter_log" does not exist, skipping
DROP TABLE
CREATE TABLE
CREATE FUNCTION
Expected:
  Statement #1 (level 1): INSERT with n=3
  Statement #2 (level 2): INSERT with n=2
  Statement #3 (level 3): INSERT with n=1
  + PERFORM calls at levels 2 and 1

                        QUERY PLAN                        
----------------------------------------------------------
 Result (actual rows=1.00 loops=1)
   Buffers: shared hit=44 dirtied=1 written=1
 
 Nested Plans:
 
   Nested Statement #1 (level 1):
   Query Text: INSERT INTO counter_log VALUES (n, n * 10)
   Insert on counter_log (actual rows=0.00 loops=1)
     Buffers: shared dirtied=1 written=1
     ->  Result (actual rows=1.00 loops=1)
 
   Nested Statement #2 (level 2):
   Query Text: INSERT INTO counter_log VALUES (n, n * 10)
   Insert on counter_log (actual rows=0.00 loops=1)
     Buffers: shared hit=1
     ->  Result (actual rows=1.00 loops=1)
 
   Nested Statement #3 (level 3):
   Query Text: INSERT INTO counter_log VALUES (n, n * 10)
   Insert on counter_log (actual rows=0.00 loops=1)
     Buffers: shared hit=1
     ->  Result (actual rows=1.00 loops=1)
 
   Nested Statement #4 (level 2):
   Query Text: SELECT recursive_func(n - 1)
   Result (actual rows=1.00 loops=1)
     Buffers: shared hit=1
 
   Nested Statement #5 (level 1):
   Query Text: SELECT recursive_func(n - 1)
   Result (actual rows=1.00 loops=1)
     Buffers: shared hit=2
(32 rows)


============================================================================
TEST 9: Exception Handling - Statements in BEGIN/EXCEPTION Blocks
============================================================================

Purpose: Verify statements in exception handlers are captured

psql:comprehensive_nested_statements_test.sql:367: NOTICE:  table "safe_table" does not exist, skipping
DROP TABLE
CREATE TABLE
INSERT 0 1
CREATE FUNCTION
Expected:
  - UPDATE (level 1): initial update
  - UPDATE (level 1): recovery in exception handler
  - The failed INSERT is rolled back and may not appear

                                    QUERY PLAN                                     
-----------------------------------------------------------------------------------
 Result (actual rows=1.00 loops=1)
   Buffers: shared hit=23
 
 Nested Plans:
 
   Nested Statement #1 (level 1):
   Query Text: UPDATE safe_table SET data = 'updated' WHERE id = 1
   Update on safe_table (actual rows=0.00 loops=1)
     Buffers: shared hit=4
     ->  Index Scan using safe_table_pkey on safe_table (actual rows=1.00 loops=1)
           Index Cond: (id = 1)
           Index Searches: 1
           Buffers: shared hit=2
 
   Nested Statement #2 (level 1):
   Query Text: UPDATE safe_table SET data = 'recovered' WHERE id = 1
   Update on safe_table (actual rows=0.00 loops=1)
     Buffers: shared hit=4
     ->  Index Scan using safe_table_pkey on safe_table (actual rows=1.00 loops=1)
           Index Cond: (id = 1)
           Index Searches: 1
           Buffers: shared hit=2
(22 rows)


============================================================================
TEST 10: No Nested Statements - Plain Query
============================================================================

Purpose: When no nested statements execute, no "Nested Plans:" section

Expected: Normal EXPLAIN output, no Nested Plans section

                   QUERY PLAN                    
-------------------------------------------------
 Seq Scan on products (actual rows=1.00 loops=1)
   Filter: (id = 1)
   Rows Removed by Filter: 2
   Buffers: shared hit=1
(4 rows)


============================================================================
TEST 11: Trigger-Fired Nested Statements
============================================================================

Purpose: Triggers fire DURING the triggering statement, creating
         deeper nesting. Note: trigger statements may appear BEFORE
         their parent (completion order, not start order).

psql:comprehensive_nested_statements_test.sql:424: NOTICE:  table "orders" does not exist, skipping
DROP TABLE
psql:comprehensive_nested_statements_test.sql:425: NOTICE:  table "audit_log" does not exist, skipping
DROP TABLE
CREATE TABLE
CREATE TABLE
CREATE FUNCTION
CREATE TRIGGER
CREATE FUNCTION
Expected:
  - INSERT INTO audit_log (level 2): trigger from INSERT
  - INSERT INTO orders (level 1): the triggering statement
  - INSERT INTO audit_log (level 2): trigger from UPDATE
  - UPDATE orders (level 1): the triggering statement

Note: Level 2 statements appear before level 1 because triggers
      complete (ExecutorEnd) before the parent statement completes.

                                QUERY PLAN                                 
---------------------------------------------------------------------------
 Result (actual rows=1.00 loops=1)
   Buffers: shared hit=76 read=1 dirtied=4 written=3
 
 Nested Plans:
 
   Nested Statement #1 (level 2):
   Query Text: INSERT INTO audit_log (order_id, action)
     VALUES (NEW.id, TG_OP || ': ' || NEW.status)
   Insert on audit_log (actual rows=0.00 loops=1)
     Buffers: shared dirtied=1 written=1
     ->  Result (actual rows=1.00 loops=1)
 
   Nested Statement #2 (level 1):
   Query Text: INSERT INTO orders VALUES (p_id, 99.99, 'new')
   Insert on orders (actual rows=0.00 loops=1)
     Buffers: shared hit=14 read=1 dirtied=3 written=2
     ->  Result (actual rows=1.00 loops=1)
 
   Nested Statement #3 (level 2):
   Query Text: INSERT INTO audit_log (order_id, action)
     VALUES (NEW.id, TG_OP || ': ' || NEW.status)
   Insert on audit_log (actual rows=0.00 loops=1)
     Buffers: shared hit=1
     ->  Result (actual rows=1.00 loops=1)
 
   Nested Statement #4 (level 1):
   Query Text: UPDATE orders SET status = 'processed' WHERE id = p_id
   Update on orders (actual rows=0.00 loops=1)
     Buffers: shared hit=5
     ->  Index Scan using orders_pkey on orders (actual rows=1.00 loops=1)
           Index Cond: (id = 1)
           Index Searches: 1
           Buffers: shared hit=2
(33 rows)


============================================================================
TEST 12: NESTED_STATEMENTS with VERBOSE and BUFFERS
============================================================================

Purpose: VERBOSE and BUFFERS options are inherited by nested plans

CREATE FUNCTION
Expected: Schema-qualified names (public.products) and Output columns

                                             QUERY PLAN                                              
-----------------------------------------------------------------------------------------------------
 Result  (cost=0.00..0.26 rows=1 width=4) (actual rows=1.00 loops=1)
   Output: verbose_func()
   Buffers: shared hit=4
 
 Nested Plans:
 
   Nested Statement #1 (level 1):
   Query Text: SELECT COUNT(*)          FROM products
   Aggregate  (cost=17.88..17.89 rows=1 width=8) (actual rows=1.00 loops=1)
     Output: count(*)
     Buffers: shared hit=1
     ->  Seq Scan on public.products  (cost=0.00..16.30 rows=630 width=0) (actual rows=3.00 loops=1)
           Output: id, name, price, category
           Buffers: shared hit=1
(14 rows)


============================================================================
TEST 13: Statement Numbering Shows Completion Order
============================================================================

Purpose: Statement #N reflects when a statement FINISHES (ExecutorEnd),
         not when it starts. This is most visible with triggers:

  Timeline for INSERT with an AFTER trigger:
    1. Parent INSERT starts executing
    2. Trigger fires → trigger INSERT starts → trigger INSERT FINISHES → gets #1
    3. Parent INSERT FINISHES → gets #2

  The trigger statement finishes INSIDE the parent, so it gets a lower number.

psql:comprehensive_nested_statements_test.sql:507: NOTICE:  table "demo_orders" does not exist, skipping
DROP TABLE
psql:comprehensive_nested_statements_test.sql:508: NOTICE:  table "demo_log" does not exist, skipping
DROP TABLE
CREATE TABLE
CREATE TABLE
CREATE FUNCTION
CREATE TRIGGER
CREATE FUNCTION
Expected numbering (completion order):
  #1 (level 2): INSERT INTO demo_log  ← trigger finishes first
  #2 (level 1): INSERT INTO demo_orders (Widget) ← parent finishes second
  #3 (level 2): INSERT INTO demo_log  ← second trigger finishes
  #4 (level 1): INSERT INTO demo_orders (Gadget) ← second parent finishes

                                QUERY PLAN                                
--------------------------------------------------------------------------
 Result (actual rows=1.00 loops=1)
   Buffers: shared hit=41 dirtied=2 written=2
 
 Nested Plans:
 
   Nested Statement #1 (level 2):
   Query Text: INSERT INTO demo_log VALUES ('order placed: ' || NEW.item)
   Insert on demo_log (actual rows=0.00 loops=1)
     Buffers: shared dirtied=1 written=1
     ->  Result (actual rows=1.00 loops=1)
 
   Nested Statement #2 (level 1):
   Query Text: INSERT INTO demo_orders VALUES (1, 'Widget')
   Insert on demo_orders (actual rows=0.00 loops=1)
     Buffers: shared dirtied=1 written=1
     ->  Result (actual rows=1.00 loops=1)
 
   Nested Statement #3 (level 2):
   Query Text: INSERT INTO demo_log VALUES ('order placed: ' || NEW.item)
   Insert on demo_log (actual rows=0.00 loops=1)
     Buffers: shared hit=1
     ->  Result (actual rows=1.00 loops=1)
 
   Nested Statement #4 (level 1):
   Query Text: INSERT INTO demo_orders VALUES (2, 'Gadget')
   Insert on demo_orders (actual rows=0.00 loops=1)
     Buffers: shared hit=1
     ->  Result (actual rows=1.00 loops=1)
(28 rows)


Key takeaway: Lower statement number = finished earlier.
Triggers finish inside their parent, so they always get lower numbers.

============================================================================
TEST 14: BEGIN/ROLLBACK - Safe Analysis of Data-Modifying Functions
============================================================================

Purpose: Demonstrate the recommended pattern for safely analyzing
         functions that modify data without persisting changes.

Pattern:
  BEGIN;
  EXPLAIN (ANALYZE, NESTED_STATEMENTS) SELECT my_function();
  ROLLBACK;

The function executes (so we get real plans with actual rows),
but ROLLBACK undoes all changes.

psql:comprehensive_nested_statements_test.sql:564: NOTICE:  table "safe_orders" does not exist, skipping
DROP TABLE
CREATE TABLE
INSERT 0 2
CREATE FUNCTION
--- Before: ---
 id |  item  | status  
----+--------+---------
  1 | Widget | pending
  2 | Gadget | pending
(2 rows)


--- EXPLAIN inside BEGIN/ROLLBACK: ---
BEGIN
                                     QUERY PLAN                                      
-------------------------------------------------------------------------------------
 Result (actual rows=1.00 loops=1)
   Buffers: shared hit=11
 
 Nested Plans:
 
   Nested Statement #1 (level 1):
   Query Text: UPDATE safe_orders SET status = 'processing' WHERE status = 'pending'
   Update on safe_orders (actual rows=0.00 loops=1)
     Buffers: shared hit=5
     ->  Seq Scan on safe_orders (actual rows=2.00 loops=1)
           Filter: (status = 'pending'::text)
           Buffers: shared hit=1
 
   Nested Statement #2 (level 1):
   Query Text: INSERT INTO safe_orders VALUES (3, 'Bonus', 'new')
   Insert on safe_orders (actual rows=0.00 loops=1)
     Buffers: shared hit=1
     ->  Result (actual rows=1.00 loops=1)
 
   Nested Statement #3 (level 1):
   Query Text: DELETE FROM safe_orders WHERE item = 'Gadget'
   Delete on safe_orders (actual rows=0.00 loops=1)
     Buffers: shared hit=2
     ->  Seq Scan on safe_orders (actual rows=1.00 loops=1)
           Filter: (item = 'Gadget'::text)
           Rows Removed by Filter: 2
           Buffers: shared hit=1
(27 rows)

ROLLBACK

--- After ROLLBACK (data unchanged): ---
 id |  item  | status  
----+--------+---------
  1 | Widget | pending
  2 | Gadget | pending
(2 rows)


Result: We got full execution plans with actual row counts,
        but the data is unchanged after ROLLBACK.

DROP FUNCTION
DROP FUNCTION
DROP FUNCTION
DROP FUNCTION
DROP FUNCTION
DROP FUNCTION
DROP FUNCTION
DROP FUNCTION
DROP FUNCTION
DROP FUNCTION
DROP FUNCTION
DROP FUNCTION
DROP FUNCTION
DROP FUNCTION
DROP FUNCTION
DROP FUNCTION
DROP FUNCTION
DROP TRIGGER
DROP FUNCTION
DROP FUNCTION
DROP FUNCTION
DROP TRIGGER
DROP FUNCTION
DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE
DROP FUNCTION
============================================================================
Test Suite Complete
============================================================================

KEY FINDING: Nesting levels reflect executor call stack depth.

  PERFORM func() / SELECT func() INTO var:
    → Creates new ExecutorRun → deeper nesting level

  result := func():
    → ExecEvalFunc (no new ExecutorRun) → SAME nesting level

Tests Covered:
  1.  Validation (requires ANALYZE)
  2.  Simple PL/pgSQL (all level 1)
  3.  PERFORM pattern (creates deeper levels)
  4.  Expression assignment (stays at same level)
  5.  Side-by-side comparison of both patterns
  6.  SQL function nesting (true SQL nesting)
  7.  Three-level chain with PERFORM
  8.  Recursive function (increasing levels)
  9.  Exception handling blocks
  10. No nested statements (plain query)
  11. Trigger-fired nested statements
  12. Combined with VERBOSE and BUFFERS
  13. Statement numbering = completion order (triggers demo)
  14. BEGIN/ROLLBACK safety pattern
============================================================================