On 24 February 2012 22:04, Dimitri Fontaine <[email protected]> wrote:
> Hi,
>
> Please find attached the latest version of the command triggers patch,
> in context diff format, with support for 79 commands and documentation
> about why only those, and with some limitations explained.
>
> I also cleaned up the node function support business that was still in
> there from the command rewriting stuff that we dropped, and did a merge
> from tonight's master branch (one of a few clean merges).
>
> This is now the whole of it, and I continue being available to make any
> necessary change, although I expect only minor changes now. Thanks to
> all reviewers and participants into the previous threads, you all have
> allowed me to reach the current point by your precious advice, comments
> and interest.
>
> The patch implements :
>
> - BEFORE/AFTER ANY command triggers
> - BEFORE/AFTER command triggers for 79 documented commands
> - regression tests exercised by the serial schedule only
> - documentation updates with examples
>
> That means you need to `make installcheck` here. Installing the tests in
> the parallel schedule does not lead to consistent output as installing a
> command trigger will impact any other test using that command, and the
> output becomes subject to the exact ordering of the concurrent tests.
>
> The only way for a BEFORE command triggers to change the command's
> behaviour is by raising an exception that aborts the whole transaction.
>
> Command triggers are called with the following arguments:
>
> - the “event” (similar to TG_WHEN, either 'BEFORE' or 'AFTER')
> - the command tag (the real one even when an ANY trigger is called)
> - the object Id if available (e.g. NULL for a CREATE statement)
> - the schema name (can be NULL)
> - the object name (can be NULL)
>
> When the trigger's procedure we're calling is written in C, then another
> argument is passed next, which is the parse tree Node * pointer.
>
> I've been talking with Marko Kreen about supporting ALTER TABLE and such
> commands automatically in Londiste: given that patch, it requires
> writing code in C that will rewrite the command string. It so happens
> that I already have worked on that code, so we intend on bringing
> support for ALTER TABLE and other commands in Skytools 3 for 9.2.
>
> I think the support code should be made into an extension that both
> Skytools and Slony would be able to share. The extension code will be
> able to adapt to major versions changes as they are released. Bucardo
> would certainly be interested too, we could NOTIFY it from such an
> extension. The design is yet to be done here, but it's clearly possible
> to implement such a feature given the current patch.
>
> The ANY trigger support is mainly there to allow people to stop any DDL
> traffic on their databases, then allowing it explicitly with an ALTER
> COMMAND TRIGGER ... SET DISABLE when appropriate only. To that
> end, the ANY command trigger is supporting more commands than you can
> attach specific triggers too.
>
> It's also possible to ENABLE a command trigger on the REPLICA only
> thanks to the session_replication_role GUC. Support for command
> triggers on an Hot Standby node is not provided in this patch.
Hi Dimitri,
I just tried building the docs with your patch and it appears
doc/src/sgml/ref/allfiles.sgml hasn't been updated with the necessary
references for alterCommandTrigger, createCommandTrigger and
dropCommandTrigger.
Also in ref/alter_command_trigger.sgml, you define SQL-CREATETRIGGER.
Shouldn't this be SQL-CREATECOMMANDTRIGGER? And there also appears to
be orphaned text in the file too, such as "Forbids the execution of
any DDL command". And there's a stray </para> on line 299.
I attach updated versions of both of those files, which seems to fix
all these problems.
--
Thom
<!--
doc/src/sgml/ref/allfiles.sgml
PostgreSQL documentation
Complete list of usable sgml source files in this directory.
-->
<!-- SQL commands -->
<!ENTITY abort SYSTEM "abort.sgml">
<!ENTITY alterAggregate SYSTEM "alter_aggregate.sgml">
<!ENTITY alterCollation SYSTEM "alter_collation.sgml">
<!ENTITY alterCommandTrigger SYSTEM "alter_command_trigger.sgml">
<!ENTITY alterConversion SYSTEM "alter_conversion.sgml">
<!ENTITY alterDatabase SYSTEM "alter_database.sgml">
<!ENTITY alterDefaultPrivileges SYSTEM "alter_default_privileges.sgml">
<!ENTITY alterDomain SYSTEM "alter_domain.sgml">
<!ENTITY alterExtension SYSTEM "alter_extension.sgml">
<!ENTITY alterForeignDataWrapper SYSTEM "alter_foreign_data_wrapper.sgml">
<!ENTITY alterForeignTable SYSTEM "alter_foreign_table.sgml">
<!ENTITY alterFunction SYSTEM "alter_function.sgml">
<!ENTITY alterGroup SYSTEM "alter_group.sgml">
<!ENTITY alterIndex SYSTEM "alter_index.sgml">
<!ENTITY alterLanguage SYSTEM "alter_language.sgml">
<!ENTITY alterLargeObject SYSTEM "alter_large_object.sgml">
<!ENTITY alterOperator SYSTEM "alter_operator.sgml">
<!ENTITY alterOperatorClass SYSTEM "alter_opclass.sgml">
<!ENTITY alterOperatorFamily SYSTEM "alter_opfamily.sgml">
<!ENTITY alterRole SYSTEM "alter_role.sgml">
<!ENTITY alterSchema SYSTEM "alter_schema.sgml">
<!ENTITY alterServer SYSTEM "alter_server.sgml">
<!ENTITY alterSequence SYSTEM "alter_sequence.sgml">
<!ENTITY alterTable SYSTEM "alter_table.sgml">
<!ENTITY alterTableSpace SYSTEM "alter_tablespace.sgml">
<!ENTITY alterTSConfig SYSTEM "alter_tsconfig.sgml">
<!ENTITY alterTSDictionary SYSTEM "alter_tsdictionary.sgml">
<!ENTITY alterTSParser SYSTEM "alter_tsparser.sgml">
<!ENTITY alterTSTemplate SYSTEM "alter_tstemplate.sgml">
<!ENTITY alterTrigger SYSTEM "alter_trigger.sgml">
<!ENTITY alterType SYSTEM "alter_type.sgml">
<!ENTITY alterUser SYSTEM "alter_user.sgml">
<!ENTITY alterUserMapping SYSTEM "alter_user_mapping.sgml">
<!ENTITY alterView SYSTEM "alter_view.sgml">
<!ENTITY analyze SYSTEM "analyze.sgml">
<!ENTITY begin SYSTEM "begin.sgml">
<!ENTITY checkpoint SYSTEM "checkpoint.sgml">
<!ENTITY close SYSTEM "close.sgml">
<!ENTITY cluster SYSTEM "cluster.sgml">
<!ENTITY commentOn SYSTEM "comment.sgml">
<!ENTITY commit SYSTEM "commit.sgml">
<!ENTITY commitPrepared SYSTEM "commit_prepared.sgml">
<!ENTITY copyTable SYSTEM "copy.sgml">
<!ENTITY createAggregate SYSTEM "create_aggregate.sgml">
<!ENTITY createCast SYSTEM "create_cast.sgml">
<!ENTITY createCollation SYSTEM "create_collation.sgml">
<!ENTITY createCommandTrigger SYSTEM "create_command_trigger.sgml">
<!ENTITY createConversion SYSTEM "create_conversion.sgml">
<!ENTITY createDatabase SYSTEM "create_database.sgml">
<!ENTITY createDomain SYSTEM "create_domain.sgml">
<!ENTITY createExtension SYSTEM "create_extension.sgml">
<!ENTITY createForeignDataWrapper SYSTEM "create_foreign_data_wrapper.sgml">
<!ENTITY createForeignTable SYSTEM "create_foreign_table.sgml">
<!ENTITY createFunction SYSTEM "create_function.sgml">
<!ENTITY createGroup SYSTEM "create_group.sgml">
<!ENTITY createIndex SYSTEM "create_index.sgml">
<!ENTITY createLanguage SYSTEM "create_language.sgml">
<!ENTITY createOperator SYSTEM "create_operator.sgml">
<!ENTITY createOperatorClass SYSTEM "create_opclass.sgml">
<!ENTITY createOperatorFamily SYSTEM "create_opfamily.sgml">
<!ENTITY createRole SYSTEM "create_role.sgml">
<!ENTITY createRule SYSTEM "create_rule.sgml">
<!ENTITY createSchema SYSTEM "create_schema.sgml">
<!ENTITY createSequence SYSTEM "create_sequence.sgml">
<!ENTITY createServer SYSTEM "create_server.sgml">
<!ENTITY createTable SYSTEM "create_table.sgml">
<!ENTITY createTableAs SYSTEM "create_table_as.sgml">
<!ENTITY createTableSpace SYSTEM "create_tablespace.sgml">
<!ENTITY createTrigger SYSTEM "create_trigger.sgml">
<!ENTITY createTSConfig SYSTEM "create_tsconfig.sgml">
<!ENTITY createTSDictionary SYSTEM "create_tsdictionary.sgml">
<!ENTITY createTSParser SYSTEM "create_tsparser.sgml">
<!ENTITY createTSTemplate SYSTEM "create_tstemplate.sgml">
<!ENTITY createType SYSTEM "create_type.sgml">
<!ENTITY createUser SYSTEM "create_user.sgml">
<!ENTITY createUserMapping SYSTEM "create_user_mapping.sgml">
<!ENTITY createView SYSTEM "create_view.sgml">
<!ENTITY deallocate SYSTEM "deallocate.sgml">
<!ENTITY declare SYSTEM "declare.sgml">
<!ENTITY delete SYSTEM "delete.sgml">
<!ENTITY discard SYSTEM "discard.sgml">
<!ENTITY do SYSTEM "do.sgml">
<!ENTITY dropAggregate SYSTEM "drop_aggregate.sgml">
<!ENTITY dropCast SYSTEM "drop_cast.sgml">
<!ENTITY dropCollation SYSTEM "drop_collation.sgml">
<!ENTITY dropCommandTrigger SYSTEM "drop_command_trigger.sgml">
<!ENTITY dropConversion SYSTEM "drop_conversion.sgml">
<!ENTITY dropDatabase SYSTEM "drop_database.sgml">
<!ENTITY dropDomain SYSTEM "drop_domain.sgml">
<!ENTITY dropExtension SYSTEM "drop_extension.sgml">
<!ENTITY dropForeignDataWrapper SYSTEM "drop_foreign_data_wrapper.sgml">
<!ENTITY dropForeignTable SYSTEM "drop_foreign_table.sgml">
<!ENTITY dropFunction SYSTEM "drop_function.sgml">
<!ENTITY dropGroup SYSTEM "drop_group.sgml">
<!ENTITY dropIndex SYSTEM "drop_index.sgml">
<!ENTITY dropLanguage SYSTEM "drop_language.sgml">
<!ENTITY dropOperator SYSTEM "drop_operator.sgml">
<!ENTITY dropOperatorClass SYSTEM "drop_opclass.sgml">
<!ENTITY dropOperatorFamily SYSTEM "drop_opfamily.sgml">
<!ENTITY dropOwned SYSTEM "drop_owned.sgml">
<!ENTITY dropRole SYSTEM "drop_role.sgml">
<!ENTITY dropRule SYSTEM "drop_rule.sgml">
<!ENTITY dropSchema SYSTEM "drop_schema.sgml">
<!ENTITY dropSequence SYSTEM "drop_sequence.sgml">
<!ENTITY dropServer SYSTEM "drop_server.sgml">
<!ENTITY dropTable SYSTEM "drop_table.sgml">
<!ENTITY dropTableSpace SYSTEM "drop_tablespace.sgml">
<!ENTITY dropTrigger SYSTEM "drop_trigger.sgml">
<!ENTITY dropTSConfig SYSTEM "drop_tsconfig.sgml">
<!ENTITY dropTSDictionary SYSTEM "drop_tsdictionary.sgml">
<!ENTITY dropTSParser SYSTEM "drop_tsparser.sgml">
<!ENTITY dropTSTemplate SYSTEM "drop_tstemplate.sgml">
<!ENTITY dropType SYSTEM "drop_type.sgml">
<!ENTITY dropUser SYSTEM "drop_user.sgml">
<!ENTITY dropUserMapping SYSTEM "drop_user_mapping.sgml">
<!ENTITY dropView SYSTEM "drop_view.sgml">
<!ENTITY end SYSTEM "end.sgml">
<!ENTITY execute SYSTEM "execute.sgml">
<!ENTITY explain SYSTEM "explain.sgml">
<!ENTITY fetch SYSTEM "fetch.sgml">
<!ENTITY grant SYSTEM "grant.sgml">
<!ENTITY insert SYSTEM "insert.sgml">
<!ENTITY listen SYSTEM "listen.sgml">
<!ENTITY load SYSTEM "load.sgml">
<!ENTITY lock SYSTEM "lock.sgml">
<!ENTITY move SYSTEM "move.sgml">
<!ENTITY notify SYSTEM "notify.sgml">
<!ENTITY prepare SYSTEM "prepare.sgml">
<!ENTITY prepareTransaction SYSTEM "prepare_transaction.sgml">
<!ENTITY reassignOwned SYSTEM "reassign_owned.sgml">
<!ENTITY reindex SYSTEM "reindex.sgml">
<!ENTITY releaseSavepoint SYSTEM "release_savepoint.sgml">
<!ENTITY reset SYSTEM "reset.sgml">
<!ENTITY revoke SYSTEM "revoke.sgml">
<!ENTITY rollback SYSTEM "rollback.sgml">
<!ENTITY rollbackPrepared SYSTEM "rollback_prepared.sgml">
<!ENTITY rollbackTo SYSTEM "rollback_to.sgml">
<!ENTITY savepoint SYSTEM "savepoint.sgml">
<!ENTITY securityLabel SYSTEM "security_label.sgml">
<!ENTITY select SYSTEM "select.sgml">
<!ENTITY selectInto SYSTEM "select_into.sgml">
<!ENTITY set SYSTEM "set.sgml">
<!ENTITY setConstraints SYSTEM "set_constraints.sgml">
<!ENTITY setRole SYSTEM "set_role.sgml">
<!ENTITY setSessionAuth SYSTEM "set_session_auth.sgml">
<!ENTITY setTransaction SYSTEM "set_transaction.sgml">
<!ENTITY show SYSTEM "show.sgml">
<!ENTITY startTransaction SYSTEM "start_transaction.sgml">
<!ENTITY truncate SYSTEM "truncate.sgml">
<!ENTITY unlisten SYSTEM "unlisten.sgml">
<!ENTITY update SYSTEM "update.sgml">
<!ENTITY vacuum SYSTEM "vacuum.sgml">
<!ENTITY values SYSTEM "values.sgml">
<!-- applications and utilities -->
<!ENTITY clusterdb SYSTEM "clusterdb.sgml">
<!ENTITY createdb SYSTEM "createdb.sgml">
<!ENTITY createlang SYSTEM "createlang.sgml">
<!ENTITY createuser SYSTEM "createuser.sgml">
<!ENTITY dropdb SYSTEM "dropdb.sgml">
<!ENTITY droplang SYSTEM "droplang.sgml">
<!ENTITY dropuser SYSTEM "dropuser.sgml">
<!ENTITY ecpgRef SYSTEM "ecpg-ref.sgml">
<!ENTITY initdb SYSTEM "initdb.sgml">
<!ENTITY pgBasebackup SYSTEM "pg_basebackup.sgml">
<!ENTITY pgConfig SYSTEM "pg_config-ref.sgml">
<!ENTITY pgControldata SYSTEM "pg_controldata.sgml">
<!ENTITY pgCtl SYSTEM "pg_ctl-ref.sgml">
<!ENTITY pgDump SYSTEM "pg_dump.sgml">
<!ENTITY pgDumpall SYSTEM "pg_dumpall.sgml">
<!ENTITY pgReceivexlog SYSTEM "pg_receivexlog.sgml">
<!ENTITY pgResetxlog SYSTEM "pg_resetxlog.sgml">
<!ENTITY pgRestore SYSTEM "pg_restore.sgml">
<!ENTITY postgres SYSTEM "postgres-ref.sgml">
<!ENTITY postmaster SYSTEM "postmaster.sgml">
<!ENTITY psqlRef SYSTEM "psql-ref.sgml">
<!ENTITY reindexdb SYSTEM "reindexdb.sgml">
<!ENTITY vacuumdb SYSTEM "vacuumdb.sgml">
<!--
doc/src/sgml/ref/create_trigger.sgml
PostgreSQL documentation
-->
<refentry id="SQL-CREATECOMMANDTRIGGER">
<refmeta>
<refentrytitle>CREATE COMMAND TRIGGER</refentrytitle>
<manvolnum>7</manvolnum>
<refmiscinfo>SQL - Language Statements</refmiscinfo>
</refmeta>
<refnamediv>
<refname>CREATE COMMAND TRIGGER</refname>
<refpurpose>define a new trigger</refpurpose>
</refnamediv>
<indexterm zone="sql-createcommandtrigger">
<primary>CREATE COMMAND TRIGGER</primary>
</indexterm>
<refsynopsisdiv>
<synopsis>
CREATE TRIGGER <replaceable class="PARAMETER">name</replaceable> { BEFORE | AFTER } ANY COMMAND
EXECUTE PROCEDURE <replaceable class="PARAMETER">function_name</replaceable> ()
CREATE TRIGGER <replaceable class="PARAMETER">name</replaceable> { BEFORE | AFTER } COMMAND <replaceable class="PARAMETER">command</replaceable> [, ... ]
EXECUTE PROCEDURE <replaceable class="PARAMETER">function_name</replaceable> ()
<phrase>where <replaceable class="parameter">command</replaceable> can be one of:</phrase>
CREATE SCHEMA
CREATE EXTENSION
CREATE LANGUAGE
CREATE FUNCTION
CREATE TABLE
CREATE SERVER
CREATE FOREIGN TABLE
CREATE FOREIGN DATA WRAPPER
CREATE USER MAPPING
CREATE INDEX
CREATE SEQUENCE
CREATE VIEW
CREATE RULE
CREATE AGGREGATE
CREATE OPERATOR
CREATE COLLATION
CREATE TEXT SEARCH PARSER
CREATE TEXT SEARCH DICTIONARY
CREATE TEXT SEARCH TEMPLATE
CREATE TEXT SEARCH CONFIGURATION
CREATE TYPE_P
CREATE DOMAIN_P
CREATE TRIGGER
CREATE CONVERSION_P
CREATE CAST
CREATE OPERATOR CLASS
CREATE OPERATOR FAMILY
ALTER SCHEMA
ALTER EXTENSION
ALTER FUNCTION
ALTER TABLE
ALTER SERVER
ALTER FOREIGN TABLE
ALTER FOREIGN DATA WRAPPER
ALTER USER MAPPING
ALTER AGGREGATE
ALTER OPERATOR
ALTER OPERATOR CLASS
ALTER OPERATOR FAMILY
ALTER COLLATION
ALTER TEXT SEARCH PARSER
ALTER TEXT SEARCH DICTIONARY
ALTER TEXT SEARCH TEMPLATE
ALTER TEXT SEARCH CONFIGURATION
ALTER TYPE_P
ALTER DOMAIN_P
ALTER TRIGGER
DROP TABLE
DROP SEQUENCE
DROP VIEW
DROP INDEX
DROP TYPE_P
DROP DOMAIN_P
DROP COLLATION
DROP CONVERSION_P
DROP SCHEMA
DROP EXTENSION
DROP TEXT SEARCH PARSER
DROP TEXT SEARCH DICTIONARY
DROP TEXT SEARCH TEMPLATE
DROP TEXT SEARCH CONFIGURATION
DROP LANGUAGE
DROP SERVER
DROP FOREIGN TABLE
DROP FOREIGN DATA WRAPPER
DROP USER MAPPING
DROP TRIGGER
DROP ASSERTION
DROP OPERATOR CLASS
DROP OPERATOR FAMILY
DROP FUNCTION
DROP AGGREGATE
DROP OPERATOR
DROP CAST
DROP RULE
REINDEX
VACUUM
CLUSTER
LOAD
</synopsis>
</refsynopsisdiv>
<refsect1>
<title>Description</title>
<para>
<command>CREATE COMMAND TRIGGER</command> creates a new command trigger.
The trigger will be associated with the specified command and will
execute the specified
function <replaceable class="parameter">function_name</replaceable> when
that command is run.
</para>
<para>
The command trigger can be specified to fire before or after the command
is executed. A command trigger's function must
return <literal>void</literal>, the only it can aborts the execution of
the command is by raising an exception.
</para>
<para>
Refer to <xref linkend="triggers"> for more information about triggers.
</para>
</refsect1>
<refsect1>
<title>Parameters</title>
<variablelist>
<varlistentry>
<term><replaceable class="parameter">name</replaceable></term>
<listitem>
<para>
The name to give the new trigger. This must be distinct from
the name of any other trigger for the same table.
The name cannot be schema-qualified — the trigger inherits the
schema of its table. For a constraint trigger, this is also the name to
use when modifying the trigger's behavior using
<command>SET CONSTRAINTS</>.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term><literal>BEFORE</literal></term>
<term><literal>AFTER</literal></term>
<listitem>
<para>
Determines whether the function is called before or after the command
is executed.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term><replaceable class="parameter">command</replaceable></term>
<listitem>
<para>
The tag of the command the trigger is for. Supported commands are
mainly those acting on database objects, plus some more facilities.
That leaves out the following list of non supported commands.
</para>
<para>
Commands that refers to global objects, such as databases, tablespaces
and roles are not supported. As command triggers are per-database, it
would be weird to affect e.g. a tablespace depending on which database
you are connected to.
</para>
<para>
Commands that exercize their own transaction control are only
supported in <literal>BEFORE</literal> command triggers, that's the
case for <literal>VACUUM</literal>, <literal>CLUSTER</literal>
<literal>CREATE INDEX CONCURRENTLY</literal>, and <literal>REINDEX
DATABASE</literal>.
</para>
<para>
Commands that are related to transaction control (such
as <literal>BEGIN</literal> or <literal>COMMIT</literal>), related to
prepared plans
(e.g. <literal>PREPARE</literal>, <literal>DEALLOCATE</literal>),
cursors management
(e.g. <literal>DECLARE</literal>, <literal>FETCH</literal>), setting
variables (<literal>SET</literal>), the <literal>LISTEN</literal>
feature, and security are not supported either.
</para>
<para>
Command triggers on <literal>CREATE COMMAND
TRIGGER</literal>, <literal>ALTER COMMAND TRIGGER</literal>
and <literal>DROP COMMAND TRIGGER</literal> are not supported so as
not to be able to take over control from a superuser.
</para>
<para>
Triggers on <literal>ANY</literal> command support more commands than
just this list, and will only provide the <literal>command
tag</literal> argument as <literal>NOT NULL</literal>. Supporting more
commands is made so that you can actually block <xref linkend="ddl">
commands in one go.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term><replaceable class="parameter">function_name</replaceable></term>
<listitem>
<para>
A user-supplied function that is declared as taking 5 arguments of
type text, text, oid, text, text and returning void.
</para>
<para>
If your command trigger is implemented in <literal>C</literal> then it
will be called with yet another argument, of
type <literal>internal</literal>, which is a pointer to
the <literal>Node *</literal> parse tree.
</para>
<para>
The command trigger function is called with the
parameters <literal>tg_when</literal> (which is set to either 'BEFORE'
or 'AFTER'), <literal>command
tag</literal>, <literal>objectid</literal> (can be null in case of a
BEFORE CREATE or an AFTER DROP command trigger
timing), <literal>schemaname</literal> (can be null for objects not
living in a schema, and for sequences due to an implementation limit)
and <literal>object name</literal> (can be null for any command
triggers).
</para>
<para>
The command <literal>CREATE SEQUENCE</literal> lacks support for
the <literal>schemaname</literal> command trigger argument, it
provides <literal>NULL</literal> in all cases.
</para>
</listitem>
</varlistentry>
</variablelist>
</refsect1>
<refsect1 id="SQL-CREATECOMMANDTRIGGER-notes">
<title>Notes</title>
<para>
To create a trigger on a command, the user must be superuser.
</para>
<para>
Use <xref linkend="sql-dropcommandtrigger"> to remove a command trigger.
</para>
</refsect1>
<refsect1 id="SQL-CREATECOMMANDTRIGGER-examples">
<title>Examples</title>
<para>
Forbids the execution of any DDL command:
</para>
<programlisting>
CREATE OR REPLACE FUNCTION abort_any_command
(tg_when text, cmd_tag text, objectid oid, schemaname text, objectname text)
RETURNS void LANGUAGE plpgsql AS $$
BEGIN
RAISE EXCEPTION 'command % is disabled' % cmd_tag;
END;
$$;
CREATE TRIGGER abort_ddl
BEFORE COMMAND CREATE TABLE
EXECUTE PROCEDURE abort_any_command();
</programlisting>
<para>
Execute the function <function>enforce_local_style</> each time
a <literal>CREATE TABLE</literal> command is run:
</para>
<programlisting>
CREATE OR REPLACE FUNCTION enforce_local_style
(tg_when text, cmd_tag text, objectid oid, schemaname text, objectname text)
RETURNS bool LANGUAGE plpgsql AS $$
BEGIN
IF substring(objectname, 0, 4) NOT IN ('ab_', 'cz_', 'fr_')
THEN
RAISE EXCEPTION 'invalid relation name: %', objectname;
END IF;
END;
$$;
CREATE TRIGGER check_style
BEFORE COMMAND CREATE TABLE
EXECUTE PROCEDURE enforce_local_style();
</programlisting>
</refsect1>
<refsect1 id="SQL-CREATECOMMANDTRIGGER-compatibility">
<title>Compatibility</title>
<para>
<command>CREATE COMMAND TRIGGER</command> is a
<productname>PostgreSQL</productname> extension of the <acronym>SQL</>
standard.
</para>
</refsect1>
<refsect1>
<title>See Also</title>
<simplelist type="inline">
<member><xref linkend="sql-createfunction"></member>
<member><xref linkend="sql-altercommandtrigger"></member>
<member><xref linkend="sql-dropcommandtrigger"></member>
</simplelist>
</refsect1>
</refentry>
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
