~vcs-imports/mammoth-replicator/trunk

DO [ ALSO | INSTEAD ] { NOTHING | <replaceable class="parameter">command</replaceable> | ( <replaceable class="parameter">command</replaceable> ; <replaceable class="parameter">command</replaceable> ... ) }

</synopsis>

</refsynopsisdiv>

<title>Description</title>

<para>

<command>CREATE RULE</command> defines a new rule applying to a specified

table or view.

<command>CREATE OR REPLACE RULE</command> will either create a

new rule, or replace an existing rule of the same name for the same

table.

</para>

<para>

The <productname>PostgreSQL</productname> rule system allows one to

define an alternate action to be performed on insertions, updates,

or deletions in database tables. Roughly speaking, a rule causes

additional commands to be executed when a given command on a given

table is executed. Alternatively, an <literal>INSTEAD</literal>

rule can replace a given command by another, or cause a command

not to be executed at all. Rules are used to implement table

views as well. It is important to realize that a rule is really

a command transformation mechanism, or command macro. The

transformation happens before the execution of the commands starts.

If you actually want an operation that fires independently for each

physical row, you probably want to use a trigger, not a rule.

More information about the rules system is in <xref linkend="rules">.

</para>

<para>

Presently, <literal>ON SELECT</literal> rules must be unconditional

<literal>INSTEAD</literal> rules and must have actions that consist

of a single <command>SELECT</command> command. Thus, an

<literal>ON SELECT</literal> rule effectively turns the table into

a view, whose visible contents are the rows returned by the rule's

<command>SELECT</command> command rather than whatever had been

stored in the table (if anything). It is considered better style

to write a <command>CREATE VIEW</command> command than to create a

real table and define an <literal>ON SELECT</literal> rule for it.

</para>

<para>

You can create the illusion of an updatable view by defining

<literal>ON INSERT</literal>, <literal>ON UPDATE</literal>, and

<literal>ON DELETE</literal> rules (or any subset of those that's

sufficient for your purposes) to replace update actions on the view

with appropriate updates on other tables.

</para>

<para>

There is a catch if you try to use conditional rules for view

updates: there <emphasis>must</> be an unconditional

<literal>INSTEAD</literal> rule for each action you wish to allow

on the view. If the rule is conditional, or is not

<literal>INSTEAD</literal>, then the system will still reject

attempts to perform the update action, because it thinks it might

end up trying to perform the action on the dummy table of the view

in some cases. If you want to handle all the useful cases in

conditional rules, add an unconditional <literal>DO

INSTEAD NOTHING</literal> rule to ensure that the system

understands it will never be called on to update the dummy table.

Then make the conditional rules non-<literal>INSTEAD</literal>; in

the cases where they are applied, they add to the default

<literal>INSTEAD NOTHING</literal> action.

</para>

</refsect1>

<title>Parameters</title>

100

101

<para>

102

The name of a rule to create. This must be distinct from the

103

name of any other rule for the same table. Multiple rules on

104

the same table and same event type are applied in alphabetical

105

name order.

106

</para>

107

</listitem>

108

</varlistentry>

109

110

111

<term><replaceable class="parameter">event</replaceable></term>

112

113

<para>

114

The event is one of <literal>SELECT</literal>,

115

<literal>INSERT</literal>, <literal>UPDATE</literal>, or

116

<literal>DELETE</literal>.

117

</para>

118

</listitem>

119

</varlistentry>

120

121

122

<term><replaceable class="parameter">table</replaceable></term>

123

124

<para>

125

The name (optionally schema-qualified) of the table or view the

126

rule applies to.

127

</para>

128

</listitem>

129

</varlistentry>

130

131

132

<term><replaceable class="parameter">condition</replaceable></term>

133

134

<para>

135

Any <acronym>SQL</acronym> conditional expression (returning

136

<type>boolean</type>). The condition expression may not refer

137

to any tables except <literal>NEW</> and <literal>OLD</>, and

138

may not contain aggregate functions.

139

</para>

140

</listitem>

141

</varlistentry>

142

143

144

<term><option>INSTEAD</option></term>

145

146

<para>

147

<literal>INSTEAD</literal> indicates that the commands should be

148

executed <emphasis>instead of</> the original command.

149

</para>

150

</listitem>

151

</varlistentry>

152

153

154

155

156

<para>

157

<literal>ALSO</literal> indicates that the commands should be

158

executed <emphasis>in addition to</emphasis> the original

159

command.

160

</para>

161

162

<para>

163

If neither <literal>ALSO</literal> nor

164

<literal>INSTEAD</literal> is specified, <literal>ALSO</literal>

165

is the default.

166

</para>

167

</listitem>

168

</varlistentry>

169

170

171

<term><replaceable class="parameter">command</replaceable></term>

172

173

<para>

174

The command or commands that make up the rule action. Valid

175

commands are <command>SELECT</command>,

176

<command>INSERT</command>, <command>UPDATE</command>,

177

<command>DELETE</command>, or <command>NOTIFY</command>.

178

</para>

179

</listitem>

180

</varlistentry>

181

</variablelist>

182

183

<para>

184

Within <replaceable class="parameter">condition</replaceable> and

185

<replaceable class="parameter">command</replaceable>, the special

186

table names <literal>NEW</literal> and <literal>OLD</literal> may

187

be used to refer to values in the referenced table.

188

<literal>NEW</literal> is valid in <literal>ON INSERT</literal> and

189

<literal>ON UPDATE</literal> rules to refer to the new row being

190

inserted or updated. <literal>OLD</literal> is valid in

191

<literal>ON UPDATE</literal> and <literal>ON DELETE</literal> rules

192

to refer to the existing row being updated or deleted.

193

</para>

194

</refsect1>

195

196

197

<title>Notes</title>

198

199

<para>

200

You must have the privilege <literal>RULE</literal> on a table to

201

be allowed to define a rule on it.

202

</para>

203

204

<para>

205

It is very important to take care to avoid circular rules. For

206

example, though each of the following two rule definitions are

207

accepted by <productname>PostgreSQL</productname>, the

208

<command>SELECT</command> command would cause

209

<productname>PostgreSQL</productname> to report an error because

210

the query cycled too many times:

211

212

213

CREATE RULE "_RETURN" AS

214

ON SELECT TO t1

215

DO INSTEAD

216

SELECT * FROM t2;

217

218

CREATE RULE "_RETURN" AS

219

ON SELECT TO t2

220

DO INSTEAD

221

SELECT * FROM t1;

222

223

SELECT * FROM t1;

224

</programlisting>

225

</para>

226

227

<para>

228

Presently, if a rule action contains a <command>NOTIFY</command>

229

command, the <command>NOTIFY</command> command will be executed

230

unconditionally, that is, the <command>NOTIFY</command> will be

231

issued even if there are not any rows that the rule should apply

232

to. For example, in

233

234

CREATE RULE notify_me AS ON UPDATE TO mytable DO ALSO NOTIFY mytable;

235

236

UPDATE mytable SET name = 'foo' WHERE id = 42;

237

</programlisting>

238

one <command>NOTIFY</command> event will be sent during the

239

<command>UPDATE</command>, whether or not there are any rows that

240

match the condition <literal>id = 42</literal>. This is an

241

implementation restriction that may be fixed in future releases.

242

</para>

243

</refsect1>

244

245

246

<title>Compatibility</title>

247

248

<para>

249

<command>CREATE RULE</command> is a

250

<productname>PostgreSQL</productname> language extension, as is the

251

entire query rewrite system.

252

</para>

253

</refsect1>

254

</refentry>

255

256

<!-- Keep this comment at the end of the file

257

Local variables:

258

mode: sgml

259

sgml-omittag:nil

260

sgml-shorttag:t

261

sgml-minimize-attributes:nil

262

sgml-always-quote-attributes:t

263

sgml-indent-step:1

264

sgml-indent-data:t

265

sgml-parent-document:nil

266

sgml-default-dtd-file:"../reference.ced"

267

sgml-exposed-tags:nil

268

sgml-local-catalogs:"/usr/lib/sgml/catalog"

269

sgml-local-ecat-files:nil

270

End:

271

-->

Older »