fFaZZ2j6

1CREATE OR REPLACE FUNCTION generate_fuzzy_table(varchar, TEXT[]) RETURNS varchar AS $$
2DECLARE
3	assoc 			ALIAS FOR $1;
4	preds 			ALIAS FOR $2;
5
6	qry_sql			TEXT DEFAULT '';
7	qry_fuzz		TEXT DEFAULT NULL;
8	pred			TEXT DEFAULT NULL;
9	rec_at			record;
10
11	fuzz_tbl		TEXT DEFAULT NULL;
12BEGIN
13	
14	FOREACH pred IN ARRAY preds LOOP
15	
16		PERFORM predname FROM pg_fuzzypred WHERE predname = pred;
17		IF NOT FOUND THEN
18	    	RAISE EXCEPTION 'fuzzy predicate % not found', pred;
19		END IF;
20	
21	END LOOP;
22
23	qry_sql := 'SELECT attname,attnum FROM pg_class, pg_attribute';
24	qry_sql := qry_sql || ' WHERE pg_class.relname = '''||assoc||''' ';
25	qry_sql := qry_sql || ' AND pg_attribute.attrelid = pg_class.oid';
26	qry_sql := qry_sql || ' AND attnum > 0 AND attnum < relnatts ORDER BY attnum';
27
28	FOR rec_at IN EXECUTE qry_sql LOOP
29
30		IF rec_at.attnum = 1 THEN
31			qry_fuzz := 'CREATE TABLE ' || assoc || '_fuzz(id serial)';
32			-- RAISE NOTICE 'qry_fuzz_main: %', qry_fuzz;
33			EXECUTE qry_fuzz;
34		END IF;
35
36		FOREACH pred IN ARRAY preds LOOP
37			
38			fuzz_tbl := assoc || '_fuzz_' || rec_at.attname;
39
40			qry_fuzz := 'CREATE TABLE '|| fuzz_tbl ||' AS SELECT id, '|| rec_at.attname ||'
41						FROM '|| assoc ||' t1
42						WHERE t1.'|| rec_at.attname ||' = '|| pred ||'
43							OR EXISTS (SELECT 1 
44										FROM '|| assoc ||' t2
45										WHERE t1.'|| rec_at.attname ||' = t2.'|| rec_at.attname ||')';
46			-- RAISE NOTICE '1 qry_fuzz_%: %', rec_at.attnum, qry_fuzz;
47			EXECUTE qry_fuzz;
48			
49			qry_fuzz := 'ALTER TABLE '|| assoc ||'_fuzz
50							ADD COLUMN '|| rec_at.attname ||'_'|| pred ||' float4';
51			-- RAISE NOTICE '2 qry_fuzz_%: %', rec_at.attnum, qry_fuzz;
52			EXECUTE qry_fuzz;
53
54			IF rec_at.attnum > 1 THEN
55				qry_fuzz := 'UPDATE '|| assoc ||'_fuzz SET '|| rec_at.attname ||'_'|| pred ||' = grmemb
56								FROM '|| fuzz_tbl ||'
57								WHERE '|| assoc ||'_fuzz.id = '|| fuzz_tbl ||'.id';
58				-- RAISE NOTICE '3 qry_fuzz_%: %', rec_at.attnum, qry_fuzz;
59				EXECUTE qry_fuzz;
60			ELSE
61				qry_fuzz := 'INSERT INTO '|| assoc ||'_fuzz ('|| rec_at.attname ||'_'|| pred ||')
62								SELECT grmemb FROM ' || fuzz_tbl;
63				-- RAISE NOTICE '4 qry_fuzz_%: %', rec_at.attnum, qry_fuzz;
64				EXECUTE qry_fuzz;
65			END IF;
66			
67			qry_fuzz := 'DROP TABLE ' || fuzz_tbl;
68			-- RAISE NOTICE '5 qry_fuzz: %', qry_fuzz;
69			EXECUTE qry_fuzz;
70
71		END LOOP;
72	END LOOP;
73
74	RETURN 'CREATE TABLE '|| assoc ||'_fuzz';
75
76END;
77$$ LANGUAGE 'plpgsql' WITH (isstrict);