Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitd10849d

Browse files
committed
add overloads for dump_statistic(), raise notice -> raise error
1 parent599fd15 commitd10849d

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_operatorinner join pg_namespace
26+
frompg_catalog.pg_operatorinner joinpg_catalog.pg_namespace
2727
on oprnamespace=pg_namespace.oid
2828
wherepg_operator.oid= opid
2929
into r;
30+
31+
if r isnull then
32+
raise exception'operator % does not exist', opid;
33+
end if;
3034

3135
ifr.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_typeinner join pg_namespace
60+
frompg_catalog.pg_typeinner joinpg_catalog.pg_namespace
5761
on typnamespace=pg_namespace.oid
5862
wherepg_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-
CREATEFUNCTIONto_schema_qualified_relname(reloidoid) RETURNSTEXTAS $$
70+
CREATEFUNCTIONto_schema_qualified_relation(reloidoid) RETURNSTEXTAS $$
6771
DECLARE
6872
resulttext;
6973

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

8690
BEGIN
87-
select attnamefrom pg_attribute
91+
select attname
92+
frompg_catalog.pg_attribute
8893
where attrelid= relation::regclassand attnum= colnum
8994
into result;
9095

9196
if result isnull then
92-
raisenotice'attribute #% of relation % not found',
97+
raiseexception'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 attnumfrom pg_attribute
111+
select attnum
112+
frompg_catalog.pg_attribute
107113
where attrelid= relation::regclassand attname= col
108114
into result;
109115

110116
if result isnull then
111-
raisenotice'attribute % of relation % not found',
117+
raiseexception'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
resulttext;
123129

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

129136
if result isnull then
130-
raisenotice'attribute % of relation % not found',
137+
raiseexception'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
resulttext;
142149

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

148156
if result isnull then
149-
raisenotice'attribute #% of relation % not found',
157+
raiseexception'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
resultoid;
161169

162170
BEGIN
163-
selectoidfrom pg_namespace
171+
selectoid
172+
frompg_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
resultoid;
175184

176185
BEGIN
177-
select relnamespacefrom pg_class
186+
select relnamespace
187+
frompg_catalog.pg_class
178188
whereoid= relation
179189
into result;
180190

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

185195

186-
CREATEFUNCTIONdump_statistic() RETURNS SETOFTEXTAS $$
196+
CREATEFUNCTIONdump_statistic(relidoid) RETURNS SETOFTEXTAS $$
187197
DECLARE
188198
resulttext;
189199

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

213223
BEGIN
214224
for rin
215-
select*frompg_catalog.pg_statistic
216-
where get_namespace(starelid)!= to_namespace('pg_catalog')
217-
and get_namespace(starelid)!= to_namespace('information_schema') loop
225+
select*frompg_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-
'WHEREto_schema_qualified_relname(starelid) ='|| relname||''
240+
'WHEREto_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+
CREATEFUNCTIONdump_statistic(schema_nametext, table_nametext) RETURNS SETOFTEXTAS $$
380+
DECLARE
381+
qual_relnametext;
382+
relidoid;
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+
CREATEFUNCTIONdump_statistic(schema_nametext) RETURNS SETOFTEXTAS $$
403+
DECLARE
404+
relidoid;
405+
itext;
406+
407+
BEGIN
408+
for relidin
409+
selectpg_class.oid
410+
frompg_catalog.pg_namespace
411+
inner joinpg_catalog.pg_class
412+
on relnamespace=pg_namespace.oid
413+
where nspname= schema_name loop
414+
415+
for iinselect dump_statistic(relid) loop
416+
return next i;
417+
end loop;
418+
end loop;
419+
END;
420+
$$ LANGUAGE plpgsql;
421+
422+
423+
CREATEFUNCTIONdump_statistic() RETURNS SETOFTEXTAS $$
424+
DECLARE
425+
relidoid;
426+
itext;
427+
428+
BEGIN
429+
for relidin
430+
selectpg_class.oid
431+
frompg_catalog.pg_namespace
432+
inner joinpg_catalog.pg_class
433+
on relnamespace=pg_namespace.oid loop
434+
435+
for iinselect dump_statistic(relid) loop
436+
return next i;
437+
end loop;
438+
end loop;
439+
END;
440+
$$ LANGUAGE plpgsql;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp