PostgreSQL: how to return composite type
composite, postgresql
Solution
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
Problem
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?