Passing Complex Types to Functions

You will pass composite types and arrays into procedures to handle hierarchical data structures.

In this lesson, you will learn how to pass composite types and arrays into PostgreSQL procedures to manage hierarchical data structures effectively.

Handling Complexity via Composite Types

When you need to pass a group of related fields into a procedure—like an employee's name, ID, and department all at once—passing them as individual arguments becomes messy and error-prone. Instead, you can define a TYPE that acts as a custom container.

A composite type is essentially a user-defined row structure. By creating one, you treat a complex object as a single, first-class argument for your PL/pgSQL procedures.

Example: Processing Hierarchical Data

Imagine you have an organizational hierarchy. You might define a type to represent a node in your tree:

CREATE TYPE org_node AS (
    id INT,
    parent_id INT,
    data TEXT
);

CREATE PROCEDURE process_node(node_data org_node)
LANGUAGE plpgsql AS $$
BEGIN
    RAISE NOTICE 'Processing node %: %', node_data.id, node_data.data;
    -- Logic to handle nested hierarchies goes here
END;
$$;

Passing Arrays for Bulk Hierarchies

Often, hierarchical data arrives in batches. Passing a single composite type is great for a leaf node, but passing an array of those types allows you to iterate over an entire branch of a hierarchy in one call.

If you have an array defined as org_node[], you can loop through the collection inside your procedure using standard iteration techniques:

CREATE PROCEDURE process_batch(nodes org_node[])
LANGUAGE plpgsql AS $$
DECLARE
    n org_node;
BEGIN
    FOREACH n IN ARRAY nodes LOOP
        CALL process_node(n);
    END LOOP;
END;
$$;

By combining these structures, you move away from flat, procedural code and toward an object-oriented style of data manipulation. This is particularly useful when working with deep trees (like file systems or category hierarchies) where the relationship between records is just as important as the data itself. When you pass these types, PostgreSQL maps the memory structure directly into the procedure's execution context, allowing for highly efficient processing of nested relationships.

Key Takeaway: Using custom composite types and arrays allows you to encapsulate complex, hierarchical data into single function arguments, making your procedures cleaner, more modular, and easier to scale.