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

Commit1a76550

Browse files
committed
Fix information schema views to return NULL for precision and scale of
an unconstrained numeric column. Also, factor out some duplicate codeinto functions, to ease future maintenance.
1 parent5ca40c5 commit1a76550

File tree

1 file changed

+142
-105
lines changed

1 file changed

+142
-105
lines changed

‎src/backend/catalog/information_schema.sql

Lines changed: 142 additions & 105 deletions
Original file line numberDiff line numberDiff line change
@@ -4,7 +4,7 @@
44
*
55
* Copyright 2003, PostgreSQL Global Development Group
66
*
7-
* $PostgreSQL: pgsql/src/backend/catalog/information_schema.sql,v 1.23 2004/02/03 08:29:56 joe Exp $
7+
* $PostgreSQL: pgsql/src/backend/catalog/information_schema.sql,v 1.24 2004/06/22 22:30:32 tgl Exp $
88
*/
99

1010
/*
@@ -26,6 +26,133 @@ CREATE SCHEMA information_schema;
2626
GRANT USAGEON SCHEMA information_schema TO PUBLIC;
2727
SET search_path TO information_schema, public;
2828

29+
/*
30+
* A few supporting functions first ...
31+
*/
32+
33+
/* This returns the integers from 1 to INDEX_MAX_KEYS/FUNC_MAX_ARGS*/
34+
CREATEFUNCTION_pg_keypositions() RETURNS SETOFinteger
35+
LANGUAGE sql
36+
IMMUTABLE
37+
AS'select g.s
38+
from generate_series(1,current_setting(''max_index_keys'')::int,1)
39+
as g(s)';
40+
41+
CREATEFUNCTION_pg_keyissubset(smallint[],smallint[]) RETURNSboolean
42+
LANGUAGE sql
43+
IMMUTABLE
44+
RETURNSNULLONNULL INPUT
45+
AS'select $1[1] is null or ($1[1] = any ($2) and coalesce(information_schema._pg_keyissubset($1[2:pg_catalog.array_upper($1,1)], $2), true))';
46+
47+
CREATEFUNCTION_pg_keysequal(smallint[],smallint[]) RETURNSboolean
48+
LANGUAGE sql
49+
IMMUTABLE
50+
RETURNSNULLONNULL INPUT
51+
AS'select information_schema._pg_keyissubset($1, $2) and information_schema._pg_keyissubset($2, $1)';
52+
53+
CREATEFUNCTION_pg_truetypid(pg_attribute, pg_type) RETURNSoid
54+
LANGUAGE sql
55+
IMMUTABLE
56+
RETURNSNULLONNULL INPUT
57+
AS
58+
$$SELECT CASE WHEN $2.typtype='d' THEN $2.typbasetype ELSE $1.atttypid END$$;
59+
60+
CREATEFUNCTION_pg_truetypmod(pg_attribute, pg_type) RETURNS int4
61+
LANGUAGE sql
62+
IMMUTABLE
63+
RETURNSNULLONNULL INPUT
64+
AS
65+
$$SELECT CASE WHEN $2.typtype='d' THEN $2.typtypmod ELSE $1.atttypmod END$$;
66+
67+
-- these functions encapsulate knowledge about the encoding of typmod:
68+
69+
CREATEFUNCTION_pg_char_max_length(typidoid, typmod int4) RETURNSinteger
70+
LANGUAGE sql
71+
IMMUTABLE
72+
RETURNSNULLONNULL INPUT
73+
AS
74+
$$SELECT
75+
CASE WHEN $2=-1/* default typmod*/
76+
THENnull
77+
WHEN $1IN (1042,1043)/* char, varchar*/
78+
THEN $2-4
79+
WHEN $1IN (1560,1562)/* bit, varbit*/
80+
THEN $2
81+
ELSEnull
82+
END$$;
83+
84+
CREATEFUNCTION_pg_char_octet_length(typidoid, typmod int4) RETURNSinteger
85+
LANGUAGE sql
86+
IMMUTABLE
87+
RETURNSNULLONNULL INPUT
88+
AS
89+
$$SELECT
90+
CASE WHEN $1IN (25,1042,1043)/* text, char, varchar*/
91+
THEN CAST(2^30ASinteger)
92+
ELSEnull
93+
END$$;
94+
95+
CREATEFUNCTION_pg_numeric_precision(typidoid, typmod int4) RETURNSinteger
96+
LANGUAGE sql
97+
IMMUTABLE
98+
RETURNSNULLONNULL INPUT
99+
AS
100+
$$SELECT
101+
CASE $1
102+
WHEN21/*int2*/ THEN16
103+
WHEN23/*int4*/ THEN32
104+
WHEN20/*int8*/ THEN64
105+
WHEN1700/*numeric*/ THEN
106+
CASE WHEN $2=-1
107+
THENnull
108+
ELSE (($2-4)>>16) &65535
109+
END
110+
WHEN700/*float4*/ THEN24/*FLT_MANT_DIG*/
111+
WHEN701/*float8*/ THEN53/*DBL_MANT_DIG*/
112+
ELSEnull
113+
END$$;
114+
115+
CREATEFUNCTION_pg_numeric_precision_radix(typidoid, typmod int4) RETURNSinteger
116+
LANGUAGE sql
117+
IMMUTABLE
118+
RETURNSNULLONNULL INPUT
119+
AS
120+
$$SELECT
121+
CASE WHEN $1IN (21,23,20,700,701) THEN2
122+
WHEN $1IN (1700) THEN10
123+
ELSEnull
124+
END$$;
125+
126+
CREATEFUNCTION_pg_numeric_scale(typidoid, typmod int4) RETURNSinteger
127+
LANGUAGE sql
128+
IMMUTABLE
129+
RETURNSNULLONNULL INPUT
130+
AS
131+
$$SELECT
132+
CASE WHEN $1IN (21,23,20) THEN0
133+
WHEN $1IN (1700) THEN
134+
CASE WHEN $2=-1
135+
THENnull
136+
ELSE ($2-4) &65535
137+
END
138+
ELSEnull
139+
END$$;
140+
141+
CREATEFUNCTION_pg_datetime_precision(typidoid, typmod int4) RETURNSinteger
142+
LANGUAGE sql
143+
IMMUTABLE
144+
RETURNSNULLONNULL INPUT
145+
AS
146+
$$SELECT
147+
CASE WHEN $2=-1/* default typmod*/
148+
THENnull
149+
WHEN $1IN (1083,1114,1184,1266)/* time, timestamp, same + tz*/
150+
THEN $2
151+
WHEN $1IN (1186)/* interval*/
152+
THEN $2 &65535
153+
ELSEnull
154+
END$$;
155+
29156

30157
-- 20.3 INFORMATION_SCHEMA_CATALOG_NAME view appears later.
31158

@@ -237,7 +364,7 @@ CREATE VIEW columns AS
237364
CAST(a.attnameAS sql_identifier)AS column_name,
238365
CAST(a.attnumAS cardinal_number)AS ordinal_position,
239366
CAST(
240-
CASE WHENu.usename=current_user THENa.adsrc ELSEnull END
367+
CASE WHENu.usename=current_user THENad.adsrc ELSEnull END
241368
AS character_data)
242369
AS column_default,
243370
CAST(CASE WHENa.attnotnullOR (t.typtype='d'ANDt.typnotnull) THEN'NO' ELSE'YES' END
@@ -258,83 +385,32 @@ CREATE VIEW columns AS
258385
AS data_type,
259386

260387
CAST(
261-
CASE WHENt.typtype='d' THEN
262-
CASE WHENt.typbasetypeIN (1042,1043)ANDt.typtypmod<>-1
263-
THENt.typtypmod-4/* char, varchar*/
264-
WHENt.typbasetypeIN (1560,1562)ANDt.typtypmod<>-1
265-
THENt.typtypmod/* bit, varbit*/
266-
ELSEnull END
267-
ELSE
268-
CASE WHENa.atttypidIN (1042,1043)ANDa.atttypmod<>-1
269-
THENa.atttypmod-4
270-
WHENa.atttypidIN (1560,1562)ANDa.atttypmod<>-1
271-
THENa.atttypmod
272-
ELSEnull END
273-
END
388+
_pg_char_max_length(_pg_truetypid(a, t), _pg_truetypmod(a, t))
274389
AS cardinal_number)
275390
AS character_maximum_length,
276391

277392
CAST(
278-
CASE WHENt.typtype='d' THEN
279-
CASE WHENt.typbasetypeIN (25,1042,1043) THEN2^30 ELSEnull END
280-
ELSE
281-
CASE WHENa.atttypidIN (25,1042,1043) THEN2^30 ELSEnull END
282-
END
393+
_pg_char_octet_length(_pg_truetypid(a, t), _pg_truetypmod(a, t))
283394
AS cardinal_number)
284395
AS character_octet_length,
285396

286397
CAST(
287-
CASE (CASE WHENt.typtype='d' THENt.typbasetype ELSEa.atttypid END)
288-
WHEN21/*int2*/ THEN16
289-
WHEN23/*int4*/ THEN32
290-
WHEN20/*int8*/ THEN64
291-
WHEN1700/*numeric*/ THEN ((CASE WHENt.typtype='d' THENt.typtypmod ELSEa.atttypmod END-4)>>16) &65535
292-
WHEN700/*float4*/ THEN24/*FLT_MANT_DIG*/
293-
WHEN701/*float8*/ THEN53/*DBL_MANT_DIG*/
294-
ELSEnull END
398+
_pg_numeric_precision(_pg_truetypid(a, t), _pg_truetypmod(a, t))
295399
AS cardinal_number)
296400
AS numeric_precision,
297401

298402
CAST(
299-
CASE WHENt.typtype='d' THEN
300-
CASE WHENt.typbasetypeIN (21,23,20,700,701) THEN2
301-
WHENt.typbasetypeIN (1700) THEN10
302-
ELSEnull END
303-
ELSE
304-
CASE WHENa.atttypidIN (21,23,20,700,701) THEN2
305-
WHENa.atttypidIN (1700) THEN10
306-
ELSEnull END
307-
END
403+
_pg_numeric_precision_radix(_pg_truetypid(a, t), _pg_truetypmod(a, t))
308404
AS cardinal_number)
309405
AS numeric_precision_radix,
310406

311407
CAST(
312-
CASE WHENt.typtype='d' THEN
313-
CASE WHENt.typbasetypeIN (21,23,20) THEN0
314-
WHENt.typbasetypeIN (1700) THEN (t.typtypmod-4) &65535
315-
ELSEnull END
316-
ELSE
317-
CASE WHENa.atttypidIN (21,23,20) THEN0
318-
WHENa.atttypidIN (1700) THEN (a.atttypmod-4) &65535
319-
ELSEnull END
320-
END
408+
_pg_numeric_scale(_pg_truetypid(a, t), _pg_truetypmod(a, t))
321409
AS cardinal_number)
322410
AS numeric_scale,
323411

324412
CAST(
325-
CASE WHENt.typtype='d' THEN
326-
CASE WHENt.typbasetypeIN (1083,1114,1184,1266)
327-
THEN (CASE WHENt.typtypmod<>-1 THENt.typtypmod ELSEnull END)
328-
WHENt.typbasetypeIN (1186)
329-
THEN (CASE WHENt.typtypmod<>-1 THENt.typtypmod &65535 ELSEnull END)
330-
ELSEnull END
331-
ELSE
332-
CASE WHENa.atttypidIN (1083,1114,1184,1266)
333-
THEN (CASE WHENa.atttypmod<>-1 THENa.atttypmod ELSEnull END)
334-
WHENa.atttypidIN (1186)
335-
THEN (CASE WHENa.atttypmod<>-1 THENa.atttypmod &65535 ELSEnull END)
336-
ELSEnull END
337-
END
413+
_pg_datetime_precision(_pg_truetypid(a, t), _pg_truetypmod(a, t))
338414
AS cardinal_number)
339415
AS datetime_precision,
340416

@@ -368,7 +444,7 @@ CREATE VIEW columns AS
368444
CAST(a.attnumAS sql_identifier)AS dtd_identifier,
369445
CAST('NO'AS character_data)AS is_self_referencing
370446

371-
FROM (pg_attributeLEFT JOIN pg_attrdefON attrelid= adrelidAND attnum= adnum)AS a,
447+
FROM (pg_attributeaLEFT JOIN pg_attrdefadON attrelid= adrelidAND attnum= adnum),
372448
pg_class c, pg_namespace nc, pg_user u,
373449
(pg_type tJOIN pg_namespace ntON (t.typnamespace=nt.oid))
374450
LEFT JOIN (pg_type btJOIN pg_namespace nbtON (bt.typnamespace=nbt.oid))
@@ -395,14 +471,6 @@ GRANT SELECT ON columns TO PUBLIC;
395471
* CONSTRAINT_COLUMN_USAGE view
396472
*/
397473

398-
/* This returns the integers from 1 to INDEX_MAX_KEYS/FUNC_MAX_ARGS*/
399-
CREATEFUNCTION_pg_keypositions() RETURNS SETOFinteger
400-
LANGUAGE sql
401-
IMMUTABLE
402-
AS'select g.s
403-
from generate_series(1,current_setting(''max_index_keys'')::int,1)
404-
as g(s)';
405-
406474
CREATEVIEWconstraint_column_usageAS
407475
SELECT CAST(current_database()AS sql_identifier)AS table_catalog,
408476
CAST(tblschemaAS sql_identifier)AS table_schema,
@@ -551,16 +619,12 @@ CREATE VIEW domains AS
551619
AS data_type,
552620

553621
CAST(
554-
CASE WHENt.typbasetypeIN (1042,1043)ANDt.typtypmod<>-1
555-
THENt.typtypmod-4/* char, varchar*/
556-
WHENt.typbasetypeIN (1560,1562)ANDt.typtypmod<>-1
557-
THENt.typtypmod/* bit, varbit*/
558-
ELSEnull END
622+
_pg_char_max_length(t.typbasetype,t.typtypmod)
559623
AS cardinal_number)
560624
AS character_maximum_length,
561625

562626
CAST(
563-
CASE WHENt.typbasetypeIN (25,1042,1043) THEN2^30 ELSEnull END
627+
_pg_char_octet_length(t.typbasetype,t.typtypmod)
564628
AS cardinal_number)
565629
AS character_octet_length,
566630

@@ -573,37 +637,22 @@ CREATE VIEW domains AS
573637
CAST(nullAS sql_identifier)AS collation_name,
574638

575639
CAST(
576-
CASEt.typbasetype
577-
WHEN21/*int2*/ THEN16
578-
WHEN23/*int4*/ THEN32
579-
WHEN20/*int8*/ THEN64
580-
WHEN1700/*numeric*/ THEN ((t.typtypmod-4)>>16) &65535
581-
WHEN700/*float4*/ THEN24/*FLT_MANT_DIG*/
582-
WHEN701/*float8*/ THEN53/*DBL_MANT_DIG*/
583-
ELSEnull END
640+
_pg_numeric_precision(t.typbasetype,t.typtypmod)
584641
AS cardinal_number)
585642
AS numeric_precision,
586643

587644
CAST(
588-
CASE WHENt.typbasetypeIN (21,23,20,700,701) THEN2
589-
WHENt.typbasetypeIN (1700) THEN10
590-
ELSEnull END
645+
_pg_numeric_precision_radix(t.typbasetype,t.typtypmod)
591646
AS cardinal_number)
592647
AS numeric_precision_radix,
593648

594649
CAST(
595-
CASE WHENt.typbasetypeIN (21,23,20) THEN0
596-
WHENt.typbasetypeIN (1700) THEN (t.typtypmod-4) &65535
597-
ELSEnull END
650+
_pg_numeric_scale(t.typbasetype,t.typtypmod)
598651
AS cardinal_number)
599652
AS numeric_scale,
600653

601654
CAST(
602-
CASE WHENt.typbasetypeIN (1083,1114,1184,1266)
603-
THEN (CASE WHENt.typtypmod<>-1 THENt.typtypmod ELSEnull END)
604-
WHENt.typbasetypeIN (1186)
605-
THEN (CASE WHENt.typtypmod<>-1 THENt.typtypmod &65535 ELSEnull END)
606-
ELSEnull END
655+
_pg_datetime_precision(t.typbasetype,t.typtypmod)
607656
AS cardinal_number)
608657
AS datetime_precision,
609658

@@ -740,18 +789,6 @@ GRANT SELECT ON parameters TO PUBLIC;
740789
* REFERENTIAL_CONSTRAINTS view
741790
*/
742791

743-
CREATEFUNCTION_pg_keyissubset(smallint[],smallint[]) RETURNSboolean
744-
LANGUAGE sql
745-
IMMUTABLE
746-
RETURNSNULLONNULL INPUT
747-
AS'select $1[1] is null or ($1[1] = any ($2) and coalesce(information_schema._pg_keyissubset($1[2:pg_catalog.array_upper($1,1)], $2), true))';
748-
749-
CREATEFUNCTION_pg_keysequal(smallint[],smallint[]) RETURNSboolean
750-
LANGUAGE sql
751-
IMMUTABLE
752-
RETURNSNULLONNULL INPUT
753-
AS'select information_schema._pg_keyissubset($1, $2) and information_schema._pg_keyissubset($2, $1)';
754-
755792
CREATEVIEWreferential_constraintsAS
756793
SELECT CAST(current_database()AS sql_identifier)AS constraint_catalog,
757794
CAST(ncon.nspnameAS sql_identifier)AS constraint_schema,

0 commit comments

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