~ubuntu-branches/ubuntu/oneiric/postgresql-9.1/oneiric-security

rule system allows one to define an alternative 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

INSTEAD

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

Chapter 37, The Rule System, in the documentation\&.

.PP

Presently,

ON SELECT

rules must be unconditional

INSTEAD

rules and must have actions that consist of a single

SELECT

command\&. Thus, an

ON SELECT

rule effectively turns the table into a view, whose visible contents are the rows returned by the rule\(aqs

SELECT

command rather than whatever had been stored in the table (if anything)\&. It is considered better style to write a

CREATE VIEW

command than to create a real table and define an

ON SELECT

rule for it\&.

.PP

You can create the illusion of an updatable view by defining

ON INSERT,

ON UPDATE, and

ON DELETE

rules (or any subset of those that\(aqs sufficient for your purposes) to replace update actions on the view with appropriate updates on other tables\&. If you want to support

INSERT RETURNING

and so on, then be sure to put a suitable

RETURNING

clause into each of these rules\&. Alternatively, an updatable view can be implemented using

INSTEAD OF

triggers (see

CREATE TRIGGER (\fBCREATE_TRIGGER\fR(7)))\&.

.PP

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

\fImust\fR

be an unconditional

INSTEAD

rule for each action you wish to allow on the view\&. If the rule is conditional, or is not

INSTEAD, 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

DO INSTEAD NOTHING

rule to ensure that the system understands it will never be called on to update the dummy table\&. Then make the conditional rules non\-INSTEAD; in the cases where they are applied, they add to the default

INSTEAD NOTHING

action\&. (This method does not currently work to support

RETURNING

queries, however\&.)

.SH "PARAMETERS"

.PP

\fIname\fR

.RS 4

The name of a rule to create\&. This must be distinct from the name of any other rule for the same table\&. Multiple rules on the same table and same event type are applied in alphabetical name order\&.

.RE

.PP

\fIevent\fR

.RS 4

The event is one of

SELECT,

INSERT,

UPDATE, or

DELETE\&.

100

.RE

101

.PP

102

\fItable\fR

103

.RS 4

104

The name (optionally schema\-qualified) of the table or view the rule applies to\&.

105

.RE

106

.PP

107

\fIcondition\fR

108

.RS 4

109

Any

110

SQL

111

conditional expression (returning

112

boolean)\&. The condition expression cannot refer to any tables except

113

NEW

114

and

115

OLD, and cannot contain aggregate functions\&.

116

.RE

117

.PP

118

\fBINSTEAD\fR

119

.RS 4

120

INSTEAD

121

indicates that the commands should be executed

122

\fIinstead of\fR

123

the original command\&.

124

.RE

125

.PP

126

\fBALSO\fR

127

.RS 4

128

ALSO

129

indicates that the commands should be executed

130

\fIin addition to\fR

131

the original command\&.

132

.sp

133

If neither

134

ALSO

135

nor

136

INSTEAD

137

is specified,

138

ALSO

139

is the default\&.

140

.RE

141

.PP

142

\fIcommand\fR

143

.RS 4

144

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

145

SELECT,

146

INSERT,

147

UPDATE,

148

DELETE, or

149

NOTIFY\&.

150

.RE

151

.PP

152

Within

153

\fIcondition\fR

154

and

155

\fIcommand\fR, the special table names

156

NEW

157

and

158

OLD

159

can be used to refer to values in the referenced table\&.

160

NEW

161

is valid in

162

ON INSERT

163

and

164

ON UPDATE

165

rules to refer to the new row being inserted or updated\&.

166

OLD

167

is valid in

168

ON UPDATE

169

and

170

ON DELETE

171

rules to refer to the existing row being updated or deleted\&.

172

.SH "NOTES"

173

.PP

174

You must be the owner of a table to create or change rules for it\&.

175

.PP

176

In a rule for

177

INSERT,

178

UPDATE, or

179

DELETE

180

on a view, you can add a

181

RETURNING

182

clause that emits the view\(aqs columns\&. This clause will be used to compute the outputs if the rule is triggered by an

183

INSERT RETURNING,

184

UPDATE RETURNING, or

185

DELETE RETURNING

186

command respectively\&. When the rule is triggered by a command without

187

RETURNING, the rule\(aqs

188

RETURNING

189

clause will be ignored\&. The current implementation allows only unconditional

190

INSTEAD

191

rules to contain

192

RETURNING; furthermore there can be at most one

193

RETURNING

194

clause among all the rules for the same event\&. (This ensures that there is only one candidate

195

RETURNING

196

clause to be used to compute the results\&.)

197

RETURNING

198

queries on the view will be rejected if there is no

199

RETURNING

200

clause in any available rule\&.

201

.PP

202

It is very important to take care to avoid circular rules\&. For example, though each of the following two rule definitions are accepted by

203

PostgreSQL, the

204

SELECT

205

command would cause

206

PostgreSQL

207

to report an error because of recursive expansion of a rule:

208

.sp

209

.if n \{\

210

.RS 4

211

.\}

212

.nf

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

.fi

225

.if n \{\

226

.RE

227

.\}

228

.PP

229

Presently, if a rule action contains a

230

NOTIFY

231

command, the

232

NOTIFY

233

command will be executed unconditionally, that is, the

234

NOTIFY

235

will be issued even if there are not any rows that the rule should apply to\&. For example, in:

236

.sp

237

.if n \{\

238

.RS 4

239

.\}

240

.nf

241

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

242

243

UPDATE mytable SET name = \(aqfoo\(aq WHERE id = 42;

244

.fi

245

.if n \{\

246

.RE

247

.\}

248

.sp

249

one

250

NOTIFY

251

event will be sent during the

252

UPDATE, whether or not there are any rows that match the condition

253

id = 42\&. This is an implementation restriction that might be fixed in future releases\&.

254

.SH "COMPATIBILITY"

255

.PP

256

CREATE RULE

257

is a

258

PostgreSQL

259

language extension, as is the entire query rewrite system\&.

Older »