[SQL] Returning data from multiple functions
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.html 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
Re: [SQL] Returning data from multiple functions
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.html 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
Re: [SQL] Returning data from multiple functions
2011/11/10 tlund79 : > 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.html > 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 > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] How to implement Aggregate Awareness?
I wonder how to implement Aggregate Awareness or Transparent Aggregate Navigation in PostgreSQL? Simply put, when Transparent (to the reporting tool) Aggregate Navigator recognizes that a query would execute faster using aggregates, it automatically rewrites the query so that the database hits the smaller aggregates rather than larger detail tables upon which the small aggregates are pre-computed. The simple theory for BI/DWH was set out by Ralph Kimball (http://www.kimballgroup.com/html/articles_search/articles1996/9608d54.html). Some tools, including Oracle, provide such functionality, and I wonder if it is possible to implement such Aggregate Awareness in PostgreSQL. Re-write rules in PostgreSQL come to mind first. After a few attempts, I realized that they may not be sophisticated enough to provide a viable solution. But perhaps they are, or there are other features in PostgreSQL that would allow for such aggregate awareness. If you have good ideas about how to implement this functionality, please respond. Thank you, Olgierd Michalak Soft Computer Consultants, Inc.
[SQL] ORACLE PROCEDURE TO POSTGRES FUNCTION -ERROR IN THE OVER PART
select d1.scheme_id,d1.value , d1.dr_cr_flg
INTO po_appl_scheme,po_scheme_val, po_dr_cr_flag
from ( select d.scheme_id,d.value, d.dr_cr_flg , d.rule_id ,
dense_rank() over (partition by d.rule_id order by
to_number(d.value) desc ) rk
from ( selectb.scheme_id,
b.rule_id,
to_number(decode(b.value_type,'%',to_number((
select
nvl((b.scheme_value*a.BASE_MILES)/100,0)
from point_mtrx_acrul a
where a.ORG = pi_org
anda.DEST = pi_dest
andpi_flt_dt between a.EFF_DT and
a.EXP_DT
anda.ARLN_NBR_CD = pi_arln_nbr_cd
)),b.SCHEME_VALUE))
as value,
b.dr_cr_flg
from rule_matrix b ,scheme_mstr c
where b.rule_id = po_appl_rule
and b.scheme_id = c.scheme_id
and pi_flt_dt between c.EFF_DT and c.EXP_DT
and b.value_type not in ('AWARD')
)d
)d1, scheme_mstr c
where d1.rk<=1
and c.scheme_id=d1.scheme_id
and pi_flt_dt between c.EFF_DT and c.EXP_DT
and rownum=1;
else
select d1.scheme_id,d1.value , d1.dr_cr_flg
INTO po_appl_scheme,po_scheme_val, po_dr_cr_flag
from ( select d.scheme_id,d.value, d.dr_cr_flg , d.rule_id ,
dense_rank() over (partition by d.rule_id order by
to_number(d.value) desc ) rk
from ( selectb.scheme_id,
b.rule_id,
to_number(decode(b.value_type,'%',to_number((
select
nvl((b.scheme_value*(decode(pi_bok_cls,'F',a.f_miles,'C',c_miles,y_miles)))/100,0)
from point_mtrx_redem a
where a.ORG = pi_org
anda.DEST = pi_dest
andpi_flt_dt between a.EFF_DT and
a.EXP_DT
anda.ARLN_NBR_CD = pi_arln_nbr_cd
)),b.SCHEME_VALUE))
as value,
b.dr_cr_flg
from rule_matrix b ,scheme_mstr c
where b.rule_id = po_appl_rule
and b.scheme_id = c.scheme_id
and pi_flt_dt between c.EFF_DT and c.EXP_DT
and b.value_type not in ('AWARD')
)d
)d1, scheme_mstr c
where d1.rk<=1
and c.scheme_id=d1.scheme_id
and pi_flt_dt between c.EFF_DT and c.EXP_DT
and rownum=1;
end if;
I am using this query in the procedure and i error is throwing as mentioned
below.and i want to convert the same oracle query into Postgresql.
ERROR-->
ERROR: syntax error at or near "OVER"
LINE 1: ...heme_id,d.value, d.dr_cr_flg , d.rule_id , RANK() OVER (part...
^
QUERY: SELECT d1.scheme_id,d1.value, d1.dr_cr_flg from ( select
d.scheme_id,d.value, d.dr_cr_flg , d.rule_id , RANK() OVER (partition by
d.rule_id order by to_number(d.value) desc) AS rk from ( select b.scheme_id,
b.rule_id, to_number( CASE b.value_type WHEN '%' THEN to_number((select
COALESCE((b.scheme_value * a.base_miles)/100,0) from point_mtrx_acrul
a,rule_matrix b where a.ORG = $1 and a.DEST = $2 and $3 between a.EFF_DT
and a.EXP_DT and a.ARLN_NBR_CD = $4 )) ELSE b.SCHEME_VALUE END ) as value1,
b.dr_cr_flg from rule_matrix b ,scheme_mstr c where b.rule_id = $5 and
b.scheme_id = c.scheme_id and $3 between c.EFF_DT and c.EXP_DT and
b.value_type not in ('AWARD') ) d ) d1, scheme_mstr c where d1.rk<=1 and
c.scheme_id=d1.scheme_id and $3 between c.EFF_DT and c.EXP_DT and rownum=1
CONTEXT: SQL statement in PL/PgSQL function "rule_engine" near line 563
** Error **
ERROR: syntax error at or near "OVER"
SQL state: 42601
Context: SQL statement in PL/PgSQL function "rule_engine" near line 563
[SQL] Partitionning + Trigger and Execute not working as expected
Hello, I'm trying to use table partitionning on a table called JOB. Each month a new table is created to contain the rows created on that month. ex : JOB_2011_11 for rows created during november 2011. To do that I followed this advices on that page : http://www.postgresql.org/docs/9.1/static/ddl-partitioning.html I also would like to create code dynamically into the trigger in order to have all "INSERT INTO" inheritated tables (tables like JOB__MM) queries done. But I can't make it work. I've an error when the insert is done using EXECUTE. *Working :* INSERT INTO job_2011_11 values (NEW.*); *Not Woking : *EXECUTE 'INSERT INTO '|| currentTableName || ' values (NEW.*)'; Could someone tell me how to make this EXECUTE work? Thank you Sylvain Bellow is the full code (trigger) and error. *Code:* CREATE OR REPLACE FUNCTION job_insert_trigger() RETURNS trigger AS $BODY$ DECLARE currentTableName character varying := 'job_' ||to_char(NEW.datecreation,'_MM'); BEGIN IF (NOT check_exist_table(currentTableName)) THEN PERFORM add_table_job__mm(currentTableName, NEW.datecreation); END IF; EXECUTE 'INSERT INTO '|| currentTableName || ' values (NEW.*)'; RETURN NULL; END; $BODY$ LANGUAGE plpgsql VOLATILE COST 100; *Error:* ERREUR: missing FROM clause for table « new » SQL :42P01
