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

Commit3af7217

Browse files
alvherrepetere
andcommitted
Update information_schema definition for not-null constraints
Now that we have catalogued not-null constraints, our information_schemadefinition can be updated to grab those rather than fabricate syntheticdefinitions.Note that we still don't have catalog rows for not-null constraints ondomains, but we've never had not-null constraints listed ininformation_schema, so that's a problem to be solved separately.Co-authored-by: Peter Eisentraut <peter.eisentraut@enterprisedb.com>Co-authored-by: Álvaro Herrera <alvherre@alvh.no-ip.org>Discussion:https://postgr.es/m/81b461c4-edab-5d8c-2f88-203108425340@enterprisedb.comDiscussion:https://postgr.es/m/202309041710.psytrxlsiqex@alvherre.pgsql
1 parente1c6db6 commit3af7217

File tree

2 files changed

+28
-48
lines changed

2 files changed

+28
-48
lines changed

‎src/backend/catalog/information_schema.sql

Lines changed: 27 additions & 47 deletions
Original file line numberDiff line numberDiff line change
@@ -444,22 +444,19 @@ CREATE VIEW check_constraints AS
444444
WHERE pg_has_role(coalesce(c.relowner,t.typowner),'USAGE')
445445
ANDcon.contype='c'
446446

447-
UNION
447+
UNION ALL
448448
-- not-null constraints
449-
450-
SELECT CAST(current_database()AS sql_identifier)AS constraint_catalog,
451-
CAST(n.nspnameAS sql_identifier)AS constraint_schema,
452-
CAST(CAST(n.oidAStext)||'_'|| CAST(r.oidAStext)||'_'|| CAST(a.attnumAStext)||'_not_null'AS sql_identifier)AS constraint_name,-- XXX
453-
CAST(a.attname||' IS NOT NULL'AS character_data)
454-
AS check_clause
455-
FROM pg_namespace n, pg_class r, pg_attribute a
456-
WHEREn.oid=r.relnamespace
457-
ANDr.oid=a.attrelid
458-
ANDa.attnum>0
459-
AND NOTa.attisdropped
460-
ANDa.attnotnull
461-
ANDr.relkindIN ('r','p')
462-
AND pg_has_role(r.relowner,'USAGE');
449+
SELECT current_database()::information_schema.sql_identifierAS constraint_catalog,
450+
rs.nspname::information_schema.sql_identifierAS constraint_schema,
451+
con.conname::information_schema.sql_identifierAS constraint_name,
452+
pg_catalog.format('CHECK (%s IS NOT NULL)',at.attname)::information_schema.character_dataAS check_clause
453+
FROM pg_constraint con
454+
LEFT JOIN pg_namespace rsONrs.oid=con.connamespace
455+
LEFT JOIN pg_class cONc.oid=con.conrelid
456+
LEFT JOIN pg_type tONt.oid=con.contypid
457+
LEFT JOIN pg_attribute atON (con.conrelid=at.attrelidANDcon.conkey[1]=at.attnum)
458+
WHERE pg_has_role(coalesce(c.relowner,t.typowner),'USAGE'::text)
459+
ANDcon.contype='n';
463460

464461
GRANTSELECTON check_constraints TO PUBLIC;
465462

@@ -826,6 +823,20 @@ CREATE VIEW constraint_column_usage AS
826823
ANDr.relkindIN ('r','p')
827824
AND NOTa.attisdropped
828825

826+
UNION ALL
827+
828+
/* not-null constraints*/
829+
SELECT DISTINCTnr.nspname,r.relname,r.relowner,a.attname,nc.nspname,c.conname
830+
FROM pg_namespace nr, pg_class r, pg_attribute a, pg_namespace nc, pg_constraint c
831+
WHEREnr.oid=r.relnamespace
832+
ANDr.oid=a.attrelid
833+
ANDr.oid=c.conrelid
834+
ANDa.attnum=c.conkey[1]
835+
ANDc.connamespace=nc.oid
836+
ANDc.contype='n'
837+
ANDr.relkindin ('r','p')
838+
AND nota.attisdropped
839+
829840
UNION ALL
830841

831842
/* unique/primary key/foreign key constraints*/
@@ -1828,6 +1839,7 @@ CREATE VIEW table_constraints AS
18281839
CAST(r.relnameAS sql_identifier)AS table_name,
18291840
CAST(
18301841
CASEc.contype WHEN'c' THEN'CHECK'
1842+
WHEN'n' THEN'CHECK'
18311843
WHEN'f' THEN'FOREIGN KEY'
18321844
WHEN'p' THEN'PRIMARY KEY'
18331845
WHEN'u' THEN'UNIQUE' END
@@ -1852,38 +1864,6 @@ CREATE VIEW table_constraints AS
18521864
ANDc.contype NOTIN ('t','x')-- ignore nonstandard constraints
18531865
ANDr.relkindIN ('r','p')
18541866
AND (NOT pg_is_other_temp_schema(nr.oid))
1855-
AND (pg_has_role(r.relowner,'USAGE')
1856-
-- SELECT privilege omitted, per SQL standard
1857-
OR has_table_privilege(r.oid,'INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')
1858-
OR has_any_column_privilege(r.oid,'INSERT, UPDATE, REFERENCES') )
1859-
1860-
UNION ALL
1861-
1862-
-- not-null constraints
1863-
1864-
SELECT CAST(current_database()AS sql_identifier)AS constraint_catalog,
1865-
CAST(nr.nspnameAS sql_identifier)AS constraint_schema,
1866-
CAST(CAST(nr.oidAStext)||'_'|| CAST(r.oidAStext)||'_'|| CAST(a.attnumAStext)||'_not_null'AS sql_identifier)AS constraint_name,-- XXX
1867-
CAST(current_database()AS sql_identifier)AS table_catalog,
1868-
CAST(nr.nspnameAS sql_identifier)AS table_schema,
1869-
CAST(r.relnameAS sql_identifier)AS table_name,
1870-
CAST('CHECK'AS character_data)AS constraint_type,
1871-
CAST('NO'AS yes_or_no)AS is_deferrable,
1872-
CAST('NO'AS yes_or_no)AS initially_deferred,
1873-
CAST('YES'AS yes_or_no)AS enforced,
1874-
CAST(NULLAS yes_or_no)AS nulls_distinct
1875-
1876-
FROM pg_namespace nr,
1877-
pg_class r,
1878-
pg_attribute a
1879-
1880-
WHEREnr.oid=r.relnamespace
1881-
ANDr.oid=a.attrelid
1882-
ANDa.attnotnull
1883-
ANDa.attnum>0
1884-
AND NOTa.attisdropped
1885-
ANDr.relkindIN ('r','p')
1886-
AND (NOT pg_is_other_temp_schema(nr.oid))
18871867
AND (pg_has_role(r.relowner,'USAGE')
18881868
-- SELECT privilege omitted, per SQL standard
18891869
OR has_table_privilege(r.oid,'INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')

‎src/include/catalog/catversion.h

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -57,6 +57,6 @@
5757
*/
5858

5959
/*yyyymmddN */
60-
#defineCATALOG_VERSION_NO202308251
60+
#defineCATALOG_VERSION_NO202309061
6161

6262
#endif

0 commit comments

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