1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
|
/*
* findoidjoins.c, requires src/interfaces/libpgeasy
*
*/
#include "postgres_fe.h"
#include "libpq-fe.h"
#include "halt.h"
#include "libpgeasy.h"
PGresult *attres,
*relres;
int
main(int argc, char **argv)
{
char query[4000];
char relname[256];
char relname2[256];
char attname[256];
char typname[256];
int count;
char optstr[256];
if (argc != 2)
halt("Usage: %s database\n", argv[0]);
snprintf(optstr, 256, "dbname=%s", argv[1]);
connectdb(optstr);
on_error_continue();
on_error_stop();
doquery("BEGIN WORK");
doquery("\
DECLARE c_attributes BINARY CURSOR FOR \
SELECT typname, relname, a.attname \
FROM pg_class c, pg_attribute a, pg_type t \
WHERE a.attnum > 0 AND \
relkind = 'r' AND \
relhasrules = 'f' AND \
(typname = 'oid' OR \
typname = 'regproc') AND \
a.attrelid = c.oid AND \
a.atttypid = t.oid \
ORDER BY 2, a.attnum ; \
");
doquery("FETCH ALL IN c_attributes");
attres = get_result();
doquery("\
DECLARE c_relations BINARY CURSOR FOR \
SELECT relname \
FROM pg_class c \
WHERE relkind = 'r' AND \
relhasrules = 'f' \
ORDER BY 1; \
");
doquery("FETCH ALL IN c_relations");
relres = get_result();
set_result(attres);
while (fetch(typname, relname, attname) != END_OF_TUPLES)
{
set_result(relres);
reset_fetch();
while (fetch(relname2) != END_OF_TUPLES)
{
unset_result(relres);
if (strcmp(typname, "oid") == 0)
sprintf(query, "\
DECLARE c_matches BINARY CURSOR FOR \
SELECT count(*) \
FROM %s t1, %s t2 \
WHERE t1.%s = t2.oid ", relname, relname2, attname);
else
sprintf(query, "\
DECLARE c_matches BINARY CURSOR FOR \
SELECT count(*) \
FROM %s t1, %s t2 \
WHERE RegprocToOid(t1.%s) = t2.oid ", relname, relname2, attname);
doquery(query);
doquery("FETCH ALL IN c_matches");
fetch(&count);
if (count != 0)
printf("Join %s.%s => %s.oid\n", relname, attname, relname2);
doquery("CLOSE c_matches");
set_result(relres);
}
set_result(attres);
}
set_result(relres);
doquery("CLOSE c_relations");
PQclear(relres);
set_result(attres);
doquery("CLOSE c_attributes");
PQclear(attres);
unset_result(attres);
doquery("COMMIT WORK");
disconnectdb();
return 0;
}
|
