Skip to content

Commit d10849d

Browse files
funbringerfunbringer
committed
add overloads for dump_statistic(), raise notice -> raise error
1 parent 599fd15 commit d10849d

File tree

1 file changed

+96
-21
lines changed

1 file changed

+96
-21
lines changed

‎contrib/dump_stat/dump_stat--1.0.sql

Lines changed: 96 additions & 21 deletions
Original file line numberDiff line numberDiff line change
@@ -23,10 +23,14 @@ CREATE OR REPLACE FUNCTION to_schema_qualified_operator(opid oid) RETURNS TEXT A
2323
end if;
2424

2525
select nspname, oprname, oprleft, oprright
26-
from pg_operator inner join pg_namespace
26+
from pg_catalog.pg_operator inner join pg_catalog.pg_namespace
2727
on oprnamespace = pg_namespace.oid
2828
where pg_operator.oid = opid
2929
into r;
30+
31+
if r is null then
32+
raise exception 'operator % does not exist', opid;
33+
end if;
3034

3135
if r.oprleft = 0 then
3236
ltype := 'NONE';
@@ -53,7 +57,7 @@ CREATE OR REPLACE FUNCTION to_schema_qualified_type(typid oid) RETURNS TEXT AS $
5357

5458
BEGIN
5559
select quote_ident(nspname) || '.' || quote_ident(typname)
56-
from pg_type inner join pg_namespace
60+
from pg_catalog.pg_type inner join pg_catalog.pg_namespace
5761
on typnamespace = pg_namespace.oid
5862
where pg_type.oid = typid
5963
into result;
@@ -63,13 +67,13 @@ CREATE OR REPLACE FUNCTION to_schema_qualified_type(typid oid) RETURNS TEXT AS $
6367
$$ LANGUAGE plpgsql;
6468

6569

66-
CREATE FUNCTION to_schema_qualified_relname(reloid oid) RETURNS TEXT AS $$
70+
CREATE FUNCTION to_schema_qualified_relation(reloid oid) RETURNS TEXT AS $$
6771
DECLARE
6872
result text;
6973

7074
BEGIN
7175
select quote_ident(nspname) || '.' || quote_ident(relname)
72-
from pg_class inner join pg_namespace
76+
from pg_catalog.pg_class inner join pg_catalog.pg_namespace
7377
on relnamespace = pg_namespace.oid
7478
where pg_class.oid = reloid
7579
into result;
@@ -84,12 +88,13 @@ CREATE FUNCTION to_attname(relation text, colnum int2) RETURNS TEXT AS $$
8488
result text;
8589

8690
BEGIN
87-
select attname from pg_attribute
91+
select attname
92+
from pg_catalog.pg_attribute
8893
where attrelid = relation::regclass and attnum = colnum
8994
into result;
9095

9196
if result is null then
92-
raise notice 'attribute #% of relation % not found',
97+
raise exception 'attribute #% of relation % not found',
9398
colnum, quote_literal(relation);
9499
end if;
95100

@@ -103,12 +108,13 @@ CREATE FUNCTION to_attnum(relation text, col text) RETURNS INT2 AS $$
103108
result int2;
104109

105110
BEGIN
106-
select attnum from pg_attribute
111+
select attnum
112+
from pg_catalog.pg_attribute
107113
where attrelid = relation::regclass and attname = col
108114
into result;
109115

110116
if result is null then
111-
raise notice 'attribute % of relation % not found',
117+
raise exception 'attribute % of relation % not found',
112118
quote_literal(col), quote_literal(relation);
113119
end if;
114120

@@ -122,12 +128,13 @@ CREATE FUNCTION to_atttype(relation text, col text) RETURNS TEXT AS $$
122128
result text;
123129

124130
BEGIN
125-
select to_schema_qualified_type(atttypid) from pg_attribute
131+
select to_schema_qualified_type(atttypid)
132+
from pg_catalog.pg_attribute
126133
where attrelid = relation::regclass and attname = col
127134
into result;
128135

129136
if result is null then
130-
raise notice 'attribute % of relation % not found',
137+
raise exception 'attribute % of relation % not found',
131138
quote_literal(col), quote_literal(relation);
132139
end if;
133140

@@ -141,12 +148,13 @@ CREATE FUNCTION to_atttype(relation text, colnum int2) RETURNS TEXT AS $$
141148
result text;
142149

143150
BEGIN
144-
select to_schema_qualified_type(atttypid) from pg_attribute
151+
select to_schema_qualified_type(atttypid)
152+
from pg_catalog.pg_attribute
145153
where attrelid = relation::regclass and attnum = colnum
146154
into result;
147155

148156
if result is null then
149-
raise notice 'attribute #% of relation % not found',
157+
raise exception 'attribute #% of relation % not found',
150158
colnum, quote_literal(relation);
151159
end if;
152160

@@ -160,7 +168,8 @@ CREATE FUNCTION to_namespace(nsp text) RETURNS OID AS $$
160168
result oid;
161169

162170
BEGIN
163-
select oid from pg_namespace
171+
select oid
172+
from pg_catalog.pg_namespace
164173
where nspname = nsp
165174
into result;
166175

@@ -174,7 +183,8 @@ CREATE FUNCTION get_namespace(relation oid) RETURNS OID AS $$
174183
result oid;
175184

176185
BEGIN
177-
select relnamespace from pg_class
186+
select relnamespace
187+
from pg_catalog.pg_class
178188
where oid = relation
179189
into result;
180190

@@ -183,7 +193,7 @@ CREATE FUNCTION get_namespace(relation oid) RETURNS OID AS $$
183193
$$ LANGUAGE plpgsql;
184194

185195

186-
CREATE FUNCTION dump_statistic() RETURNS SETOF TEXT AS $$
196+
CREATE FUNCTION dump_statistic(relid oid) RETURNS SETOF TEXT AS $$
187197
DECLARE
188198
result text;
189199

@@ -212,11 +222,12 @@ CREATE FUNCTION dump_statistic() RETURNS SETOF TEXT AS $$
212222

213223
BEGIN
214224
for r in
215-
select * from pg_catalog.pg_statistic
216-
where get_namespace(starelid) != to_namespace('pg_catalog')
217-
and get_namespace(starelid) != to_namespace('information_schema') loop
225+
select * from pg_catalog.pg_statistic
226+
where starelid = relid
227+
and get_namespace(starelid) != to_namespace('information_schema')
228+
and get_namespace(starelid) != to_namespace('pg_catalog') loop
218229

219-
relname := to_schema_qualified_relname(r.starelid);
230+
relname := to_schema_qualified_relation(r.starelid);
220231
attname := quote_literal(to_attname(relname, r.staattnum));
221232
atttype := quote_literal(to_atttype(relname, r.staattnum));
222233
relname := quote_literal(relname); -- redefine relname
@@ -226,7 +237,7 @@ CREATE FUNCTION dump_statistic() RETURNS SETOF TEXT AS $$
226237

227238
cmd := 'WITH upsert as ( ' ||
228239
'UPDATE pg_catalog.pg_statistic SET %s ' ||
229-
'WHERE to_schema_qualified_relname(starelid) = ' || relname || ' '
240+
'WHERE to_schema_qualified_relation(starelid) = ' || relname || ' '
230241
'AND to_attname(' || relname || ', staattnum) = ' || attname || ' '
231242
'AND to_atttype(' || relname || ', staattnum) = ' || atttype || ' '
232243
'AND stainherit = ' || r.stainherit || ' ' ||
@@ -362,4 +373,68 @@ CREATE FUNCTION dump_statistic() RETURNS SETOF TEXT AS $$
362373

363374
return;
364375
END;
365-
$$ LANGUAGE plpgsql;
376+
$$ LANGUAGE plpgsql;
377+
378+
379+
CREATE FUNCTION dump_statistic(schema_name text, table_name text) RETURNS SETOF TEXT AS $$
380+
DECLARE
381+
qual_relname text;
382+
relid oid;
383+
384+
BEGIN
385+
qual_relname := quote_ident(schema_name) ||
386+
'.' || quote_ident(table_name);
387+
388+
return next dump_statistic(qual_relname::regclass);
389+
return;
390+
391+
EXCEPTION
392+
when invalid_schema_name then
393+
raise exception 'schema % does not exist',
394+
quote_literal(schema_name);
395+
when undefined_table then
396+
raise exception 'relation % does not exist',
397+
quote_literal(qual_relname);
398+
END;
399+
$$ LANGUAGE plpgsql;
400+
401+
402+
CREATE FUNCTION dump_statistic(schema_name text) RETURNS SETOF TEXT AS $$
403+
DECLARE
404+
relid oid;
405+
i text;
406+
407+
BEGIN
408+
for relid in
409+
select pg_class.oid
410+
from pg_catalog.pg_namespace
411+
inner join pg_catalog.pg_class
412+
on relnamespace = pg_namespace.oid
413+
where nspname = schema_name loop
414+
415+
for i in select dump_statistic(relid) loop
416+
return next i;
417+
end loop;
418+
end loop;
419+
END;
420+
$$ LANGUAGE plpgsql;
421+
422+
423+
CREATE FUNCTION dump_statistic() RETURNS SETOF TEXT AS $$
424+
DECLARE
425+
relid oid;
426+
i text;
427+
428+
BEGIN
429+
for relid in
430+
select pg_class.oid
431+
from pg_catalog.pg_namespace
432+
inner join pg_catalog.pg_class
433+
on relnamespace = pg_namespace.oid loop
434+
435+
for i in select dump_statistic(relid) loop
436+
return next i;
437+
end loop;
438+
end loop;
439+
END;
440+
$$ LANGUAGE plpgsql;

0 commit comments

Comments
 (0)