PostgreSQL: how to return composite type

Question

I'm trying to get a composite value with a stored function in PostreSQL as follows. I created a type called PersonId, and I used the type in a table called Person.

And I inserted values into the table.

CREATE TYPE PersonId AS
(
    id      VARCHAR(32),
    issuer  VARCHAR(32)
);

CREATE TABLE Person
(
    key     INTEGER,
    pid     PersonId
);

INSERT INTO Person VALUES (1, ('111','ABC'));
INSERT INTO Person VALUES (2, ('222','DEF'));

CREATE OR REPLACE FUNCTION Person_lookup_id
(
    p_key   IN Person.key%TYPE
)
RETURNS Person.pid%TYPE
LANGUAGE plpgsql
AS $BODY$
DECLARE
    v_pid   Person.pid%TYPE;
BEGIN
    SELECT pid INTO v_pid
    FROM Person
    WHERE key = p_key;

    RETURN v_pid;

EXCEPTION
    WHEN no_data_found THEN
        RETURN NULL;
END;
$BODY$;

However, the result was different from what I had expected.

Actually I expected the value 111 would be in id column, and ABC in issuer column. But 111 and ABC were combined in id column.

# select person_lookup_id(1);
 person_lookup_id 
------------------
 ("(111,ABC)",)
(1 row)

# select * from person_lookup_id(1);
    id     | issuer 
-----------+--------
 (111,ABC) | 
(1 row)

Where was I wrong?

Answer 1

Since pid is a composite you must extract its columns otherwise you are inserting the whole composite into the first column of the v_pid variable

select (pid).* into v_pid