Thank you.
You are right, and you are right again and again.
As for the bias of my question, I will try to modify the thread to register in mySQL too... Thank you for your experience anyway. Very much the kind of stuff I am expecting.
Using 8.4, and completely new to it. This is why I forgot that "detail". Sorry.
Here is the function call:
********************
select * from eGap_Evolution(
25,
2,
'1',
'2',
4
)
********************
Here are the pg messages provides :
--------------------
NOTICE: la table « tmproute » n'existe pas, poursuite du traitement
CONTEXT: instruction SQL « drop table IF EXISTS tmpRoute »
PL/pgSQL function "egap_evolution" line 41 at instruction SQL
NOTICE: la table « tmpsection » n'existe pas, poursuite du traitement
CONTEXT: instruction SQL « drop table IF EXISTS tmpSection »
PL/pgSQL function "egap_evolution" line 42 at instruction SQL
NOTICE: la table « tmpsummary » n'existe pas, poursuite du traitement
CONTEXT: instruction SQL « drop table IF EXISTS tmpSummary »
PL/pgSQL function "egap_evolution" line 43 at instruction SQL
NOTICE: la table « tmpairline » n'existe pas, poursuite du traitement
CONTEXT: instruction SQL « drop table IF EXISTS tmpAirline »
PL/pgSQL function "egap_evolution" line 44 at instruction SQL
NOTICE: la table « tmpquestion » n'existe pas, poursuite du traitement
CONTEXT: instruction SQL « drop table IF EXISTS tmpQuestion »
PL/pgSQL function "egap_evolution" line 45 at instruction SQL
NOTICE: la table « tmpsummarycomp » n'existe pas, poursuite du traitement
CONTEXT: instruction SQL « drop table IF EXISTS tmpSummaryComp »
PL/pgSQL function "egap_evolution" line 46 at instruction SQL
NOTICE: la table « tmpstats » n'existe pas, poursuite du traitement
CONTEXT: instruction SQL « drop table IF EXISTS tmpStats »
PL/pgSQL function "egap_evolution" line 47 at instruction SQL
NOTICE: la table « tmpvals » n'existe pas, poursuite du traitement
CONTEXT: instruction SQL « drop table IF EXISTS tmpVals »
PL/pgSQL function "egap_evolution" line 48 at instruction SQL
ERREUR: la requête n'a pas de destination pour les données résultantes
HINT: Si vous voulez annuler les résultats d'un SELECT, utilisez PERFORM à la place.
CONTEXT: fonction PL/pgsql « egap_evolution », ligne 307 à instruction SQL
********** Error **********
ERREUR: la requête n'a pas de destination pour les données résultantes
SQL state: 42601
Hint: Si vous voulez annuler les résultats d'un SELECT, utilisez PERFORM à la place.
Context: fonction PL/pgsql « egap_evolution », ligne 307 à instruction SQL
--------------------------
And here is the function. Don't hit me: lots of stuff is just a trial and error to have pg at least building the function. If you are kind enough to go through it...
**********************
CREATE or replace function eGap_Evolution(
int,
int,
character varying(100),
character varying(100),
int
) returns table(
MeanHit character varying(10),
T2BHit character varying(10),
XLHit character varying(10),
B2BHit character varying(10),
ALID int,
quarter int,
pos int,
Mean double precision,
T2Bw double precision,
T2B double precision,
B2Bw double precision,
B2B double precision,
XLw double precision,
XL double precision,
VGw double precision,
VG double precision,
Gw double precision,
G double precision,
Pw double precision,
P double precision,
VPw double precision,
VP double precision,
Base int,
WeightedBase double precision,
EffectiveBase double precision,
StdDeviation double precision,
compMean double precision,
compT2Bw double precision,
compT2B double precision,
compB2Bw double precision,
compB2B double precision,
compXLw double precision,
compXL double precision,
compWeightedBase double precision,
compEffectiveBase double precision,
compStdDeviation double precision
)
as $$
/*------------------------
/*Alias "input variable names*/
/*------------------------
declare ALID alias for $1;
declare QID alias for $2;
declare inRoutes alias for $3;
declare inSections alias for $4;
declare "Shift" alias for $5;
/*------------------------
/*map multiparameters to workable parameters */
/*(pg does not allow modifying their content in the function)*/
/*------------------------
declare Routes character varying(100);
declare Sections character varying(100);
/*------------------------
/*other variables*/
/*------------------------
declare Param character varying(5);
declare MinMean double precision;
declare MinT2B double precision;
declare MinXL double precision;
declare MinB2B double precision;
declare MaxMean double precision;
declare MaxT2B double precision;
declare MaxXL double precision;
declare MaxB2B double precision;
begin
/*------------------------
/*take care of multi parameters*/
/*------------------------
Routes = inRoutes;
Sections = inSections;
drop table IF EXISTS tmpRoute;
drop table IF EXISTS tmpSection;
drop table IF EXISTS tmpSummary;
drop table IF EXISTS tmpAirline;
drop table IF EXISTS tmpQuestion;
drop table IF EXISTS tmpSummaryComp;
drop table IF EXISTS tmpStats;
drop table IF EXISTS tmpVals;
/*------------------------
/*store parameters in temporary tables for efficency*/
/*as "joins are much faster than where clauses*/
/*------------------------
create temporary table tmpRoute(myParam int);
create temporary table tmpSection(myParam int);
create temporary table tmpAirline(myParam int);
create temporary table tmpQuestion(myParam int);
if ALID = 0 then
insert into tmpAirline(myParam) (select id from vw_airlines);
else
insert into tmpAirline(myParam) values(ALID);
end if;
insert into tmpQuestion(myParam) values(QID);
Param = substring(Routes from 1 for 1);
while Param <> '' loop
insert into tmpRoute(myParam) values(cast(Param as int));
if length(Routes) > 1 then Routes = substring(Routes from 3);
else Routes = ''; end if;
Param = substring(Routes from 1 for 1);
end loop;
Param = substring(Sections, 1);
while Param <> '' loop
insert into tmpSection(myParam) values(cast(Param as int));
if length(Sections) > 1 then Sections = substring(Sections, 3);
else Sections = ''; end if;
Param = substring(Sections, 1);
end loop;
/*------------------------
/*extract and store means by airlines*/
/*------------------------
CREATE TEMPORARY TABLE "tmpVals"
as
select
"tmpAirline"."myParam" as "ALID",
"interview"."quarter",
sum(answer.value) / sum(interview.weight) as "Mean",
sum(case when answer.value in (4,5) then interview.weight else 0 end) as "T2Bw",
sum(case when answer.value in (4,5) then interview.weight else 0 end) / sum(interview.weight) as "T2B",
sum(case when answer.value in (1,2) then interview.weight else 0 end) as "B2Bw",
sum(case when answer.value in (1,2) then interview.weight else 0 end) / sum(interview.weight) as "B2B",
sum(case when answer.value = 5 then interview.weight else 0 end)
as "XLw",
sum(case when answer.value = 5 then interview.weight else 0 end) /
sum(interview.weight)
as "XL",
sum(case when answer.value = 4 then interview.weight else 0 end)
as "VGw",
sum(case when answer.value = 4 then interview.weight else 0 end) /
sum(interview.weight)
as "VG",
sum(case when answer.value = 3 then interview.weight else 0 end)
as "Gw",
sum(case when answer.value = 3 then interview.weight else 0 end) /
sum(interview.weight)
as "G",
sum(case when answer.value = 2 then interview.weight else 0 end)
as "Pw",
sum(case when answer.value = 2 then interview.weight else 0 end) /
sum(interview.weight)
as "P",
sum(case when answer.value = 1 then interview.weight else 0 end)
as "VPw",
sum(case when answer.value = 1 then interview.weight else 0 end) /
sum(interview.weight)
as "VP",
count(interview.id) as "Base",
sum(interview.weight) as "WeightedBase",
POW(sum(interview.weight),
SQRT((sum(interview.weight
- POW(sum(interview.weight * answer.value), 2) /
sum(interview.weight)) / (sum(interview.weight) - 1))
as "StdDeviation"
from interview join answer
on answer.interview_id = interview.id
join "tmpSection"
on interview.section_bis_id = "tmpSection"."myParam"
join "tmpRoute"
on interview.route_id = "tmpRoute"."myParam"
join "tmpAirline"
on interview.airline_id = "tmpAirline"."myParam"
join "tmpQuestion"
on answer.question_id = "tmpQuestion"."myParam"
group by "tmpAirline"."myParam", interview.quarter
;
/*------------------------
/*store specific values*/
/*------------------------
Select min("Mean") into MinMean from "tmpVals";
Select min("T2B") into MinT2B from "tmpVals";
select min("XL") into MinXL from "tmpVals";
select min("B2B") into MinB2B from "tmpVals";
select max("Mean") into MaxMean from "tmpVals";
select max("T2B") into MaxT2B from "tmpVals";
select max("XL") into MaxXL from "tmpVals";
select max("B2B") into MaxB2B from "tmpVals";
/*------------------------
/*Summarize*/
/*------------------------
CREATE TEMPORARY TABLE "tmpSummary"
as
select
case when "Mean" = MinMean then 'Min' else case when "Mean" = MaxMean then 'Max' else '' end end as "MeanHit",
case when "T2B" = MinT2B then 'Min' else case when "T2B" = MaxT2B then 'Max' else '' end end as "T2BHit",
case when "XL" = MinXL then 'Min' else case when "XL" = MaxXL then 'Max' else '' end end as "XLHit",
case when "B2B" = MinB2B then 'Min' else case when "B2B" = MaxB2B then 'Max' else '' end end as "B2BHit",
rank() over(order by quarter) AS rnk,
"ALID",
v."quarter",
"Mean",
"T2Bw",
"T2B",
"B2Bw",
"B2B",
"XLw",
"XL",
"VGw",
"VG",
"Gw",
"G",
"Pw",
"P",
"VPw",
"VP",
"Base",
"WeightedBase",
"EffectiveBase",
"StdDeviation"
from "tmpVals" v
;
/*------------------------
/*Duplicate temporary table for join with comparable quarter*/
/*------------------------
create temporary table "tmpSummaryComp"
as
select * from "tmpSummary";
/*------------------------
/*Prepare significance tests*/
/*------------------------
create temporary table "tmpStats"
as
select
s."MeanHit",
s."T2BHit",
s."XLHit",
s."B2BHit",
s."ALID",
s."quarter",
s.rnk,
s."Mean",
s."T2Bw",
s."T2B",
s."B2Bw",
s."B2B",
s."XLw",
s."XL",
s."VGw",
s."VG",
s."Gw",
s."G",
s."Pw",
s."P",
s."VPw",
s."VP",
s."Base",
s."WeightedBase",
s."EffectiveBase",
s."StdDeviation",
c."Mean" as "compMean",
c."T2Bw" as "compT2Bw",
c."T2B" as "compT2B",
c."B2Bw" as "compB2Bw",
c."B2B" as "compB2B",
c."XLw" as "compXLw",
c."XL" as "compXL",
c."WeightedBase" as "compWeightedBase",
c."EffectiveBase" as "compEffectiveBase",
c."StdDeviation" as "compStdDeviation"
from "tmpSummary" s left join "tmpSummaryComp" c
on c.rnk = (s.rnk - "Shift")
;
/*------------------------
/*Return result set*/
/*------------------------
select
"MeanHit",
"T2BHit",
"XLHit",
"B2BHit",
"rnk",
"ALID",
"quarter",
"Mean",
"T2Bw",
"T2B",
"B2Bw",
"B2B",
"XLw",
"XL",
"VGw",
"VG",
"Gw",
"G",
"Pw",
"P",
"VPw",
"VP",
"Base",
"WeightedBase",
"EffectiveBase",
"StdDeviation",
"EffectiveBase" + "compEffectiveBase" -1 as "DOF",
("Mean" - "compMean") /
sqrt(
(
(("EffectiveBase" - 1) * POW("StdDeviation", 2) + ("compEffectiveBase" - 1) * POW("compStdDeviation", 2)) /
("EffectiveBase" + "compEffectiveBase" - 2)
) *
(1 / "EffectiveBase" + 1 / "compEffectiveBase")
)
as "tMean",
("T2B" - "compT2B" + abs((1 / "compEffectiveBase") + (1 / "EffectiveBase")) / 2) /
sqrt(
(((1 / "compEffectiveBase") + (1 / "EffectiveBase")) * (("compT2Bw" + "T2Bw") / ("compWeightedBase" + "WeightedBase")) * (1 - ("compT2Bw" + "T2Bw") / ("compWeightedBase" + "WeightedBase"))) /
(1 - 1 / ("EffectiveBase" + "compEffectiveBase"))
)
as "tT2B",
("XL" - "compXL" + abs((1 / "compEffectiveBase") + (1 / "EffectiveBase")) / 2) /
sqrt(
(((1 / "compEffectiveBase") + (1 / "EffectiveBase")) * (("compXLw" + "XLw") / ("compWeightedBase" + "WeightedBase")) * (1 - ("compXLw" + "XLw") / ("compWeightedBase" + "WeightedBase"))) /
(1 - 1 / ("EffectiveBase" + "compEffectiveBase"))
)
as "tXL",
("B2B" - "compB2B" + abs((1 / "compEffectiveBase") + (1 / "EffectiveBase")) / 2) /
sqrt(
(((1 / "compEffectiveBase") + (1 / "EffectiveBase")) * (("compB2Bw" + "B2Bw") / ("compWeightedBase" + "WeightedBase")) * (1 - ("compB2Bw" + "B2Bw") / ("compWeightedBase" + "WeightedBase"))) /
(1 - 1 / ("EffectiveBase" + "compEffectiveBase"))
)
as "tB2B"
from "tmpStats" s;
/*------------------------
/*Clean temporary tables*/
/*------------------------
drop table IF EXISTS "tmpRoute";
drop table IF EXISTS "tmpSection";
drop table IF EXISTS "tmpAirline";
drop table IF EXISTS "tmpQuestion";
drop table IF EXISTS "tmpSummary";
drop table IF EXISTS "tmpSummaryComp";
drop table IF EXISTS "tmpStats";
drop table IF EXISTS "tmpVals";
end $$ LANGUAGE plpgsql;
**********************
Have a nice week-end anyway.
Vincent
Main Topics
Browse All Topics
by: cminearPosted on 2009-10-16 at 07:57:52ID: 25590161
It would be helpful if you had included your code that you're having trouble with, and an example of how you are calling it.
Also, what version of PostgreSQL are you using? According to the documentation, 8.4 supports the return of a table from a function (http://www.postgresql.org
There's a little danger of bias in the question about opinion of PostgreSQL over Mysql, if you only ask the 'PostgreSQL Database' zone. Still, I'll give my response. When I started developing the database that I still administer, it originally used Mysql. This was because I was adapting the application code from another group, and they were already using Mysql. I had initially put migrating to PostgreSQL on the backburner because there were enough hurdles that I couldn't just do a drop-in replacement. (Primarily the lack of a boolean type in Mysql was the hiccup.) Well, at some point I started having trouble with how the application was behaving. It turned out that the field for storing hashed passwords was supposed to be 40 characters long; however, in my database it was only _4_ characters. Now I can't say if the configuration error was my mistake or something I inherited from the other group, but the fact that Mysql was silently dropping 36 characters since the first day I started working with it scared me; I was working on a database application, where I wanted some trust in the data being stored. Finding that out, I changed over to PostgreSQL as fast as I could.