pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedReturning data from multiple functions
3+ messages / 2 participants
[nested] [flat]
* Returning data from multiple functions
@ 2011-11-10 11:14 tlund79 <thomas.lund@eniro.no>
2011-11-10 11:37 ` Re: Returning data from multiple functions tlund79 <thomas.lund@eniro.no>
0 siblings, 1 reply; 3+ messages in thread
From: tlund79 @ 2011-11-10 11:14 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Returning data from multiple functions
2011-11-10 11:14 Returning data from multiple functions tlund79 <thomas.lund@eniro.no>
@ 2011-11-10 11:37 ` tlund79 <thomas.lund@eniro.no>
2011-11-10 12:53 ` Re: Returning data from multiple functions Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: tlund79 @ 2011-11-10 11:37 UTC (permalink / raw)
To: pgsql-sql
I know got this far thanks to Pavle Stehule. The function worked and returned
the data when the variables was predefined after "return query".
When tried to replace these with variables passed through the function call
I got this message;
ERROR: syntax error at or near "RETURN"
LINE 1: ...ll select ppr_pf_inn_antall( $1 , $2 , $3 , $4 , $5 ) RETURN
^
QUERY: select ppr_pf_inn_verdi( $1 , $2 , $3 , $4 , $5 ) union all select
ppr_pf_inn_antall( $1 , $2 , $3 , $4 , $5 ) RETURN
CONTEXT: SQL statement in PL/PgSQL function "ppr_test_to_funk" near line 7
CREATE OR REPLACE FUNCTION ppr_test_to_funk(aarstall int, frauke int, tiluke
int, prosjektkode int[], teamkode int[])
RETURNS setof integer AS
$BODY$
I called the function with this: select * from
ppr_test_to_funk(2011,1,52,array[3], array[7,4])
Am I lost or are this possible?
*Updated function:*
BEGIN
return query
select ppr_pf_inn_verdi($1,$2,$3,$4,$5)
union all
select ppr_pf_inn_antall($1,$2,$3,$4,$5)
RETURN;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION ppr_test_to_funk()
OWNER TO oystein;
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Returning-data-from-multiple-functions-tp4980747p4980786.htm...
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Returning data from multiple functions
2011-11-10 11:14 Returning data from multiple functions tlund79 <thomas.lund@eniro.no>
2011-11-10 11:37 ` Re: Returning data from multiple functions tlund79 <thomas.lund@eniro.no>
@ 2011-11-10 12:53 ` Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Pavel Stehule @ 2011-11-10 12:53 UTC (permalink / raw)
To: tlund79 <thomas.lund@eniro.no>; +Cc: pgsql-sql
2011/11/10 tlund79 <thomas.lund@eniro.no>:
> I know got this far thanks to Pavle Stehule. The function worked and returned
> the data when the variables was predefined after "return query".
>
> When tried to replace these with variables passed through the function call
> I got this message;
> ERROR: syntax error at or near "RETURN"
> LINE 1: ...ll select ppr_pf_inn_antall( $1 , $2 , $3 , $4 , $5 ) RETURN
> ^
> QUERY: select ppr_pf_inn_verdi( $1 , $2 , $3 , $4 , $5 ) union all select
> ppr_pf_inn_antall( $1 , $2 , $3 , $4 , $5 ) RETURN
> CONTEXT: SQL statement in PL/PgSQL function "ppr_test_to_funk" near line 7
>
>
>
> CREATE OR REPLACE FUNCTION ppr_test_to_funk(aarstall int, frauke int, tiluke
> int, prosjektkode int[], teamkode int[])
> RETURNS setof integer AS
> $BODY$
>
> I called the function with this: select * from
> ppr_test_to_funk(2011,1,52,array[3], array[7,4])
>
> Am I lost or are this possible?
>
> *Updated function:*
>
> BEGIN
> return query
> select ppr_pf_inn_verdi($1,$2,$3,$4,$5)
> union all
> select ppr_pf_inn_antall($1,$2,$3,$4,$5)
>>> MISSING SEMICOLON HERE!!!
>
> RETURN;
> END;
> $BODY$
> LANGUAGE plpgsql VOLATILE
> COST 100;
> ALTER FUNCTION ppr_test_to_funk()
> OWNER TO oystein;
>
> --
> View this message in context: http://postgresql.1045698.n5.nabble.com/Returning-data-from-multiple-functions-tp4980747p4980786.htm...
> Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2011-11-10 12:53 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-11-10 11:14 Returning data from multiple functions tlund79 <thomas.lund@eniro.no>
2011-11-10 11:37 ` tlund79 <thomas.lund@eniro.no>
2011-11-10 12:53 ` Pavel Stehule <pavel.stehule@gmail.com>
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