🌐 AI搜索 & 代理 主页
Skip to content

Commit 65ab9f4

Browse files
tglsfdctglsfdc
committed
Add a couple of information functions to support direct checks on whether
a schema is our own temp schema or another backend's temp schema, and use these in place of some former kluges in information_schema. Per my proposal of yesterday.
1 parent 2a20412 commit 65ab9f4

File tree

5 files changed

+67
-16
lines changed

5 files changed

+67
-16
lines changed

‎doc/src/sgml/func.sgml‎

Lines changed: 31 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.336 2006/09/10 19:03:57 tgl Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.337 2006/09/14 22:05:05 tgl Exp $ -->
22

33
<chapter id="functions">
44
<title>Functions and Operators</title>
@@ -9232,6 +9232,18 @@ select current_date + s.a as dates from generate_series(0,14,7) as s(a);
92329232
<entry>port of the local connection</entry>
92339233
</row>
92349234

9235+
<row>
9236+
<entry><literal><function>pg_my_temp_schema</function>()</literal></entry>
9237+
<entry><type>oid</type></entry>
9238+
<entry>OID of session's temporary schema, or 0 if none</entry>
9239+
</row>
9240+
9241+
<row>
9242+
<entry><literal><function>pg_is_other_temp_schema</function>(<type>oid</type>)</literal></entry>
9243+
<entry><type>boolean</type></entry>
9244+
<entry>is schema another session's temporary schema?</entry>
9245+
</row>
9246+
92359247
<row>
92369248
<entry><literal><function>pg_postmaster_start_time</function>()</literal></entry>
92379249
<entry><type>timestamp with time zone</type></entry>
@@ -9343,6 +9355,24 @@ SET search_path TO <replaceable>schema</> <optional>, <replaceable>schema</>, ..
93439355
Unix-domain socket.
93449356
</para>
93459357

9358+
<indexterm zone="functions-info">
9359+
<primary>pg_my_temp_schema</primary>
9360+
</indexterm>
9361+
9362+
<indexterm zone="functions-info">
9363+
<primary>pg_is_other_temp_schema</primary>
9364+
</indexterm>
9365+
9366+
<para>
9367+
<function>pg_my_temp_schema</function> returns the OID of the current
9368+
session's temporary schema, or 0 if it has none (because it has not
9369+
created any temporary tables).
9370+
<function>pg_is_other_temp_schema</function> returns true if the
9371+
given OID is the OID of any other session's temporary schema.
9372+
(This can be useful, for example, to exclude other sessions' temporary
9373+
tables from a catalog display.)
9374+
</para>
9375+
93469376
<indexterm zone="functions-info">
93479377
<primary>pg_postmaster_start_time</primary>
93489378
</indexterm>

‎src/backend/catalog/information_schema.sql‎

Lines changed: 12 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.36 2006/09/05 21:08:35 tgl Exp $
7+
* $PostgreSQL: pgsql/src/backend/catalog/information_schema.sql,v 1.37 2006/09/14 22:05:06 tgl Exp $
88
*/
99

1010
/*
@@ -644,7 +644,8 @@ CREATE VIEW columns AS
644644
WHERE a.attrelid = c.oid
645645
AND a.atttypid = t.oid
646646
AND nc.oid = c.relnamespace
647-
AND (nc.nspname NOT LIKE 'pg!_temp!_%' ESCAPE '!' OR pg_catalog.pg_table_is_visible(c.oid))
647+
AND (NOT pg_is_other_temp_schema(nc.oid))
648+
648649
AND a.attnum > 0 AND NOT a.attisdropped AND c.relkind in ('r', 'v')
649650

650651
AND (pg_has_role(c.relowner, 'USAGE')
@@ -940,7 +941,7 @@ CREATE VIEW key_column_usage AS
940941
AND nc.oid = c.connamespace
941942
AND c.contype IN ('p', 'u', 'f')
942943
AND r.relkind = 'r'
943-
AND (nr.nspname NOT LIKE 'pg!_temp!_%' ESCAPE '!' OR pg_catalog.pg_table_is_visible(r.oid))
944+
AND (NOT pg_is_other_temp_schema(nr.oid))
944945
AND (pg_has_role(r.relowner, 'USAGE')
945946
OR has_table_privilege(c.oid, 'SELECT')
946947
OR has_table_privilege(c.oid, 'INSERT')
@@ -1467,7 +1468,7 @@ CREATE VIEW sequences AS
14671468
FROM pg_namespace nc, pg_class c
14681469
WHERE c.relnamespace = nc.oid
14691470
AND c.relkind = 'S'
1470-
AND (nc.nspname NOT LIKE 'pg!_temp!_%' ESCAPE '!' OR pg_catalog.pg_table_is_visible(c.oid))
1471+
AND (NOT pg_is_other_temp_schema(nc.oid))
14711472
AND (pg_has_role(c.relowner, 'USAGE')
14721473
OR has_table_privilege(c.oid, 'SELECT')
14731474
OR has_table_privilege(c.oid, 'UPDATE') );
@@ -1698,7 +1699,7 @@ CREATE VIEW table_constraints AS
16981699
WHERE nc.oid = c.connamespace AND nr.oid = r.relnamespace
16991700
AND c.conrelid = r.oid
17001701
AND r.relkind = 'r'
1701-
AND (nr.nspname NOT LIKE 'pg!_temp!_%' ESCAPE '!' OR pg_catalog.pg_table_is_visible(r.oid))
1702+
AND (NOT pg_is_other_temp_schema(nr.oid))
17021703
AND (pg_has_role(r.relowner, 'USAGE')
17031704
-- SELECT privilege omitted, per SQL standard
17041705
OR has_table_privilege(r.oid, 'INSERT')
@@ -1731,7 +1732,7 @@ CREATE VIEW table_constraints AS
17311732
AND a.attnum > 0
17321733
AND NOT a.attisdropped
17331734
AND r.relkind = 'r'
1734-
AND (nr.nspname NOT LIKE 'pg!_temp!_%' ESCAPE '!' OR pg_catalog.pg_table_is_visible(r.oid))
1735+
AND (NOT pg_is_other_temp_schema(nr.oid))
17351736
AND (pg_has_role(r.relowner, 'USAGE')
17361737
OR has_table_privilege(r.oid, 'SELECT')
17371738
OR has_table_privilege(r.oid, 'INSERT')
@@ -1806,7 +1807,7 @@ CREATE VIEW tables AS
18061807
CAST(c.relname AS sql_identifier) AS table_name,
18071808

18081809
CAST(
1809-
CASE WHEN nc.nspname LIKE 'pg!_temp!_%' ESCAPE '!' THEN 'LOCAL TEMPORARY'
1810+
CASE WHEN nc.oid = pg_my_temp_schema() THEN 'LOCAL TEMPORARY'
18101811
WHEN c.relkind = 'r' THEN 'BASE TABLE'
18111812
WHEN c.relkind = 'v' THEN 'VIEW'
18121813
ELSE null END
@@ -1823,15 +1824,15 @@ CREATE VIEW tables AS
18231824
THEN 'YES' ELSE 'NO' END AS character_data) AS is_insertable_into,
18241825
CAST('NO' AS character_data) AS is_typed,
18251826
CAST(
1826-
CASE WHEN nc.nspname LIKE 'pg!_temp!_%' ESCAPE '!' THEN 'PRESERVE'
1827+
CASE WHEN nc.oid = pg_my_temp_schema() THEN 'PRESERVE' -- FIXME
18271828
ELSE null END
18281829
AS character_data) AS commit_action
18291830

18301831
FROM pg_namespace nc, pg_class c
18311832

18321833
WHERE c.relnamespace = nc.oid
18331834
AND c.relkind IN ('r', 'v')
1834-
AND (nc.nspname NOT LIKE 'pg!_temp!_%' ESCAPE '!' OR pg_catalog.pg_table_is_visible(c.oid))
1835+
AND (NOT pg_is_other_temp_schema(nc.oid))
18351836
AND (pg_has_role(c.relowner, 'USAGE')
18361837
OR has_table_privilege(c.oid, 'SELECT')
18371838
OR has_table_privilege(c.oid, 'INSERT')
@@ -1952,7 +1953,7 @@ CREATE VIEW triggers AS
19521953
AND c.oid = t.tgrelid
19531954
AND t.tgtype & em.num <> 0
19541955
AND NOT t.tgisconstraint
1955-
AND (n.nspname NOT LIKE 'pg!_temp!_%' ESCAPE '!' OR pg_catalog.pg_table_is_visible(c.oid))
1956+
AND (NOT pg_is_other_temp_schema(n.oid))
19561957
AND (pg_has_role(c.relowner, 'USAGE')
19571958
-- SELECT privilege omitted, per SQL standard
19581959
OR has_table_privilege(c.oid, 'INSERT')
@@ -2150,7 +2151,7 @@ CREATE VIEW views AS
21502151

21512152
WHERE c.relnamespace = nc.oid
21522153
AND c.relkind = 'v'
2153-
AND (nc.nspname NOT LIKE 'pg!_temp!_%' ESCAPE '!' OR pg_catalog.pg_table_is_visible(c.oid))
2154+
AND (NOT pg_is_other_temp_schema(nc.oid))
21542155
AND (pg_has_role(c.relowner, 'USAGE')
21552156
OR has_table_privilege(c.oid, 'SELECT')
21562157
OR has_table_privilege(c.oid, 'INSERT')

‎src/backend/catalog/namespace.c‎

Lines changed: 17 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -13,7 +13,7 @@
1313
* Portions Copyright (c) 1994, Regents of the University of California
1414
*
1515
* IDENTIFICATION
16-
* $PostgreSQL: pgsql/src/backend/catalog/namespace.c,v 1.86 2006/07/14 14:52:17 momjian Exp $
16+
* $PostgreSQL: pgsql/src/backend/catalog/namespace.c,v 1.87 2006/09/14 22:05:06 tgl Exp $
1717
*
1818
*-------------------------------------------------------------------------
1919
*/
@@ -143,6 +143,8 @@ Datum pg_function_is_visible(PG_FUNCTION_ARGS);
143143
Datum pg_operator_is_visible(PG_FUNCTION_ARGS);
144144
Datum pg_opclass_is_visible(PG_FUNCTION_ARGS);
145145
Datum pg_conversion_is_visible(PG_FUNCTION_ARGS);
146+
Datum pg_my_temp_schema(PG_FUNCTION_ARGS);
147+
Datum pg_is_other_temp_schema(PG_FUNCTION_ARGS);
146148

147149

148150
/*
@@ -2035,3 +2037,17 @@ pg_conversion_is_visible(PG_FUNCTION_ARGS)
20352037

20362038
PG_RETURN_BOOL(ConversionIsVisible(oid));
20372039
}
2040+
2041+
Datum
2042+
pg_my_temp_schema(PG_FUNCTION_ARGS)
2043+
{
2044+
PG_RETURN_OID(myTempNamespace);
2045+
}
2046+
2047+
Datum
2048+
pg_is_other_temp_schema(PG_FUNCTION_ARGS)
2049+
{
2050+
Oid oid = PG_GETARG_OID(0);
2051+
2052+
PG_RETURN_BOOL(isOtherTempNamespace(oid));
2053+
}

‎src/include/catalog/catversion.h‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -37,7 +37,7 @@
3737
* Portions Copyright (c) 1996-2006, PostgreSQL Global Development Group
3838
* Portions Copyright (c) 1994, Regents of the University of California
3939
*
40-
* $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.355 2006/09/10 00:29:34 tgl Exp $
40+
* $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.356 2006/09/14 22:05:06 tgl Exp $
4141
*
4242
*-------------------------------------------------------------------------
4343
*/
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/* yyyymmddN */
56-
#define CATALOG_VERSION_NO 200609091
56+
#define CATALOG_VERSION_NO 200609141
5757

5858
#endif

‎src/include/catalog/pg_proc.h‎

Lines changed: 5 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1996-2006, PostgreSQL Global Development Group
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
10-
* $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.423 2006/09/10 00:29:34 tgl Exp $
10+
* $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.424 2006/09/14 22:05:06 tgl Exp $
1111
*
1212
* NOTES
1313
* The script catalog/genbki.sh reads this file and generates .bki
@@ -3096,6 +3096,10 @@ DATA(insert OID = 2083 ( pg_opclass_is_visible PGNSP PGUID 12 f f t f s 1 16 "
30963096
DESCR("is opclass visible in search path?");
30973097
DATA(insert OID = 2093 ( pg_conversion_is_visible PGNSP PGUID 12 f f t f s 1 16 "26" _null_ _null_ _null_ pg_conversion_is_visible - _null_ ));
30983098
DESCR("is conversion visible in search path?");
3099+
DATA(insert OID = 2854 ( pg_my_temp_schema PGNSP PGUID 12 f f t f s 0 26 "" _null_ _null_ _null_ pg_my_temp_schema - _null_ ));
3100+
DESCR("get OID of current session's temp schema, if any");
3101+
DATA(insert OID = 2855 ( pg_is_other_temp_schema PGNSP PGUID 12 f f t f s 1 16 "26" _null_ _null_ _null_ pg_is_other_temp_schema - _null_ ));
3102+
DESCR("is schema another session's temp schema?");
30993103

31003104
DATA(insert OID = 2171 ( pg_cancel_backend PGNSP PGUID 12 f f t f v 1 16 "23" _null_ _null_ _null_ pg_cancel_backend - _null_ ));
31013105
DESCR("Cancel a server process' current query");

0 commit comments

Comments
 (0)