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

Commita00431b

Browse files
committed
"Information_schema" changes
- Add domain check constraints to "check_constraints" view- Create "domains" view- Create "domain_constraints" view--Rod Taylor <rbt@rbt.ca>
1 parent54ca7a7 commita00431b

File tree

1 file changed

+132
-4
lines changed

1 file changed

+132
-4
lines changed

‎src/backend/catalog/information_schema.sql

Lines changed: 132 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -4,7 +4,7 @@
44
*
55
* Copyright 2002, PostgreSQL Global Development Group
66
*
7-
* $Id: information_schema.sql,v 1.3 2003/01/15 23:37:27 petere Exp $
7+
* $Id: information_schema.sql,v 1.4 2003/03/20 05:06:55 momjian Exp $
88
*/
99

1010

@@ -76,9 +76,13 @@ CREATE VIEW check_constraints AS
7676
CAST(rs.nspnameAS sql_identifier)AS constraint_schema,
7777
CAST(con.connameAS sql_identifier)AS constraint_name,
7878
CAST(con.consrcAS character_data)AS check_clause
79-
FROM pg_namespace rs, pg_class c, pg_constraint con, pg_user u
80-
WHERErs.oid=c.relnamespaceANDc.oid=con.conrelid
81-
ANDc.relowner=u.usesysidANDu.usename=current_user
79+
FROM pg_namespace rs, pg_constraint con
80+
left outer join pg_class con (c.oid=con.conrelid)
81+
left outer join pg_type ton (t.oid=con.contypid),
82+
pg_user u
83+
WHERErs.oid=con.connamespace
84+
ANDu.usesysidIN (c.relowner,t.typowner)
85+
ANDu.usename=current_user
8286
ANDcon.contype='c';
8387

8488
GRANTSELECTON check_constraints TO PUBLIC;
@@ -236,6 +240,130 @@ CREATE VIEW columns AS
236240
GRANTSELECTON columns TO PUBLIC;
237241

238242

243+
/*
244+
* 20.24
245+
* DOMAIN_CONSTRAINTS view
246+
*/
247+
248+
CREATEVIEWdomain_constraintsAS
249+
SELECT CAST(current_database()AS sql_identifier)AS constraint_catalog,
250+
CAST(rs.nspnameAS sql_identifier)AS constraint_schema,
251+
CAST(con.connameAS sql_identifier)AS constraint_name,
252+
CAST(current_database()AS sql_identifier)AS domain_catalog,
253+
CAST(n.nspnameAS sql_identifier)AS domain_schema,
254+
CAST(t.typnameAS sql_identifier)AS domain_name,
255+
CAST(CASE WHEN condeferrable THEN'YES' ELSE'NO' END
256+
AS character_data)AS is_deferrable,
257+
CAST(CASE WHEN condeferred THEN'YES' ELSE'NO' END
258+
AS character_data)AS initially_deferred
259+
FROM pg_namespace rs, pg_namespace n, pg_constraint con, pg_type t, pg_user u
260+
WHERErs.oid=con.connamespace
261+
ANDn.oid=t.typnamespace
262+
ANDu.usesysid=t.typowner
263+
ANDu.usename=current_user
264+
ANDt.oid=con.contypid;
265+
266+
GRANTSELECTON domain_constraints TO PUBLIC;
267+
268+
269+
/*
270+
* 20.26
271+
* DOMAINS view
272+
*/
273+
274+
CREATEVIEWdomainsAS
275+
SELECT CAST(current_database()AS sql_identifier)AS domain_catalog,
276+
CAST(rs.nspnameAS sql_identifier)AS domain_schema,
277+
CAST(t.typnameAS sql_identifier)AS domain_name,
278+
CAST(format_type(t.typbasetype,null)AS character_data)
279+
AS data_type,
280+
281+
CAST(
282+
CASE WHENt.typbasetypeIN (25,1042,1043,1560,1562)ANDt.typtypmod<>-1
283+
THENt.typtypmod-4
284+
ELSEnull END
285+
AS cardinal_number)
286+
AS character_maximum_length,
287+
288+
CAST(
289+
CASE WHENt.typbasetypeIN (25,1042,1043) THEN2^30 ELSEnull END
290+
AS cardinal_number)
291+
AS character_octet_length,
292+
CAST(nullAS sql_identifier)AS character_set_catalog,
293+
CAST(nullAS sql_identifier)AS character_set_schema,
294+
CAST(nullAS sql_identifier)AS character_set_name,
295+
296+
CAST(nullAS sql_identifier)AS collation_catalog,
297+
CAST(nullAS sql_identifier)AS collation_schema,
298+
CAST(nullAS sql_identifier)AS collation_name,
299+
300+
CAST(
301+
CASE WHENt.typbasetypeIN (1700) THEN ((t.typtypmod-4)>>16) &65535 ELSEnull END
302+
AS cardinal_number)
303+
AS numeric_precision,
304+
305+
CAST(
306+
CASE WHENt.typbasetypeIN (1700) THEN10 ELSEnull END
307+
AS cardinal_number)
308+
AS numeric_precision_radix,
309+
310+
CAST(
311+
CASE WHENt.typbasetypeIN (1700) THEN (t.typtypmod-4) &65535 ELSEnull END
312+
AS cardinal_number)
313+
AS numeric_scale,
314+
315+
CAST(
316+
CASE WHENt.typbasetypeIN (1083,1114,1184,1266)
317+
THEN (CASE WHENt.typtypmod<>-1 THENt.typtypmod ELSEnull END)
318+
WHENt.typbasetypeIN (1186)
319+
THEN (CASE WHENt.typtypmod<>-1 THENt.typtypmod &65535 ELSEnull END)
320+
ELSEnull END
321+
AS cardinal_number)
322+
AS datetime_precision,
323+
324+
CAST(nullAS character_data)AS interval_type,-- XXX
325+
CAST(nullAS character_data)AS interval_precision,-- XXX
326+
327+
CAST(typdefaultAS character_data)AS domain_default,
328+
329+
CAST(CASE WHENt.typbasetype=0 THEN current_database() ELSEnull END
330+
AS sql_identifier)AS udt_catalog,
331+
CAST(CASE WHENt.typbasetype=0 THENrs.nspname ELSEnull END
332+
AS sql_identifier)AS udt_schema,
333+
CAST(CASE WHENt.typbasetype=0 THENt.typname ELSEnull END
334+
AS sql_identifier)AS udt_name,
335+
336+
CAST(nullAS sql_identifier)AS scope_catalog,
337+
CAST(nullAS sql_identifier)AS scope_schema,
338+
CAST(nullAS sql_identifier)AS scope_name,
339+
340+
CAST(nullAS cardinal_number)AS maximum_cardinality,
341+
CAST(nullAS sql_identifier)AS dtd_identifier
342+
343+
FROM pg_namespace rs,
344+
pg_type t,
345+
pg_user u
346+
347+
WHERErs.oid=t.typnamespace
348+
ANDt.typtype='d'
349+
ANDt.typowner=u.usesysid
350+
AND (u.usename=CURRENT_USER
351+
OR EXISTS (SELECT1
352+
FROM pg_userAS u2
353+
WHERErs.nspowner=u2.usesysid
354+
ANDu2.usename=CURRENT_USER)
355+
OR EXISTS (SELECT1
356+
FROM pg_userAS u3,
357+
pg_attributeAS a3,
358+
pg_classAS c3
359+
WHEREu3.usesysid=c3.relowner
360+
ANDa3.attrelid=c3.oid
361+
ANDa3.atttypid=t.oid));
362+
363+
364+
GRANTSELECTON domains TO PUBLIC;
365+
366+
239367
/*
240368
* 20.35
241369
* REFERENTIAL_CONSTRAINTS view

0 commit comments

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