table-valued function in PostreSQL only returning one column

Question

I have the following schema in PostreSQL:

patients (patient_id, name, gender, dob)

clinics (clinic_id, name)

doctors (doctor_id, clinic_id, name, specialty)

examinations (patient_id, doctor_id, exam_date, exam_cost)

I want to create a table function that takes in a patient's ID as input and returns their examination history including the patient's name, the doctor's name, the exam date, the exam cost, and the name of the clinic. I created the following table function:

CREATE OR REPLACE FUNCTION exam_history (patient_id char(8))
    RETURNS TABLE (
        patient_name varchar(250),
        doctor_name varchar(250),
        exam_date timestamp,
        exam_cost numeric,
        clinic_name varchar(250)) AS
    $$
        BEGIN
            ALTER TABLE patients
                RENAME COLUMN name TO patient_name;
            
            ALTER TABLE doctors
                RENAME COLUMN name TO doctor_name;
            
            ALTER TABLE clinics
                RENAME COLUMN name TO clinic_name;
                
            RETURN QUERY    
            SELECT p.patient_name, d.doctor_name, e.exam_date, e.exam_cost, c.clinic_name
            FROM examinations e
                JOIN patients p on p.patient_id = e.patient_id
                JOIN doctors d on d.doctor_id = e.doctor_id
                JOIN clinics c on c.clinic_id = d.clinic_id
            WHERE p.patient_id = exam_history.patient_id; 
        END
    $$
LANGUAGE plpgsql

This is my first time creating a table function so I'm not sure if it is entirely correct. When I use the function on a random patient's ID, I only get a one column outlook shown below:

How do I get the correct table output with 5 columns?

Answer 1

To decompose the returned row type, call the function with

SELECT * FROM exam_history ('12345678');

Related:

• Simple PostgreSQL function to return rows