bdHpE1kJ

1CREATE OR REPLACE FUNCTION tr012q(
2    IN ex0arg integer,
3    IN ex2arg integer,
4    OUT q integer)
5  RETURNS integer AS
6$BODY$declare
7ctvar integer;
8begin
9-- If ex0arg and ex2arg are identical:
10if ex2arg = ex0arg
11then
12---- Identify their estimated expression quality.
13select coalesce (sum (uqmax), 0) into q from (
14select ui, max (uq) as uqmax from deriv.dnx
15where ex = ex0arg
16group by ui
17) as tbl;
18---- Return it and quit.
19return;
20end if;
21-- Arguments ex0arg and ex2arg differ. Create
22-- temporary table tr0temp of translations
23-- of ex0arg, their source groups, and their
24-- source groups’ qualities.
25create temporary table tr0temp on commit drop as
26select dnx.ex as ex1, ui, max (uq) as uqmax
27from dn, deriv.dnx
28where dn.ex = ex0arg
29and dnx.mn = dn.mn
30group by ex1, ui;
31-- Identify their count.
32get diagnostics ctvar = row_count;
33-- If there are none:
34if ctvar = 0
35then
36---- Identify the quality as 0.
37q := 0;
38-- Drop the temporary tables. Keep this section until tr012q () exists for use in state
39-- trkk1 of PanLem.
40drop table tr0temp;
41---- Return it and quit.
42return;
43end if;
44-- There are translations of ex0arg. Index
45-- tr0temp on ex1.
46create index on tr0temp (ex1);
47-- Index it on ui.
48create index on tr0temp (ui);
49-- Compile query-planning statistics on it.
50analyze tr0temp;
51-- Create temporary table tr1temp of
52-- translations of ex2arg.
53create temporary table tr1temp on commit drop as
54select dnx.ex as ex1, ui, max (uq) as uqmax
55from dn, deriv.dnx
56where dn.ex = ex2arg
57and dnx.mn = dn.mn
58group by ex1, ui;
59-- Identify their count.
60get diagnostics ctvar = row_count;
61-- If there are none:
62if ctvar = 0
63then
64---- Identify the quality as 0.
65q := 0;
66-- Drop the temporary tables. Keep this section until tr012q () exists for use in state
67-- trkk1 of PanLem.
68drop table tr0temp;
69drop table tr1temp;
70---- Return it and quit.
71return;
72end if;
73-- There are translations of ex2arg. Index
74-- tr1temp on ex1.
75create index on tr1temp (ex1);
76-- Index it on ui.
77create index on tr1temp (ui);
78-- Compile query-planning statistics on it.
79analyze tr1temp;
80-- Identify the estimated quality of the
81-- translation chain of length 1 between
82-- ex0arg and ex2arg.
83select coalesce (sum (uqmax), 0) into q from tr0temp
84where ex1 = ex2arg;
85-- Create temporary table tcdtemp of the disjoint
86-- translation chains of length 2 between ex0arg
87-- and ex2arg and their disjoint source groups.
88create temporary table tcdtemp on commit drop as
89select tr0temp.ex1, tr0temp.ui as ui0, tr1temp.ui as ui1
90from tr0temp, tr1temp
91where tr1temp.ex1 = tr0temp.ex1
92and not tr0temp.ui in (select ui from tr1temp)
93and not tr1temp.ui in (select ui from tr0temp);
94-- Identify the count of those chains.
95get diagnostics ctvar = row_count;
96-- If there are none:
97if ctvar = 0
98then
99-- Drop the temporary tables. Keep this section until tr012q () exists for use in state
100-- trkk1 of PanLem.
101drop table tr0temp;
102drop table tr1temp;
103drop table tcdtemp;
104---- Return the length-1 quality and quit.
105return;
106end if;
107-- Create temporary table s0temp of segment 0
108-- of each of those chains, its source groups,
109-- and their qualities.
110create temporary table s0temp on commit drop as
111select distinct tcdtemp.ex1, ui0, uqmax
112from tcdtemp, tr0temp
113where tr0temp.ex1 = tcdtemp.ex1
114and ui = ui0;
115-- Compile query-planning statistics on it.
116analyze s0temp;
117-- Create temporary table s1temp of segment 1
118-- of each of those chains, its source groups,
119-- and their qualities.
120create temporary table s1temp on commit drop as
121select distinct tcdtemp.ex1, ui1, uqmax
122from tcdtemp, tr1temp
123where tr1temp.ex1 = tcdtemp.ex1
124and ui = ui1;
125-- Compile query-planning statistics on it.
126analyze s1temp;
127-- Create temporary table rtemp of the
128-- redundancies of the disjoint source groups
129-- of the segments of those chains.
130create temporary table rtemp on commit drop as
131select ui0 as ui, count (ex1) as ex1ct from s0temp
132group by ui0
133union
134select ui1 as ui, count (ex1) as ex1ct from s1temp
135group by ui1;
136-- Compile query-planning statistics on it.
137analyze rtemp;
138-- Create temporary table v0temp of the values of
139-- the source groups of segment 0 of those chains.
140create temporary table v0temp on commit drop as
141select ex1, ui, uqmax::real / ex1ct as val
142from s0temp, rtemp
143where ui = ui0;
144-- Compile query-planning statistics on it.
145analyze v0temp;
146-- Create temporary table v1temp of the values of
147-- the source groups of segment 1 of those chains.
148create temporary table v1temp on commit drop as
149select ex1, ui, uqmax::real / ex1ct as val
150from s1temp, rtemp
151where ui = ui1;
152-- Compile query-planning statistics on it.
153analyze v1temp;
154-- Create temporary table altemp of the local
155-- ambiguities of the expressions ex1 in those
156-- translation chains.
157create temporary table altemp on commit drop as
158select ex, ap, uq, uq * count (mn) as al
159from (select distinct ex1 from tcdtemp) as tbl,
160deriv.dnx
161where ex = ex1
162group by ex, ap, uq;
163-- Compile query-planning statistics on it.
164analyze altemp;
165-- Create temporary table ambtemp of the global
166-- ambiguities of those expressions.
167create temporary table ambtemp on commit drop as
168select ex, cast (sum (al) as real) / sum (uq) as amb
169from altemp
170group by ex;
171-- Compile query-planning statistics on it.
172analyze ambtemp;
173-- Create temporary table vs0temp of the sums of
174-- the values of the disjoint source groups of
175-- segment 0 of the disjoint length-2 translation
176-- chains between ex0arg and ex1arg.
177create temporary table vs0temp on commit drop as
178select ex1, sum (val) as v0sum from v0temp
179group by ex1;
180-- Compile query-planning statistics on it.
181analyze vs0temp;
182-- Create temporary table vs1temp of the sums of
183-- the values of the disjoint source groups of
184-- segment 1 of the disjoint length-2 translation
185-- chains between ex0arg and ex1arg.
186create temporary table vs1temp on commit drop as
187select ex1, sum (val) as v1sum from v1temp
188group by ex1;
189-- Compile query-planning statistics on it.
190analyze vs1temp;
191-- Create temporary table tcvtemp of the products
192-- of those sums.
193create temporary table tcvtemp on commit drop as
194select vs0temp.ex1, v0sum * v1sum as val
195from vs0temp, vs1temp
196where vs1temp.ex1 = vs0temp.ex1;
197-- Compile query-planning statistics on it.
198analyze tcvtemp;
199-- Create temporary table tcqtemp of the square
200-- roots of the ratios of those products to the
201-- global ambiguities the ex1 of their chains.
202create temporary table tcqtemp on commit drop as
203select ex1, sqrt (val / amb) as tcq
204from tcvtemp, ambtemp
205where ex = ex1;
206-- Compile query-planning statistics on it.
207analyze tcqtemp;
208-- Identify the sum of the length-1 and length-2
209-- translation-chain qualities of ex0arg and ex2arg.
210select q + cast (sum (tcq) as integer) from tcqtemp
211into q;
212-- Drop the temporary tables. Keep this section until tr012q () exists for use in state
213-- trkk1 of PanLem.
214drop table tr0temp;
215drop table tr1temp;
216drop table tcdtemp;
217drop table s0temp;
218drop table s1temp;
219drop table rtemp;
220drop table v0temp;
221drop table v1temp;
222drop table altemp;
223drop table ambtemp;
224drop table vs0temp;
225drop table vs1temp;
226drop table tcvtemp;
227drop table tcqtemp;
228-- Return it and quit.
229return;
230end;$BODY$
231  LANGUAGE plpgsql VOLATILE
232  COST 100;