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

Commit2ec993a

Browse files
committed
Support triggers on views.
This patch adds the SQL-standard concept of an INSTEAD OF trigger, whichis fired instead of performing a physical insert/update/delete. Thetrigger function is passed the entire old and/or new rows of the view,and must figure out what to do to the underlying tables to implementthe update. So this feature can be used to implement updatable viewsusing trigger programming style rather than rule hacking.In passing, this patch corrects the names of some columns in theinformation_schema.triggers view. It seems the SQL committee renamedthem somewhere between SQL:99 and SQL:2003.Dean Rasheed, reviewed by Bernd Helmle; some additional hacking by me.
1 parentf7b15b5 commit2ec993a

File tree

47 files changed

+2816
-760
lines changed

Some content is hidden

Large Commits have some content hidden by default. Use the searchbox below for content that may be hidden.

47 files changed

+2816
-760
lines changed

‎doc/src/sgml/catalogs.sgml

Lines changed: 5 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -4243,7 +4243,7 @@
42434243

42444244
<para>
42454245
The catalog <structname>pg_seclabel</structname> stores security
4246-
labels on database objects. See the
4246+
labels on database objects. See the
42474247
<xref linkend="sql-security-label"> statement.
42484248
</para>
42494249

@@ -4795,7 +4795,8 @@
47954795
</indexterm>
47964796

47974797
<para>
4798-
The catalog <structname>pg_trigger</structname> stores triggers on tables.
4798+
The catalog <structname>pg_trigger</structname> stores triggers on tables
4799+
and views.
47994800
See <xref linkend="sql-createtrigger">
48004801
for more information.
48014802
</para>
@@ -4839,7 +4840,7 @@
48394840
<entry><structfield>tgtype</structfield></entry>
48404841
<entry><type>int2</type></entry>
48414842
<entry></entry>
4842-
<entry>Bit mask identifying trigger conditions</entry>
4843+
<entry>Bit mask identifying triggerfiringconditions</entry>
48434844
</row>
48444845

48454846
<row>
@@ -4956,7 +4957,7 @@
49564957
<note>
49574958
<para>
49584959
<literal>pg_class.relhastriggers</literal>
4959-
must be true if atable has any triggers in this catalog.
4960+
must be true if arelation has any triggers in this catalog.
49604961
</para>
49614962
</note>
49624963

‎doc/src/sgml/information_schema.sgml

Lines changed: 45 additions & 16 deletions
Original file line numberDiff line numberDiff line change
@@ -4885,8 +4885,8 @@ ORDER BY c.ordinal_position;
48854885

48864886
<para>
48874887
The view <literal>triggers</literal> contains all triggers defined
4888-
in the current database on tables that the current user owns or has
4889-
some non-SELECT privilege on.
4888+
in the current database on tablesand viewsthat the current user owns
4889+
or hassome non-SELECT privilege on.
48904890
</para>
48914891

48924892
<table>
@@ -4987,34 +4987,34 @@ ORDER BY c.ordinal_position;
49874987
</row>
49884988

49894989
<row>
4990-
<entry><literal>condition_timing</literal></entry>
4990+
<entry><literal>action_timing</literal></entry>
49914991
<entry><type>character_data</type></entry>
49924992
<entry>
4993-
Time at which the trigger fires (<literal>BEFORE</literal> or
4994-
<literal>AFTER</literal>)
4993+
Time at which the trigger fires (<literal>BEFORE</literal>,
4994+
<literal>AFTER</literal>, or <literal>INSTEAD OF</literal>)
49954995
</entry>
49964996
</row>
49974997

49984998
<row>
4999-
<entry><literal>condition_reference_old_table</literal></entry>
4999+
<entry><literal>action_reference_old_table</literal></entry>
50005000
<entry><type>sql_identifier</type></entry>
50015001
<entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
50025002
</row>
50035003

50045004
<row>
5005-
<entry><literal>condition_reference_new_table</literal></entry>
5005+
<entry><literal>action_reference_new_table</literal></entry>
50065006
<entry><type>sql_identifier</type></entry>
50075007
<entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
50085008
</row>
50095009

50105010
<row>
5011-
<entry><literal>condition_reference_old_row</literal></entry>
5011+
<entry><literal>action_reference_old_row</literal></entry>
50125012
<entry><type>sql_identifier</type></entry>
50135013
<entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
50145014
</row>
50155015

50165016
<row>
5017-
<entry><literal>condition_reference_new_row</literal></entry>
5017+
<entry><literal>action_reference_new_row</literal></entry>
50185018
<entry><type>sql_identifier</type></entry>
50195019
<entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
50205020
</row>
@@ -5032,9 +5032,9 @@ ORDER BY c.ordinal_position;
50325032
Triggers in <productname>PostgreSQL</productname> have two
50335033
incompatibilities with the SQL standard that affect the
50345034
representation in the information schema. First, trigger names are
5035-
local tothe table in <productname>PostgreSQL</productname>, rather
5035+
local toeach table in <productname>PostgreSQL</productname>, rather
50365036
than being independent schema objects. Therefore there can be duplicate
5037-
trigger names defined in one schema,as long as they belong to
5037+
trigger names defined in one schema,so long as they belong to
50385038
different tables. (<literal>trigger_catalog</literal> and
50395039
<literal>trigger_schema</literal> are really the values pertaining
50405040
to the table that the trigger is defined on.) Second, triggers can
@@ -5045,14 +5045,34 @@ ORDER BY c.ordinal_position;
50455045
multiple rows in the information schema, one for each type of
50465046
event. As a consequence of these two issues, the primary key of
50475047
the view <literal>triggers</literal> is really
5048-
<literal>(trigger_catalog, trigger_schema,trigger_name,
5049-
event_object_table, event_manipulation)</literal> instead of
5048+
<literal>(trigger_catalog, trigger_schema,event_object_table,
5049+
trigger_name, event_manipulation)</literal> instead of
50505050
<literal>(trigger_catalog, trigger_schema, trigger_name)</literal>,
50515051
which is what the SQL standard specifies. Nonetheless, if you
50525052
define your triggers in a manner that conforms with the SQL
50535053
standard (trigger names unique in the schema and only one event
50545054
type per trigger), this will not affect you.
50555055
</para>
5056+
5057+
<note>
5058+
<para>
5059+
Prior to <productname>PostgreSQL</> 9.1, this view's columns
5060+
<structfield>action_timing</structfield>,
5061+
<structfield>action_reference_old_table</structfield>,
5062+
<structfield>action_reference_new_table</structfield>,
5063+
<structfield>action_reference_old_row</structfield>, and
5064+
<structfield>action_reference_new_row</structfield>
5065+
were named
5066+
<structfield>condition_timing</structfield>,
5067+
<structfield>condition_reference_old_table</structfield>,
5068+
<structfield>condition_reference_new_table</structfield>,
5069+
<structfield>condition_reference_old_row</structfield>, and
5070+
<structfield>condition_reference_new_row</structfield>
5071+
respectively.
5072+
That was how they were named in the SQL:1999 standard.
5073+
The new naming conforms to SQL:2003 and later.
5074+
</para>
5075+
</note>
50565076
</sect1>
50575077

50585078
<sect1 id="infoschema-usage-privileges">
@@ -5562,19 +5582,28 @@ ORDER BY c.ordinal_position;
55625582
<row>
55635583
<entry><literal>is_trigger_updatable</literal></entry>
55645584
<entry><type>yes_or_no</type></entry>
5565-
<entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
5585+
<entry>
5586+
<literal>YES</> if the view has an <literal>INSTEAD OF</>
5587+
<command>UPDATE</> trigger defined on it, <literal>NO</> if not
5588+
</entry>
55665589
</row>
55675590

55685591
<row>
55695592
<entry><literal>is_trigger_deletable</literal></entry>
55705593
<entry><type>yes_or_no</type></entry>
5571-
<entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
5594+
<entry>
5595+
<literal>YES</> if the view has an <literal>INSTEAD OF</>
5596+
<command>DELETE</> trigger defined on it, <literal>NO</> if not
5597+
</entry>
55725598
</row>
55735599

55745600
<row>
55755601
<entry><literal>is_trigger_insertable_into</literal></entry>
55765602
<entry><type>yes_or_no</type></entry>
5577-
<entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
5603+
<entry>
5604+
<literal>YES</> if the view has an <literal>INSTEAD OF</>
5605+
<command>INSERT</> trigger defined on it, <literal>NO</> if not
5606+
</entry>
55785607
</row>
55795608
</tbody>
55805609
</tgroup>

‎doc/src/sgml/plperl.sgml

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -999,7 +999,9 @@ $$ LANGUAGE plperl;
999999
<term><literal>$_TD-&gt;{when}</literal></term>
10001000
<listitem>
10011001
<para>
1002-
When the trigger was called: <literal>BEFORE</literal>, <literal>AFTER</literal>, or <literal>UNKNOWN</literal>
1002+
When the trigger was called: <literal>BEFORE</literal>,
1003+
<literal>AFTER</literal>, <literal>INSTEAD OF</literal>, or
1004+
<literal>UNKNOWN</literal>
10031005
</para>
10041006
</listitem>
10051007
</varlistentry>

‎doc/src/sgml/plpgsql.sgml

Lines changed: 101 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -3112,9 +3112,9 @@ RAISE unique_violation USING MESSAGE = 'Duplicate user ID: ' || user_id;
31123112
<term><varname>TG_WHEN</varname></term>
31133113
<listitem>
31143114
<para>
3115-
Data type <type>text</type>; a string of either
3116-
<literal>BEFORE</literal> or<literal>AFTER</literal>
3117-
depending on the trigger's definition.
3115+
Data type <type>text</type>; a string of
3116+
<literal>BEFORE</literal>,<literal>AFTER</literal>, or
3117+
<literal>INSTEAD OF</literal>,depending on the trigger's definition.
31183118
</para>
31193119
</listitem>
31203120
</varlistentry>
@@ -3234,8 +3234,25 @@ RAISE unique_violation USING MESSAGE = 'Duplicate user ID: ' || user_id;
32343234
effect, but it has to be nonnull to allow the trigger action to
32353235
proceed. Note that <varname>NEW</varname> is null
32363236
in <command>DELETE</command> triggers, so returning that is
3237-
usually not sensible. A useful idiom in <command>DELETE</command>
3238-
triggers might be to return <varname>OLD</varname>.
3237+
usually not sensible. The usual idiom in <command>DELETE</command>
3238+
triggers is to return <varname>OLD</varname>.
3239+
</para>
3240+
3241+
<para>
3242+
<literal>INSTEAD OF</> triggers (which are always row-level triggers,
3243+
and may only be used on views) can return null to signal that they did
3244+
not perform any updates, and that the rest of the operation for this
3245+
row should be skipped (i.e., subsequent triggers are not fired, and the
3246+
row is not counted in the rows-affected status for the surrounding
3247+
<command>INSERT</>/<command>UPDATE</>/<command>DELETE</>).
3248+
Otherwise a nonnull value should be returned, to signal
3249+
that the trigger performed the requested operation. For
3250+
<command>INSERT</> and <command>UPDATE</> operations, the return value
3251+
should be <varname>NEW</>, which the trigger function may modify to
3252+
support <command>INSERT RETURNING</> and <command>UPDATE RETURNING</>
3253+
(this will also affect the row value passed to any subsequent triggers).
3254+
For <command>DELETE</> operations, the return value should be
3255+
<varname>OLD</>.
32393256
</para>
32403257

32413258
<para>
@@ -3354,6 +3371,85 @@ AFTER INSERT OR UPDATE OR DELETE ON emp
33543371
</programlisting>
33553372
</example>
33563373

3374+
<para>
3375+
A variation of the previous example uses a view joining the main table
3376+
to the audit table, to show when each entry was last modified. This
3377+
approach still records the full audit trail of changes to the table,
3378+
but also presents a simplified view of the audit trail, showing just
3379+
the last modified timestamp derived from the audit trail for each entry.
3380+
<xref linkend="plpgsql-view-trigger-audit-example"> shows an example
3381+
of an audit trigger on a view in <application>PL/pgSQL</application>.
3382+
</para>
3383+
3384+
<example id="plpgsql-view-trigger-audit-example">
3385+
<title>A <application>PL/pgSQL</application> View Trigger Procedure For Auditing</title>
3386+
3387+
<para>
3388+
This example uses a trigger on the view to make it updatable, and
3389+
ensure that any insert, update or delete of a row in the view is
3390+
recorded (i.e., audited) in the emp_audit table. The current time
3391+
and user name are recorded, together with the type of operation
3392+
performed, and the view displays the last modified time of each row.
3393+
</para>
3394+
3395+
<programlisting>
3396+
CREATE TABLE emp (
3397+
empname text PRIMARY KEY,
3398+
salary integer
3399+
);
3400+
3401+
CREATE TABLE emp_audit(
3402+
operation char(1) NOT NULL,
3403+
userid text NOT NULL,
3404+
empname text NOT NULL,
3405+
salary integer,
3406+
stamp timestamp NOT NULL
3407+
);
3408+
3409+
CREATE VIEW emp_view AS
3410+
SELECT e.empname,
3411+
e.salary,
3412+
max(ea.stamp) AS last_updated
3413+
FROM emp e
3414+
LEFT JOIN emp_audit ea ON ea.empname = e.empname
3415+
GROUP BY 1, 2;
3416+
3417+
CREATE OR REPLACE FUNCTION update_emp_view() RETURNS TRIGGER AS $$
3418+
BEGIN
3419+
--
3420+
-- Perform the required operation on emp, and create a row in emp_audit
3421+
-- to reflect the change made to emp.
3422+
--
3423+
IF (TG_OP = 'DELETE') THEN
3424+
DELETE FROM emp WHERE empname = OLD.empname;
3425+
IF NOT FOUND THEN RETURN NULL; END IF;
3426+
3427+
OLD.last_updated = now();
3428+
INSERT INTO emp_audit VALUES('D', user, OLD.*);
3429+
RETURN OLD;
3430+
ELSIF (TG_OP = 'UPDATE') THEN
3431+
UPDATE emp SET salary = NEW.salary WHERE empname = OLD.empname;
3432+
IF NOT FOUND THEN RETURN NULL; END IF;
3433+
3434+
NEW.last_updated = now();
3435+
INSERT INTO emp_audit VALUES('U', user, NEW.*);
3436+
RETURN NEW;
3437+
ELSIF (TG_OP = 'INSERT') THEN
3438+
INSERT INTO emp VALUES(NEW.empname, NEW.salary);
3439+
3440+
NEW.last_updated = now();
3441+
INSERT INTO emp_audit VALUES('I', user, NEW.*);
3442+
RETURN NEW;
3443+
END IF;
3444+
END;
3445+
$$ LANGUAGE plpgsql;
3446+
3447+
CREATE TRIGGER emp_audit
3448+
INSTEAD OF INSERT OR UPDATE OR DELETE ON emp_view
3449+
FOR EACH ROW EXECUTE PROCEDURE update_emp_view();
3450+
</programlisting>
3451+
</example>
3452+
33573453
<para>
33583454
One use of triggers is to maintain a summary table
33593455
of another table. The resulting summary can be used in place of the

‎doc/src/sgml/plpython.sgml

Lines changed: 10 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -609,7 +609,7 @@ CREATE TYPE greeting AS (
609609
who text
610610
);
611611
</programlisting>
612-
612+
613613
A set result can be returned from a:
614614

615615
<variablelist>
@@ -751,8 +751,7 @@ $$ LANGUAGE plpythonu;
751751
<para>
752752
contains the event as a string:
753753
<literal>INSERT</>, <literal>UPDATE</>,
754-
<literal>DELETE</>, <literal>TRUNCATE</>,
755-
or <literal>UNKNOWN</>.
754+
<literal>DELETE</>, or <literal>TRUNCATE</>.
756755
</para>
757756
</listitem>
758757
</varlistentry>
@@ -761,8 +760,8 @@ $$ LANGUAGE plpythonu;
761760
<term><literal>TD["when"]</></term>
762761
<listitem>
763762
<para>
764-
contains one of <literal>BEFORE</>, <literal>AFTER</>,
765-
or<literal>UNKNOWN</>.
763+
contains one of <literal>BEFORE</>, <literal>AFTER</>, or
764+
<literal>INSTEAD OF</>.
766765
</para>
767766
</listitem>
768767
</varlistentry>
@@ -771,8 +770,7 @@ $$ LANGUAGE plpythonu;
771770
<term><literal>TD["level"]</></term>
772771
<listitem>
773772
<para>
774-
contains one of <literal>ROW</>,
775-
<literal>STATEMENT</>, or <literal>UNKNOWN</>.
773+
contains <literal>ROW</> or <literal>STATEMENT</>.
776774
</para>
777775
</listitem>
778776
</varlistentry>
@@ -838,12 +836,14 @@ $$ LANGUAGE plpythonu;
838836
</para>
839837

840838
<para>
841-
If <literal>TD["when"]</literal> is <literal>BEFORE</> and
839+
If <literal>TD["when"]</literal> is <literal>BEFORE</> or
840+
<literal>INSTEAD OF</> and
842841
<literal>TD["level"]</literal> is <literal>ROW</>, you can
843842
return <literal>None</literal> or <literal>"OK"</literal> from the
844843
Python function to indicate the row is unmodified,
845-
<literal>"SKIP"</> to abort the event, or <literal>"MODIFY"</> to
846-
indicate you've modified the row.
844+
<literal>"SKIP"</> to abort the event, or if <literal>TD["event"]</>
845+
is <command>INSERT</> or <command>UPDATE</> you can return
846+
<literal>"MODIFY"</> to indicate you've modified the new row.
847847
Otherwise the return value is ignored.
848848
</para>
849849
</sect1>

‎doc/src/sgml/pltcl.sgml

Lines changed: 10 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -591,8 +591,8 @@ SELECT 'doesn''t' AS ret
591591
<term><varname>$TG_when</varname></term>
592592
<listitem>
593593
<para>
594-
The string <literal>BEFORE</> or<literal>AFTER</> depending on the
595-
type of trigger event.
594+
The string <literal>BEFORE</>,<literal>AFTER</>, or
595+
<literal>INSTEAD OF</>, depending on thetype of trigger event.
596596
</para>
597597
</listitem>
598598
</varlistentry>
@@ -665,10 +665,14 @@ SELECT 'doesn''t' AS ret
665665
the operation (<command>INSERT</>/<command>UPDATE</>/<command>DELETE</>) that fired the trigger will proceed
666666
normally. <literal>SKIP</> tells the trigger manager to silently suppress
667667
the operation for this row. If a list is returned, it tells PL/Tcl to
668-
return a modified row to the trigger manager that will be inserted
669-
instead of the one given in <varname>$NEW</>. (This works for <command>INSERT</> and <command>UPDATE</>
670-
only.) Needless to say that all this is only meaningful when the trigger
671-
is <literal>BEFORE</> and <command>FOR EACH ROW</>; otherwise the return value is ignored.
668+
return a modified row to the trigger manager. This is only meaningful
669+
for row-level <literal>BEFORE</> <command>INSERT</> or <command>UPDATE</>
670+
triggers for which the modified row will be inserted instead of the one
671+
given in <varname>$NEW</>; or for row-level <literal>INSTEAD OF</>
672+
<command>INSERT</> or <command>UPDATE</> triggers where the returned row
673+
is used to support <command>INSERT RETURNING</> and
674+
<command>UPDATE RETURNING</> commands. The return value is ignored for
675+
other types of triggers.
672676
</para>
673677

674678
<para>

0 commit comments

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