returning columns from different tables, in plpgsql function

["Amit Phatarphekar" <amit@xxxxxxxxxxxxxxxxx>]

Hello –

 

I’m trying to findout a better solution to this approach. 

 

Currently if I have to return columns from multiple tables, I have to define my own TYPE and then return SETOF that type in the function.  I’ve provided an example below. 

 

Now, if I have to add a column to the select query, I have drop the existing TYPE definition, create a new TYPE with the new column added to it, and then modify the function sql to return this extra column.

Maintenance wise, this is a lot of work to manage. 

 

Moreover since the function returns multiple records, I have loop through the results also.

 

Question – Is there any other way to doing this?  - Is everybody following the same approach out there? I know I can return a SETOF RECORD type and then define in the function call, all the columns that are being returned.  But I like TYPE the definition approach better than this anyways.  Let me know if I’m missing anything in the mix.

 

 

CREATE TYPE templateadmin_templateinfo AS

   (templatename varchar,

    templateid int4,

    physicianid int4,

    physicianname varchar,

    infectioncontrolid int4,

    infectioncontrol varchar,

    ventmanufacturerid int4,

    manufacturename varchar,

    ventmodeid int4,

    ventmode varchar,

    ageid int4,

    age varchar,

    acuitycategoryid int4,

    acuitycategoryname varchar,

    templatestatus int4,

    patientid int4);

 

CREATE OR REPLACE FUNCTION ccs_get_templates()

  RETURNS SETOF templateadmin_templateinfo AS

$BODY$

-- Gets template list for the template type

 

DECLARE

            rec templateadmin_templateinfo;

BEGIN

 

  for rec in

      select

                  t.vc_name,

                  t.i_wt_template_id,

                  t.i_physician_id,

                  COALESCE(p.vc_fname,'')||' '|| COALESCE(p.vc_mname,'')||' '|| COALESCE(p.vc_lname,''),   

                  t.i_infectioncontrol_id,

                  ic.vc_name,

                  t.i_vent_id,

                  vm.vc_manufacturer,

                  t.i_ventmode_id,

                  v.vc_name,

                  t.i_agerange_id,

                  mg.vc_value,

                  t.i_acuitycategory_id,

                  ma.vc_name,

                  t.i_status,

                  t.i_patienthist_id

      from m_weaning_trial_template t

            left join m_physician p on t.i_physician_id = p.i_physician_id

            left join m_infection_control ic on t.i_infectioncontrol_id = ic.i_infectioncontrol_id

            left join m_vent_manufacturer vm on vm.i_vent_id = t.i_vent_id

            left join m_ventmode v on v.i_ventmode_id = t.i_ventmode_id

            left join m_name_value mg on mg.i_nameval_id = t.i_agerange_id

            left join m_name_value ma on ma.i_nameval_id = t.i_acuitycategory_id

 

    LOOP

      -- fetch each record

      return next rec;

    END LOOP;

 

RETURN;

END;

$BODY$

  LANGUAGE 'plpgsql' VOLATILE;