Creating a table function in PostgreSQL

Question

I'm creating a table function in pgAdmin that returns a table with multiple variables from multiple tables. For simplicity, I will only show the function with one variable since that will answer my question just as well.

I have a patients relation with the following attribute: patient_id, address_id, name, gender, dob. For simplicity purposes, say I want to create a table function that will take in a patient's ID and return a table output with their name.

CREATE OR REPLACE FUNCTION patientname (patient_id char(8))
    RETURNS TABLE (
        patient_name varchar(250)) AS
    $$
        BEGIN
            RETURN QUERY
            SELECT patient_name
            FROM patients
            WHERE patient_id = patientname.patient_id;
        END
    $$

In the examples that I have seen, the variables being defined in the RETURNS TABLE() section have the same names as the variables in the tables that the information is being pulled from in the RETURN QUERY section. How would I create this function with variable names in the RETURNS TABLE section that are different from the variable names in the original tables that I will be pulling data from. Like in the example above the table output from the function should return a variable called patient_name but this variable in the patients table is only called name.

Answer 1

You can choose any result column name you want, and it doesn't have to be the same as the alias of the corresponding SELECT list entry of the query. The first column of the query result set will become the first column of the function result set, and so on.