<!-- $PostgreSQL: pgsql/doc/src/sgml/information_schema.sgml,v 1.43 2009/12/30 22:48:10 petere Exp $ -->

<!-- $PostgreSQL: pgsql/doc/src/sgml/information_schema.sgml,v 1.44 2009/12/31 14:41:23 petere Exp $ -->

<chapter id="information-schema">

<title>The Information Schema</title>

</table>

</sect1>

<sect1 id="infoschema-triggered-update-columns">

<title><literal>triggered_update_columns</literal></title>

<para>

For triggers in the current database that specify a column list

(like <literal>UPDATE OF column1, column2</literal>), the

view <literal>triggered_update_columns</literal> identifies these

columns. Triggers that do not specify a column list are not

included in this view. Only those columns are shown that the

current user owns or has some non-SELECT privilege on.

</para>

<table>

<title><literal>triggered_update_columns</literal> Columns</title>

<tgroup cols="3">

<thead>

<row>

<entry>Name</entry>

<entry>Data Type</entry>

<entry>Description</entry>

</row>

</thead>

<tbody>

<row>

<entry><literal>trigger_catalog</literal></entry>

<entry><type>sql_identifier</type></entry>

<entry>Name of the database that contains the trigger (always the current database)</entry>

</row>

<row>

<entry><literal>trigger_schema</literal></entry>

<entry><type>sql_identifier</type></entry>

<entry>Name of the schema that contains the trigger</entry>

</row>

<row>

<entry><literal>trigger_name</literal></entry>

<entry><type>sql_identifier</type></entry>

<entry>Name of the trigger</entry>

</row>

<row>

<entry><literal>event_object_catalog</literal></entry>

<entry><type>sql_identifier</type></entry>

<entry>

Name of the database that contains the table that the trigger

is defined on (always the current database)

</entry>

</row>

<row>

<entry><literal>event_object_schema</literal></entry>

<entry><type>sql_identifier</type></entry>

<entry>Name of the schema that contains the table that the trigger is defined on</entry>

</row>

<row>

<entry><literal>event_object_table</literal></entry>

<entry><type>sql_identifier</type></entry>

<entry>Name of the table that the trigger is defined on</entry>

</row>

<row>

<entry><literal>event_object_column</literal></entry>

<entry><type>sql_identifier</type></entry>

<entry>Name of the column that the trigger is defined on</entry>

</row>

</tbody>

</tgroup>

</table>

</sect1>

<sect1 id="infoschema-triggers">

<title><literal>triggers</literal></title>

SELECT CAST(current_database()AS sql_identifier)AS trigger_catalog,

CAST(nullAS sql_identifier)AS trigger_schema,

CAST(nullAS sql_identifier)AS trigger_name,

CAST(n.nspnameAS sql_identifier)AS trigger_schema,

CAST(t.tgnameAS sql_identifier)AS trigger_name,

CAST(current_database()AS sql_identifier)AS event_object_catalog,

CAST(nullAS sql_identifier)AS event_object_schema,

CAST(nullAS sql_identifier)AS event_object_table,

CAST(nullAS sql_identifier)AS event_object_column

WHERE false;

CAST(n.nspnameAS sql_identifier)AS event_object_schema,

CAST(c.relnameAS sql_identifier)AS event_object_table,

CAST(a.attnameAS sql_identifier)AS event_object_column

FROM pg_namespace n, pg_class c, pg_trigger t,

(SELECT tgoid, (ta0.tgat).xAS tgattnum, (ta0.tgat).nAS tgattpos

FROM (SELECToidAS tgoid,information_schema._pg_expandarray(tgattr)AS tgatFROM pg_trigger)AS ta0)AS ta,

pg_attribute a

WHEREn.oid=c.relnamespace

ANDc.oid=t.tgrelid

ANDt.oid=ta.tgoid

AND (a.attrelid,a.attnum)= (t.tgrelid,ta.tgattnum)

AND NOTt.tgisconstraint

AND (NOT pg_is_other_temp_schema(n.oid))

AND (pg_has_role(c.relowner,'USAGE')

OR has_column_privilege(c.oid,a.attnum,'INSERT, UPDATE, REFERENCES') );

GRANTSELECTON triggered_update_columns TO PUBLIC;