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

Commite341cdb

Browse files
committed
Further work on information_schema. _pg_keyissubset() wasn't quite
fully search-path-proof yet; also, element_types view did not work forparameters and result types of functions, because it didn't generatethe object_name for the function the same way the data_type_privilegesview does. While at it, centralize dependencies on INDEX_MAX_KEYS/FUNC_MAX_ARGS into a function returning setof int, so that it will beeasier to fix information_schema for nonstandard values of theseparameters.
1 parenteda80f0 commite341cdb

File tree

1 file changed

+63
-52
lines changed

1 file changed

+63
-52
lines changed

‎src/backend/catalog/information_schema.sql

Lines changed: 63 additions & 52 deletions
Original file line numberDiff line numberDiff line change
@@ -4,7 +4,7 @@
44
*
55
* Copyright 2003, PostgreSQL Global Development Group
66
*
7-
* $Id: information_schema.sql,v 1.14 2003/10/1812:53:35 petere Exp $
7+
* $Id: information_schema.sql,v 1.15 2003/10/1819:06:10 tgl Exp $
88
*/
99

1010
/*
@@ -178,7 +178,10 @@ CREATE VIEW column_privileges AS
178178
UNION ALL
179179
SELECT0,0,'PUBLIC'
180180
)AS grantee (usesysid, grosysid, name),
181-
(SELECT'SELECT'UNION ALLSELECT'INSERT'UNION ALLSELECT'UPDATE'UNION ALLSELECT'REFERENCES')AS pr (type)
181+
(SELECT'SELECT'UNION ALL
182+
SELECT'INSERT'UNION ALL
183+
SELECT'UPDATE'UNION ALL
184+
SELECT'REFERENCES')AS pr (type)
182185

183186
WHEREa.attrelid=c.oid
184187
ANDc.relnamespace=nc.oid
@@ -389,6 +392,22 @@ GRANT SELECT ON columns TO PUBLIC;
389392
* CONSTRAINT_COLUMN_USAGE view
390393
*/
391394

395+
/* This returns the integers from 1 to INDEX_MAX_KEYS/FUNC_MAX_ARGS*/
396+
CREATEFUNCTION_pg_keypositions() RETURNS SETOFinteger
397+
LANGUAGE sql
398+
IMMUTABLE
399+
AS'select 1 union all select 2 union all select 3 union all
400+
select 4 union all select 5 union all select 6 union all
401+
select 7 union all select 8 union all select 9 union all
402+
select 10 union all select 11 union all select 12 union all
403+
select 13 union all select 14 union all select 15 union all
404+
select 16 union all select 17 union all select 18 union all
405+
select 19 union all select 20 union all select 21 union all
406+
select 22 union all select 23 union all select 24 union all
407+
select 25 union all select 26 union all select 27 union all
408+
select 28 union all select 29 union all select 30 union all
409+
select 31 union all select 32';
410+
392411
CREATEVIEWconstraint_column_usageAS
393412
SELECT CAST(current_database()AS sql_identifier)AS table_catalog,
394413
CAST(tblschemaAS sql_identifier)AS table_schema,
@@ -419,14 +438,8 @@ CREATE VIEW constraint_column_usage AS
419438

420439
/* unique/primary key/foreign key constraints*/
421440
SELECTnr.nspname,r.relname,r.relowner,a.attname,nc.nspname,c.conname
422-
FROM pg_namespace nr, pg_class r, pg_attribute a, pg_namespace nc, pg_constraint c,
423-
(select1union allselect2union allselect3union allselect4union allselect5union all
424-
select6union allselect7union allselect8union allselect9union allselect10union all
425-
select11union allselect12union allselect13union allselect14union allselect15union all
426-
select16union allselect17union allselect18union allselect19union allselect20union all
427-
select21union allselect22union allselect23union allselect24union allselect25union all
428-
select26union allselect27union allselect28union allselect29union allselect30union all
429-
select31union allselect32)AS pos(n)
441+
FROM pg_namespace nr, pg_class r, pg_attribute a, pg_namespace nc,
442+
pg_constraint c, _pg_keypositions()AS pos(n)
430443
WHEREnr.oid=r.relnamespace
431444
ANDr.oid=a.attrelid
432445
ANDr.oid=c.conrelid
@@ -659,16 +672,8 @@ CREATE VIEW key_column_usage AS
659672
CAST(a.attnameAS sql_identifier)AS column_name,
660673
CAST(pos.nAS cardinal_number)AS ordinal_position
661674

662-
FROM pg_namespace nr, pg_class r, pg_attribute a, pg_namespace nc, pg_constraint c,
663-
pg_user u,
664-
(select1union allselect2union allselect3union allselect4union allselect5union all
665-
select6union allselect7union allselect8union allselect9union allselect10union all
666-
select11union allselect12union allselect13union allselect14union allselect15union all
667-
select16union allselect17union allselect18union allselect19union allselect20union all
668-
select21union allselect22union allselect23union allselect24union allselect25union all
669-
select26union allselect27union allselect28union allselect29union allselect30union all
670-
select31union allselect32)AS pos(n)
671-
675+
FROM pg_namespace nr, pg_class r, pg_attribute a, pg_namespace nc,
676+
pg_constraint c, pg_user u, _pg_keypositions()AS pos(n)
672677
WHEREnr.oid=r.relnamespace
673678
ANDr.oid=a.attrelid
674679
ANDr.oid=c.conrelid
@@ -693,7 +698,7 @@ CREATE VIEW parameters AS
693698
SELECT CAST(current_database()AS sql_identifier)AS specific_catalog,
694699
CAST(n.nspnameAS sql_identifier)AS specific_schema,
695700
CAST(p.proname||'_'|| CAST(p.oidAStext)AS sql_identifier)AS specific_name,
696-
CAST(n+1AS cardinal_number)AS ordinal_position,
701+
CAST(pos.nAS cardinal_number)AS ordinal_position,
697702
CAST('IN'AS character_data)AS parameter_mode,
698703
CAST('NO'AS character_data)AS is_result,
699704
CAST('NO'AS character_data)AS as_locator,
@@ -724,19 +729,13 @@ CREATE VIEW parameters AS
724729
CAST(nullAS sql_identifier)AS scope_schema,
725730
CAST(nullAS sql_identifier)AS scope_name,
726731
CAST(nullAS cardinal_number)AS maximum_cardinality,
727-
CAST(n+1AS sql_identifier)AS dtd_identifier
732+
CAST(pos.nAS sql_identifier)AS dtd_identifier
728733

729734
FROM pg_namespace n, pg_proc p, pg_type t, pg_namespace nt, pg_user u,
730-
(select0union allselect1union allselect2union allselect3union allselect4union all
731-
select5union allselect6union allselect7union allselect8union allselect9union all
732-
select10union allselect11union allselect12union allselect13union allselect14union all
733-
select15union allselect16union allselect17union allselect18union allselect19union all
734-
select20union allselect21union allselect22union allselect23union allselect24union all
735-
select25union allselect26union allselect27union allselect28union allselect29union all
736-
select30union allselect31)AS pos(n)
737-
738-
WHEREn.oid=p.pronamespaceANDp.pronargs>pos.n
739-
ANDp.proargtypes[n]=t.oidANDt.typnamespace=nt.oid
735+
_pg_keypositions()AS pos(n)
736+
737+
WHEREn.oid=p.pronamespaceANDp.pronargs>=pos.n
738+
ANDp.proargtypes[pos.n-1]=t.oidANDt.typnamespace=nt.oid
740739
ANDp.proowner=u.usesysid
741740
AND (u.usename=current_userOR has_function_privilege(p.oid,'EXECUTE'));
742741

@@ -752,7 +751,7 @@ CREATE FUNCTION _pg_keyissubset(smallint[], smallint[]) RETURNS boolean
752751
LANGUAGE sql
753752
IMMUTABLE
754753
RETURNSNULLONNULL INPUT
755-
AS'select $1[1] is null or ($1[1] = any ($2) and coalesce(information_schema._pg_keyissubset($1[2:array_upper($1,1)], $2), true))';
754+
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))';
756755

757756
CREATEFUNCTION_pg_keysequal(smallint[],smallint[]) RETURNSboolean
758757
LANGUAGE sql
@@ -831,7 +830,10 @@ CREATE VIEW role_column_grants AS
831830
pg_namespace nc,
832831
pg_user u_grantor,
833832
pg_group g_grantee,
834-
(SELECT'SELECT'UNION ALLSELECT'INSERT'UNION ALLSELECT'UPDATE'UNION ALLSELECT'REFERENCES')AS pr (type)
833+
(SELECT'SELECT'UNION ALL
834+
SELECT'INSERT'UNION ALL
835+
SELECT'UPDATE'UNION ALL
836+
SELECT'REFERENCES')AS pr (type)
835837

836838
WHEREa.attrelid=c.oid
837839
ANDc.relnamespace=nc.oid
@@ -900,8 +902,13 @@ CREATE VIEW role_table_grants AS
900902
pg_namespace nc,
901903
pg_user u_grantor,
902904
pg_group g_grantee,
903-
(SELECT'SELECT'UNION ALLSELECT'DELETE'UNION ALLSELECT'INSERT'UNION ALLSELECT'UPDATE'
904-
UNION ALLSELECT'REFERENCES'UNION ALLSELECT'RULE'UNION ALLSELECT'TRIGGER')AS pr (type)
905+
(SELECT'SELECT'UNION ALL
906+
SELECT'DELETE'UNION ALL
907+
SELECT'INSERT'UNION ALL
908+
SELECT'UPDATE'UNION ALL
909+
SELECT'REFERENCES'UNION ALL
910+
SELECT'RULE'UNION ALL
911+
SELECT'TRIGGER')AS pr (type)
905912

906913
WHEREc.relnamespace=nc.oid
907914
ANDc.relkindIN ('r','v')
@@ -1310,8 +1317,13 @@ CREATE VIEW table_privileges AS
13101317
UNION ALL
13111318
SELECT0,0,'PUBLIC'
13121319
)AS grantee (usesysid, grosysid, name),
1313-
(SELECT'SELECT'UNION ALLSELECT'DELETE'UNION ALLSELECT'INSERT'UNION ALLSELECT'UPDATE'
1314-
UNION ALLSELECT'REFERENCES'UNION ALLSELECT'RULE'UNION ALLSELECT'TRIGGER')AS pr (type)
1320+
(SELECT'SELECT'UNION ALL
1321+
SELECT'DELETE'UNION ALL
1322+
SELECT'INSERT'UNION ALL
1323+
SELECT'UPDATE'UNION ALL
1324+
SELECT'REFERENCES'UNION ALL
1325+
SELECT'RULE'UNION ALL
1326+
SELECT'TRIGGER')AS pr (type)
13151327

13161328
WHEREc.relnamespace=nc.oid
13171329
ANDc.relkindIN ('r','v')
@@ -1414,7 +1426,9 @@ CREATE VIEW triggers AS
14141426
CAST(nullAS sql_identifier)AS condition_reference_new_table
14151427

14161428
FROM pg_namespace n, pg_class c, pg_trigger t, pg_user u,
1417-
(SELECT4,'INSERT'UNION ALLSELECT8,'DELETE'UNION ALLSELECT16,'UPDATE')AS em (num,text)
1429+
(SELECT4,'INSERT'UNION ALL
1430+
SELECT8,'DELETE'UNION ALL
1431+
SELECT16,'UPDATE')AS em (num,text)
14181432

14191433
WHEREn.oid=c.relnamespace
14201434
ANDc.oid=t.tgrelid
@@ -1640,7 +1654,8 @@ CREATE VIEW element_types AS
16401654
FROM pg_namespace n, pg_type at, pg_namespace nbt, pg_type bt,
16411655
(
16421656
/* columns*/
1643-
SELECTc.relnamespace,c.relname,'TABLE'::text,a.attnum,a.atttypid
1657+
SELECTc.relnamespace, CAST(c.relnameAS sql_identifier),
1658+
'TABLE'::text,a.attnum,a.atttypid
16441659
FROM pg_class c, pg_attribute a
16451660
WHEREc.oid=a.attrelid
16461661
ANDc.relkindIN ('r','v')
@@ -1649,28 +1664,24 @@ CREATE VIEW element_types AS
16491664
UNION ALL
16501665

16511666
/* domains*/
1652-
SELECTt.typnamespace,t.typname,'DOMAIN'::text,1,t.typbasetype
1667+
SELECTt.typnamespace, CAST(t.typnameAS sql_identifier),
1668+
'DOMAIN'::text,1,t.typbasetype
16531669
FROM pg_type t
16541670
WHEREt.typtype='d'
16551671

16561672
UNION ALL
16571673

16581674
/* parameters*/
1659-
SELECTp.pronamespace,p.proname,'ROUTINE'::text,pos.n+1,p.proargtypes[n]
1660-
FROM pg_proc p,
1661-
(select0union allselect1union allselect2union allselect3union allselect4union all
1662-
select5union allselect6union allselect7union allselect8union allselect9union all
1663-
select10union allselect11union allselect12union allselect13union allselect14union all
1664-
select15union allselect16union allselect17union allselect18union allselect19union all
1665-
select20union allselect21union allselect22union allselect23union allselect24union all
1666-
select25union allselect26union allselect27union allselect28union allselect29union all
1667-
select30union allselect31)AS pos(n)
1668-
WHEREp.pronargs>pos.n
1675+
SELECTp.pronamespace, CAST(p.proname||'_'|| CAST(p.oidAStext)AS sql_identifier),
1676+
'ROUTINE'::text,pos.n,p.proargtypes[pos.n-1]
1677+
FROM pg_proc p, _pg_keypositions()AS pos(n)
1678+
WHEREp.pronargs>=pos.n
16691679

16701680
UNION ALL
16711681

16721682
/* result types*/
1673-
SELECTp.pronamespace,p.proname,'ROUTINE'::text,0,p.prorettype
1683+
SELECTp.pronamespace, CAST(p.proname||'_'|| CAST(p.oidAStext)AS sql_identifier),
1684+
'ROUTINE'::text,0,p.prorettype
16741685
FROM pg_proc p
16751686

16761687
)AS x (objschema, objname, objtype, objdtdid, objtypeid)

0 commit comments

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