From: tlund79 <thomas.lund@eniro.no>
To: pgsql-sql@postgresql.org
Subject: Returning data from multiple functions
Date: Thu, 10 Nov 2011 03:14:10 -0800 (PST)
Message-ID: <1320923650898-4980747.post@n5.nabble.com> (raw)
A short brief about the goal:
I have a Excel workbook loaded with static data and need to compare these to
"live-data" from DB. There are 20 rows with data in the report that needs to
be retrieved from the database, and almost the same amount of queries, since
all of these queries relies on variable input I've created on function per
cell. This solution works both in terms of maintenance and less "query work
in Excel". However, all of these functions do have the same variable input,
thus I was hoping to create one function that runs all of the functions and
input the variables. This enables me to make the excel workbook maintenance
free and all of the logic is put in one place.
This is were I'm stuck, creating and selecting one and one function works
fine, but creating one function to run and retrieve data from multiple
functions seems like a challenge. What I basically want is a 'union all',
but when I try to run a union all on the functions it only retrieves data
from the last function.
This is what I got now (i've inputed the variables manually in each function
for testing purposes):
CREATE OR REPLACE FUNCTION ppr_test_to_funk()
RETURNS SETOF RECORD AS
$BODY$
DECLARE
ppr RECORD;
BEGIN
select ppr_pf_inn_verdi(2011,1,52,array[3], array[7,4])
union all
select ppr_pf_inn_antall(2011,1,52,array[3], array[7,4]);
RETURN NEXT ppr;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100
ROWS 1000;
ALTER FUNCTION ppr_test_to_funk()
OWNER TO oystein;
When I select this one I get the usual "Query has no destination for result
data". If my suspicions are correct I'm struggelig with the "RETURNS SETOF"
combined with the destination.
And yes; I'm totally new to all of this.
Thanks in advance
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Returning-data-from-multiple-functions-tp4980747p4980747.htm...
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: thomas.lund@eniro.no
Subject: Re: Returning data from multiple functions
In-Reply-To: <1320923650898-4980747.post@n5.nabble.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox