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

Commit32ff269

Browse files
committed
Add more information_schema columns
- table_constraints.enforced- triggers.action_order- triggers.action_reference_old_table- triggers.action_reference_new_tableReviewed-by: Michael Paquier <michael.paquier@gmail.com>
1 parentb98a7cd commit32ff269

File tree

4 files changed

+106
-9
lines changed

4 files changed

+106
-9
lines changed

‎doc/src/sgml/information_schema.sgml

Lines changed: 17 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -5317,6 +5317,13 @@ ORDER BY c.ordinal_position;
53175317
<entry><type>yes_or_no</type></entry>
53185318
<entry><literal>YES</literal> if the constraint is deferrable and initially deferred, <literal>NO</literal> if not</entry>
53195319
</row>
5320+
<row>
5321+
<entry><literal>enforced</literal></entry>
5322+
<entry><type>yes_or_no</type></entry>
5323+
<entry>Applies to a feature not available in
5324+
<productname>PostgreSQL</productname> (currently always
5325+
<literal>YES</literal>)</entry>
5326+
</row>
53205327
</tbody>
53215328
</tgroup>
53225329
</table>
@@ -5761,7 +5768,14 @@ ORDER BY c.ordinal_position;
57615768
<row>
57625769
<entry><literal>action_order</literal></entry>
57635770
<entry><type>cardinal_number</type></entry>
5764-
<entry>Not yet implemented</entry>
5771+
<entry>
5772+
Firing order among triggers on the same table having the same
5773+
<literal>event_manipulation</literal>,
5774+
<literal>action_timing</literal>, and
5775+
<literal>action_orientation</literal>. In
5776+
<productname>PostgreSQL</productname>, triggers are fired in name
5777+
order, so this column reflects that.
5778+
</entry>
57655779
</row>
57665780

57675781
<row>
@@ -5806,13 +5820,13 @@ ORDER BY c.ordinal_position;
58065820
<row>
58075821
<entry><literal>action_reference_old_table</literal></entry>
58085822
<entry><type>sql_identifier</type></entry>
5809-
<entry>Applies to a feature not available in <productname>PostgreSQL</productname></entry>
5823+
<entry>Name of the <quote>old</quote> transition table, or null if none</entry>
58105824
</row>
58115825

58125826
<row>
58135827
<entry><literal>action_reference_new_table</literal></entry>
58145828
<entry><type>sql_identifier</type></entry>
5815-
<entry>Applies to a feature not available in <productname>PostgreSQL</productname></entry>
5829+
<entry>Name of the <quote>new</quote> transition table, or null if none</entry>
58165830
</row>
58175831

58185832
<row>

‎src/backend/catalog/information_schema.sql

Lines changed: 12 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -1783,7 +1783,8 @@ CREATE VIEW table_constraints AS
17831783
CAST(CASE WHENc.condeferrable THEN'YES' ELSE'NO' ENDAS yes_or_no)
17841784
AS is_deferrable,
17851785
CAST(CASE WHENc.condeferred THEN'YES' ELSE'NO' ENDAS yes_or_no)
1786-
AS initially_deferred
1786+
AS initially_deferred,
1787+
CAST('YES'AS yes_or_no)AS enforced
17871788

17881789
FROM pg_namespace nc,
17891790
pg_namespace nr,
@@ -1812,7 +1813,8 @@ CREATE VIEW table_constraints AS
18121813
CAST(r.relnameAS sql_identifier)AS table_name,
18131814
CAST('CHECK'AS character_data)AS constraint_type,
18141815
CAST('NO'AS yes_or_no)AS is_deferrable,
1815-
CAST('NO'AS yes_or_no)AS initially_deferred
1816+
CAST('NO'AS yes_or_no)AS initially_deferred,
1817+
CAST('YES'AS yes_or_no)AS enforced
18161818

18171819
FROM pg_namespace nr,
18181820
pg_class r,
@@ -2084,8 +2086,12 @@ CREATE VIEW triggers AS
20842086
CAST(current_database()AS sql_identifier)AS event_object_catalog,
20852087
CAST(n.nspnameAS sql_identifier)AS event_object_schema,
20862088
CAST(c.relnameAS sql_identifier)AS event_object_table,
2087-
CAST(nullAS cardinal_number)AS action_order,
2088-
-- XXX strange hacks follow
2089+
CAST(
2090+
-- To determine action order, partition by schema, table,
2091+
-- event_manipulation (INSERT/DELETE/UPDATE), ROW/STATEMENT (1),
2092+
-- BEFORE/AFTER (66), then order by trigger name
2093+
rank() OVER (PARTITION BYn.oid,c.oid,em.num,t.tgtype &1,t.tgtype &66ORDER BYt.tgname)
2094+
AS cardinal_number)AS action_order,
20892095
CAST(
20902096
CASE WHEN pg_has_role(c.relowner,'USAGE')
20912097
THEN (regexp_match(pg_get_triggerdef(t.oid), E'.{35,} WHEN\\((.+)\\) EXECUTE PROCEDURE'))[1]
@@ -2103,8 +2109,8 @@ CREATE VIEW triggers AS
21032109
-- hard-wired refs to TRIGGER_TYPE_BEFORE, TRIGGER_TYPE_INSTEAD
21042110
CASEt.tgtype &66 WHEN2 THEN'BEFORE' WHEN64 THEN'INSTEAD OF' ELSE'AFTER' END
21052111
AS character_data)AS action_timing,
2106-
CAST(nullAS sql_identifier)AS action_reference_old_table,
2107-
CAST(nullAS sql_identifier)AS action_reference_new_table,
2112+
CAST(tgoldtableAS sql_identifier)AS action_reference_old_table,
2113+
CAST(tgnewtableAS sql_identifier)AS action_reference_new_table,
21082114
CAST(nullAS sql_identifier)AS action_reference_old_row,
21092115
CAST(nullAS sql_identifier)AS action_reference_new_row,
21102116
CAST(nullAS time_stamp)AS created

‎src/test/regress/expected/triggers.out

Lines changed: 63 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -96,6 +96,24 @@ CONTEXT: SQL statement "delete from fkeys2 where fkey21 = $1 and fkey22 = $2 "
9696
update pkeys set pkey1 = 7, pkey2 = '70' where pkey1 = 10 and pkey2 = '1';
9797
NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys are deleted
9898
NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys2 are deleted
99+
SELECT trigger_name, event_manipulation, event_object_schema, event_object_table,
100+
action_order, action_condition, action_orientation, action_timing,
101+
action_reference_old_table, action_reference_new_table
102+
FROM information_schema.triggers ORDER BY 1, 2;
103+
trigger_name | event_manipulation | event_object_schema | event_object_table | action_order | action_condition | action_orientation | action_timing | action_reference_old_table | action_reference_new_table
104+
----------------------------+--------------------+---------------------+--------------------+--------------+------------------+--------------------+---------------+----------------------------+----------------------------
105+
check_fkeys2_fkey_restrict | DELETE | public | fkeys2 | 1 | | ROW | BEFORE | |
106+
check_fkeys2_fkey_restrict | UPDATE | public | fkeys2 | 1 | | ROW | BEFORE | |
107+
check_fkeys2_pkey_exist | INSERT | public | fkeys2 | 1 | | ROW | BEFORE | |
108+
check_fkeys2_pkey_exist | UPDATE | public | fkeys2 | 2 | | ROW | BEFORE | |
109+
check_fkeys_pkey2_exist | INSERT | public | fkeys | 1 | | ROW | BEFORE | |
110+
check_fkeys_pkey2_exist | UPDATE | public | fkeys | 1 | | ROW | BEFORE | |
111+
check_fkeys_pkey_exist | INSERT | public | fkeys | 2 | | ROW | BEFORE | |
112+
check_fkeys_pkey_exist | UPDATE | public | fkeys | 2 | | ROW | BEFORE | |
113+
check_pkeys_fkey_cascade | DELETE | public | pkeys | 1 | | ROW | BEFORE | |
114+
check_pkeys_fkey_cascade | UPDATE | public | pkeys | 1 | | ROW | BEFORE | |
115+
(10 rows)
116+
99117
DROP TABLE pkeys;
100118
DROP TABLE fkeys;
101119
DROP TABLE fkeys2;
@@ -347,6 +365,24 @@ CREATE TRIGGER insert_when BEFORE INSERT ON main_table
347365
FOR EACH STATEMENT WHEN (true) EXECUTE PROCEDURE trigger_func('insert_when');
348366
CREATE TRIGGER delete_when AFTER DELETE ON main_table
349367
FOR EACH STATEMENT WHEN (true) EXECUTE PROCEDURE trigger_func('delete_when');
368+
SELECT trigger_name, event_manipulation, event_object_schema, event_object_table,
369+
action_order, action_condition, action_orientation, action_timing,
370+
action_reference_old_table, action_reference_new_table
371+
FROM information_schema.triggers ORDER BY 1, 2;
372+
trigger_name | event_manipulation | event_object_schema | event_object_table | action_order | action_condition | action_orientation | action_timing | action_reference_old_table | action_reference_new_table
373+
----------------------+--------------------+---------------------+--------------------+--------------+--------------------------------+--------------------+---------------+----------------------------+----------------------------
374+
after_ins_stmt_trig | INSERT | public | main_table | 1 | | STATEMENT | AFTER | |
375+
after_upd_row_trig | UPDATE | public | main_table | 1 | | ROW | AFTER | |
376+
after_upd_stmt_trig | UPDATE | public | main_table | 1 | | STATEMENT | AFTER | |
377+
before_ins_stmt_trig | INSERT | public | main_table | 1 | | STATEMENT | BEFORE | |
378+
delete_a | DELETE | public | main_table | 1 | (old.a = 123) | ROW | AFTER | |
379+
delete_when | DELETE | public | main_table | 1 | true | STATEMENT | AFTER | |
380+
insert_a | INSERT | public | main_table | 1 | (new.a = 123) | ROW | AFTER | |
381+
insert_when | INSERT | public | main_table | 2 | true | STATEMENT | BEFORE | |
382+
modified_a | UPDATE | public | main_table | 1 | (old.a <> new.a) | ROW | BEFORE | |
383+
modified_any | UPDATE | public | main_table | 2 | (old.* IS DISTINCT FROM new.*) | ROW | BEFORE | |
384+
(10 rows)
385+
350386
INSERT INTO main_table (a) VALUES (123), (456);
351387
NOTICE: trigger_func(before_ins_stmt) called: action = INSERT, when = BEFORE, level = STATEMENT
352388
NOTICE: trigger_func(insert_when) called: action = INSERT, when = BEFORE, level = STATEMENT
@@ -1991,6 +2027,33 @@ create trigger child3_update_trig
19912027
create trigger child3_delete_trig
19922028
after delete on child3 referencing old table as old_table
19932029
for each statement execute procedure dump_delete();
2030+
SELECT trigger_name, event_manipulation, event_object_schema, event_object_table,
2031+
action_order, action_condition, action_orientation, action_timing,
2032+
action_reference_old_table, action_reference_new_table
2033+
FROM information_schema.triggers ORDER BY 1, 2;
2034+
trigger_name | event_manipulation | event_object_schema | event_object_table | action_order | action_condition | action_orientation | action_timing | action_reference_old_table | action_reference_new_table
2035+
------------------------+--------------------+---------------------+--------------------+--------------+------------------+--------------------+---------------+----------------------------+----------------------------
2036+
after_ins_stmt_trig | INSERT | public | main_table | 1 | | STATEMENT | AFTER | |
2037+
after_upd_a_b_row_trig | UPDATE | public | main_table | 1 | | ROW | AFTER | |
2038+
after_upd_b_row_trig | UPDATE | public | main_table | 2 | | ROW | AFTER | |
2039+
after_upd_b_stmt_trig | UPDATE | public | main_table | 1 | | STATEMENT | AFTER | |
2040+
after_upd_stmt_trig | UPDATE | public | main_table | 2 | | STATEMENT | AFTER | |
2041+
before_ins_stmt_trig | INSERT | public | main_table | 1 | | STATEMENT | BEFORE | |
2042+
before_upd_a_stmt_trig | UPDATE | public | main_table | 1 | | STATEMENT | BEFORE | |
2043+
child1_delete_trig | DELETE | public | child1 | 1 | | STATEMENT | AFTER | old_table |
2044+
child1_insert_trig | INSERT | public | child1 | 1 | | STATEMENT | AFTER | | new_table
2045+
child1_update_trig | UPDATE | public | child1 | 1 | | STATEMENT | AFTER | old_table | new_table
2046+
child2_delete_trig | DELETE | public | child2 | 1 | | STATEMENT | AFTER | old_table |
2047+
child2_insert_trig | INSERT | public | child2 | 1 | | STATEMENT | AFTER | | new_table
2048+
child2_update_trig | UPDATE | public | child2 | 1 | | STATEMENT | AFTER | old_table | new_table
2049+
child3_delete_trig | DELETE | public | child3 | 1 | | STATEMENT | AFTER | old_table |
2050+
child3_insert_trig | INSERT | public | child3 | 1 | | STATEMENT | AFTER | | new_table
2051+
child3_update_trig | UPDATE | public | child3 | 1 | | STATEMENT | AFTER | old_table | new_table
2052+
parent_delete_trig | DELETE | public | parent | 1 | | STATEMENT | AFTER | old_table |
2053+
parent_insert_trig | INSERT | public | parent | 1 | | STATEMENT | AFTER | | new_table
2054+
parent_update_trig | UPDATE | public | parent | 1 | | STATEMENT | AFTER | old_table | new_table
2055+
(19 rows)
2056+
19942057
-- insert directly into children sees respective child-format tuples
19952058
insert into child1 values ('AAA', 42);
19962059
NOTICE: trigger = child1_insert_trig, new table = (AAA,42)

‎src/test/regress/sql/triggers.sql

Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -92,6 +92,11 @@ delete from pkeys where pkey1 = 40 and pkey2 = '4';
9292
update pkeysset pkey1=7, pkey2='70'where pkey1=50and pkey2='5';
9393
update pkeysset pkey1=7, pkey2='70'where pkey1=10and pkey2='1';
9494

95+
SELECT trigger_name, event_manipulation, event_object_schema, event_object_table,
96+
action_order, action_condition, action_orientation, action_timing,
97+
action_reference_old_table, action_reference_new_table
98+
FROMinformation_schema.triggersORDER BY1,2;
99+
95100
DROPTABLE pkeys;
96101
DROPTABLE fkeys;
97102
DROPTABLE fkeys2;
@@ -279,6 +284,10 @@ CREATE TRIGGER insert_when BEFORE INSERT ON main_table
279284
FOR EACH STATEMENT WHEN (true) EXECUTE PROCEDURE trigger_func('insert_when');
280285
CREATETRIGGERdelete_when AFTERDELETEON main_table
281286
FOR EACH STATEMENT WHEN (true) EXECUTE PROCEDURE trigger_func('delete_when');
287+
SELECT trigger_name, event_manipulation, event_object_schema, event_object_table,
288+
action_order, action_condition, action_orientation, action_timing,
289+
action_reference_old_table, action_reference_new_table
290+
FROMinformation_schema.triggersORDER BY1,2;
282291
INSERT INTO main_table (a)VALUES (123), (456);
283292
COPY main_tableFROM stdin;
284293
123999
@@ -1472,6 +1481,11 @@ create trigger child3_delete_trig
14721481
afterdeleteon child3 referencing old tableas old_table
14731482
for each statement execute procedure dump_delete();
14741483

1484+
SELECT trigger_name, event_manipulation, event_object_schema, event_object_table,
1485+
action_order, action_condition, action_orientation, action_timing,
1486+
action_reference_old_table, action_reference_new_table
1487+
FROMinformation_schema.triggersORDER BY1,2;
1488+
14751489
-- insert directly into children sees respective child-format tuples
14761490
insert into child1values ('AAA',42);
14771491
insert into child2values ('BBB',42);

0 commit comments

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