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?
