PostgreSQL: how to return a composite type

Question

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

And I inserted the 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 expected.

In fact, I expected 111 to be in the id column, and ABC in the issuer column. But 111 and ABC were combined in the 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 compound, you must extract its columns, otherwise you insert the whole composite into the first column of the v_pid variable

 select (pid).* into v_pid