创建数组类型时无法打印V1.porttype数组的问题及类型定义

Hey there! Let's sort out how to properly print that custom array type and access its PortType field. Based on your type definitions, I bet you're running into trouble because you're trying to access the field directly on the array instead of individual elements. Here's what you need to do:

1. Print the Entire Array #

If you just want to output the full array (with all fields of each PORTDETAIL_EX element), you can reference the array field directly in a query or PL/pgSQL block. PostgreSQL will render it in its default composite array format.

Example with a Table #

Suppose you have a table using your PORTDETAILS type:

CREATE TABLE ont_ports (id SERIAL PRIMARY KEY, port_data PORTDETAILS);
-- Insert sample data
INSERT INTO ont_ports (port_data)
VALUES (('ONT-001', ARRAY[('GE', 'ACTIVE', 'Y'), ('FE', 'INACTIVE', 'N')]::PORTDETAIL_EX[], 1));

-- Query the full array
SELECT port_data.portdetailsarray FROM ont_ports;

Example with a PL/pgSQL Variable #

DO $$
DECLARE
    my_port PORTDETAILS;
BEGIN
    -- Assign sample data to the variable
    my_port := ('ONT-002', ARRAY[('PON', 'UP', 'Y'), ('ETH', 'DOWN', 'N')]::PORTDETAIL_EX[], 2);
    -- Print the entire array
    RAISE NOTICE 'Full Port Details Array: %', my_port.portdetailsarray;
END $$;

2. Print Individual PortType Values from the Array #

If you want to extract and print just the PortType field from each element in the array, you need to either:

• Use unnest() to expand the array into rows, then access the field
• Use array subscripts to target specific elements

Using unnest() (For All Elements) #

This is great if you want to list every PortType in the array:

-- For a table
SELECT (unnest(port_data.portdetailsarray)).porttype AS port_type
FROM ont_ports;

-- For a PL/pgSQL variable (loop through the array)
DO $$
DECLARE
    my_port PORTDETAILS;
    single_port PORTDETAIL_EX;
BEGIN
    my_port := ('ONT-002', ARRAY[('PON', 'UP', 'Y'), ('ETH', 'DOWN', 'N')]::PORTDETAIL_EX[], 2);
    -- Loop through each element in the array
    FOREACH single_port IN ARRAY my_port.portdetailsarray LOOP
        RAISE NOTICE 'Port Type: %', single_port.porttype;
    END LOOP;
END $$;

Using Array Subscripts (For Specific Elements) #

PostgreSQL arrays use 1-based indexing, so you can target individual elements directly:

DO $$
DECLARE
    my_port PORTDETAILS;
BEGIN
    my_port := ('ONT-002', ARRAY[('PON', 'UP', 'Y'), ('ETH', 'DOWN', 'N')]::PORTDETAIL_EX[], 2);
    -- Print the PortType of the first element
    RAISE NOTICE 'First Port Type: %', my_port.portdetailsarray[1].porttype;
    -- Print the PortType of the second element
    RAISE NOTICE 'Second Port Type: %', my_port.portdetailsarray[2].porttype;
END $$;

Key Notes to Avoid Issues #

• Don't try to access portdetailsarray.porttype directly — the array itself doesn't have a porttype field; you need to target individual elements first.
• Always check if the array is empty before using subscripts to avoid "array index out of bounds" errors. You can use array_length(my_port.portdetailsarray, 1) to get the number of elements.

内容的提问来源于stack exchange，提问作者srinivas yadav