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

Commit33556af

Browse files
committed
Fix errors in key_column_usage.position_in_unique_constraint column recently
added to information_schema (per a SQL2003 addition). The original codingfailed if a referenced column participated in more than one pg_constraintentry. Also, it did not work if an FK relied directly on a unique indexwithout any constraint syntactic sugar --- this case is outside the SQL spec,but PG has always supported it, so it's reasonable for our information_schemato handle it too. Per bug#2750 from Stephen Haberman.Although this patch changes the initial catalog contents, I didn't forceinitdb. Any beta3 testers who need the fix can install it via CREATE ORREPLACE VIEW, so forcing them to initdb seems an unnecessary imposition.
1 parent87a5016 commit33556af

File tree

1 file changed

+32
-11
lines changed

1 file changed

+32
-11
lines changed

‎src/backend/catalog/information_schema.sql‎

Lines changed: 32 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -4,7 +4,7 @@
44
*
55
* Copyright (c) 2003-2006, PostgreSQL Global Development Group
66
*
7-
* $PostgreSQL: pgsql/src/backend/catalog/information_schema.sql,v 1.37 2006/09/14 22:05:06 tgl Exp $
7+
* $PostgreSQL: pgsql/src/backend/catalog/information_schema.sql,v 1.38 2006/11/10 18:10:10 tgl Exp $
88
*/
99

1010
/*
@@ -52,6 +52,28 @@ CREATE FUNCTION _pg_keysequal(smallint[], smallint[]) RETURNS boolean
5252
RETURNSNULLONNULL INPUT
5353
AS'select information_schema._pg_keyissubset($1, $2) and information_schema._pg_keyissubset($2, $1)';
5454

55+
/* Get the OID of the unique index that an FK constraint depends on*/
56+
CREATEFUNCTION_pg_underlying_index(oid) RETURNSoid
57+
LANGUAGE sql STRICT STABLE
58+
AS $$
59+
SELECT refobjidFROMpg_catalog.pg_depend
60+
WHERE classid='pg_catalog.pg_constraint'::pg_catalog.regclassAND
61+
objid= $1AND
62+
refclassid='pg_catalog.pg_class'::pg_catalog.regclassAND
63+
refobjsubid=0AND deptype='n'
64+
$$;
65+
66+
/* Given an index's OID and an underlying-table column number, return the
67+
* column's position in the index (NULL if not there)*/
68+
CREATEFUNCTION_pg_index_position(oid,smallint) RETURNSint
69+
LANGUAGE sql STRICT STABLE
70+
AS $$
71+
SELECT (ss.a).nFROM
72+
(SELECTinformation_schema._pg_expandarray(indkey)AS a
73+
FROMpg_catalog.pg_indexWHERE indexrelid= $1) ss
74+
WHERE (ss.a).x= $2;
75+
$$;
76+
5577
CREATEFUNCTION_pg_truetypid(pg_attribute, pg_type) RETURNSoid
5678
LANGUAGE sql
5779
IMMUTABLE
@@ -922,17 +944,16 @@ CREATE VIEW key_column_usage AS
922944
CAST(relnameAS sql_identifier)AS table_name,
923945
CAST(a.attnameAS sql_identifier)AS column_name,
924946
CAST((ss.x).nAS cardinal_number)AS ordinal_position,
925-
(
926-
SELECT CAST(aAS cardinal_number)
927-
FROM pg_constraint,
928-
(SELECT aFROM generate_series(1, array_upper(ss.confkey,1)) a)AS foo
929-
WHERE conrelid=ss.confrelid
930-
AND conkey[foo.a]=ss.confkey[(ss.x).n]
931-
)AS position_in_unique_constraint
947+
CAST(CASE WHEN contype='f' THEN
948+
_pg_index_position(_pg_underlying_index(ss.coid),
949+
ss.confkey[(ss.x).n])
950+
ELSENULL
951+
ENDAS cardinal_number)
952+
AS position_in_unique_constraint
932953
FROM pg_attribute a,
933-
(SELECTr.oid,r.relname,nc.nspnameAS nc_nspname,
954+
(SELECTr.oidAS roid,r.relname,nc.nspnameAS nc_nspname,
934955
nr.nspnameAS nr_nspname,
935-
c.conname,c.confkey,c.confrelid,
956+
c.oidAS coid,c.conname,c.contype,c.confkey,c.confrelid,
936957
_pg_expandarray(c.conkey)AS x
937958
FROM pg_namespace nr, pg_class r, pg_namespace nc,
938959
pg_constraint c
@@ -947,7 +968,7 @@ CREATE VIEW key_column_usage AS
947968
OR has_table_privilege(c.oid,'INSERT')
948969
OR has_table_privilege(c.oid,'UPDATE')
949970
OR has_table_privilege(c.oid,'REFERENCES')) )AS ss
950-
WHEREss.oid=a.attrelid
971+
WHEREss.roid=a.attrelid
951972
ANDa.attnum= (ss.x).x
952973
AND NOTa.attisdropped;
953974

0 commit comments

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