By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,426 Members | 3,338 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,426 IT Pros & Developers. It's quick & easy.

returning columns from different tables, in plpgsql function

P: 1
Hello

Im 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. Ive 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 Im 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;
Dec 7 '07 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.