473,883 Members | 2,116 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

returning columns from different tables, in plpgsql function

1 New Member
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_t emplateinfo AS
(templatename varchar,
templateid int4,
physicianid int4,
physicianname varchar,
infectioncontro lid int4,
infectioncontro l varchar,
ventmanufacture rid int4,
manufacturename varchar,
ventmodeid int4,
ventmode varchar,
ageid int4,
age varchar,
acuitycategoryi d int4,
acuitycategoryn ame varchar,
templatestatus int4,
patientid int4);

CREATE OR REPLACE FUNCTION ccs_get_templat es()
RETURNS SETOF templateadmin_t emplateinfo AS
$BODY$
-- Gets template list for the template type

DECLARE
rec templateadmin_t emplateinfo;
BEGIN

for rec in
select
t.vc_name,
t.i_wt_template _id,
t.i_physician_i d,
COALESCE(p.vc_f name,'')||' '|| COALESCE(p.vc_m name,'')||' '|| COALESCE(p.vc_l name,''),
t.i_infectionco ntrol_id,
ic.vc_name,
t.i_vent_id,
vm.vc_manufactu rer,
t.i_ventmode_id ,
v.vc_name,
t.i_agerange_id ,
mg.vc_value,
t.i_acuitycateg ory_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_i d = p.i_physician_i d
left join m_infection_con trol ic on t.i_infectionco ntrol_id = ic.i_infectionc ontrol_id
left join m_vent_manufact urer 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_acuitycateg ory_id

LOOP
-- fetch each record
return next rec;
END LOOP;

RETURN;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
Dec 7 '07 #1
0 3607

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

Similar topics

1
8219
by: Julie May | last post by:
I have 90% of my function working and I know to get the next 10% it is justa matter of getting the quotations and the escaping quotations correct. Here is the portion that does work: <working code> -- Function: public.get_factory_ytd() CREATE or replace FUNCTION public.get_factory_ytd() RETURNS setof record AS' declare ytd_record record; d_date record; begin for d_date in select distinct delivery_date from load_info_table order...
22
3734
by: Christopher Murtagh | last post by:
Greetings, I'm trying to write a pl/perl function that will return multiple rows. I've looked all over the web and only found vague references as to how to do this (some said it was possible, and some said it wasn't but it was for older versions of Postgres). Basically I would *love* to be able to do something like this: SELECT some_id
9
19513
by: Ron St-Pierre | last post by:
On a daily basis I place a lot of data into the empty table dailyList, and from that data update certain fields in currentList. I thought that using a function would be a good way to do this(?). However I get the following error when I run updateCurrentData(): ERROR: set-valued function called in context that cannot accept a set CONTEXT: PL/pgSQL function "updatecurrentcata" line 6 at return next I've googled and tried variations on the...
5
2709
by: Raphael Bauduin | last post by:
Hi, I'm looking at the logging of a database we'll put in production soon. I've seen some posts on this list about history tables, like mentioned in http://www-106.ibm.com/developerworks/web/library/wa-dbdsgn2.html . I think I'll go that way too, but I still have some questions on the approach, and would appreciate any advice on it. Here are some questions I have: - is it possible to write only one function used for all logging...
9
10942
by: Karl O. Pinc | last post by:
I want to return multiple values, but not a set, only a single row, from a plpgsql function and I can't seem to get it to work. (I suppose I'd be happy to return a set, but I can't seem to make that work either. Anyway, what's wrong with this?) Version is: $ rpm -q postgresql
5
14402
by: Robert Fitzpatrick | last post by:
Can someone point me to some more information or perhaps show an example of returning a recordset from a plpgsql function. I'd like to send an argument or arguments to the function, do some queries to return a set of records. I've done several functions that return one value of one type, but nothing that returns a set. -- Robert
1
2604
by: Ruediger Herrmann | last post by:
Hello again, as I am new to PostgreSQL (great pice of softwork btw) this probably won't be the last question. I access PostgreSQL from Java via the PostgreSQL JDBC driver. I am currently building a little framework that provides basic CRUD operations by mapping class properties to database columns. All my primary keys are artificial and built by sequences (datat type bigserial). Now I need to (re-)read the row that was...
6
18386
by: twinklyblue | last post by:
Hi thescripts team, I would like to ask for your help once again regarding returning set of records in plpgsql. I followed examples presented in other sites regarding this function but my script doesnt work. here's my code create or replace function find_postal(varchar, varchar, varchar) returns setof record as ' declare ret_row record;
0
2980
by: r0cboff | last post by:
Hi there, This is my first time posting in here and I'm hoping somebody can point out where I am going wrong? I am currently trying to use Bitemporal tables. By this I mean a table with a valid times and transaction times. These topics are covered by Joe Celko and Richard Snodgrass in their respective books. I have developed a simple schema to test the relevant constraints which are required to keep all the valid times and transaction...
0
9945
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9798
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10766
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10863
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9588
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7978
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7136
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
6007
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4230
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.