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

{ OPERATOR <replaceable class="parameter">strategy_number</replaceable> <replaceable class="parameter">operator_name</replaceable> [ ( <replaceable class="parameter">op_type</replaceable>, <replaceable class="parameter">op_type</replaceable> ) ] [ FOR SEARCH | FOR ORDER BY <replaceable class="parameter">sort_family_name</replaceable> ]

| FUNCTION <replaceable class="parameter">support_number</replaceable> [ ( <replaceable class="parameter">op_type</replaceable> [ , <replaceable class="parameter">op_type</replaceable> ] ) ] <replaceable class="parameter">function_name</replaceable> ( <replaceable class="parameter">argument_type</replaceable> [, ...] )

| STORAGE <replaceable class="parameter">storage_type</replaceable>

} [, ... ]

</synopsis>

</refsynopsisdiv>

<title>Description</title>

<para>

<command>CREATE OPERATOR CLASS</command> creates a new operator class.

An operator class defines how a particular data type can be used with

an index. The operator class specifies that certain operators will fill

particular roles or <quote>strategies</> for this data type and this

index method. The operator class also specifies the support procedures to

be used by

the index method when the operator class is selected for an

index column. All the operators and functions used by an operator

class must be defined before the operator class can be created.

</para>

<para>

If a schema name is given then the operator class is created in the

specified schema. Otherwise it is created in the current schema.

Two operator classes in the same schema can have the same name only if they

are for different index methods.

</para>

<para>

The user who defines an operator class becomes its owner. Presently,

the creating user must be a superuser. (This restriction is made because

an erroneous operator class definition could confuse or even crash the

server.)

</para>

<para>

<command>CREATE OPERATOR CLASS</command> does not presently check

whether the operator class definition includes all the operators and

functions required by the index method, nor whether the operators and

functions form a self-consistent set. It is the user's

responsibility to define a valid operator class.

</para>

<para>

Related operator classes can be grouped into <firstterm>operator

families</>. To add a new operator class to an existing family,

specify the <literal>FAMILY</> option in <command>CREATE OPERATOR

CLASS</command>. Without this option, the new class is placed into

a family named the same as the new class (creating that family if

it doesn't already exist).

</para>

<para>

Refer to <xref linkend="xindex"> for further information.

</para>

</refsect1>

<title>Parameters</title>

<para>

The name of the operator class to be created. The name can be

schema-qualified.

</para>

</listitem>

</varlistentry>

<term><literal>DEFAULT</></term>

100

101

<para>

102

If present, the operator class will become the default

103

operator class for its data type. At most one operator class

104

can be the default for a specific data type and index method.

105

</para>

106

</listitem>

107

</varlistentry>

108

109

110

111

112

<para>

113

The column data type that this operator class is for.

114

</para>

115

</listitem>

116

</varlistentry>

117

118

119

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

120

121

<para>

122

The name of the index method this operator class is for.

123

</para>

124

</listitem>

125

</varlistentry>

126

127

128

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

129

130

<para>

131

The name of the existing operator family to add this operator class to.

132

If not specified, a family named the same as the operator class is

133

used (creating it, if it doesn't already exist).

134

</para>

135

</listitem>

136

</varlistentry>

137

138

139

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

140

141

<para>

142

The index method's strategy number for an operator

143

associated with the operator class.

144

</para>

145

</listitem>

146

</varlistentry>

147

148

149

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

150

151

<para>

152

The name (optionally schema-qualified) of an operator associated

153

with the operator class.

154

</para>

155

</listitem>

156

</varlistentry>

157

158

159

160

161

<para>

162

In an <literal>OPERATOR</> clause,

163

the operand data type(s) of the operator, or <literal>NONE</> to

164

signify a left-unary or right-unary operator. The operand data

165

types can be omitted in the normal case where they are the same

166

as the operator class's data type.

167

</para>

168

169

<para>

170

In a <literal>FUNCTION</> clause, the operand data type(s) the

171

function is intended to support, if different from

172

the input data type(s) of the function (for B-tree and hash indexes)

173

or the class's data type (for GIN and GiST indexes). These defaults

174

are always correct, so there is no point in specifying <replaceable

175

class="parameter">op_type</replaceable> in a <literal>FUNCTION</> clause

176

in <command>CREATE OPERATOR CLASS</>, but the option is provided

177

for consistency with the comparable syntax in

178

<command>ALTER OPERATOR FAMILY</>.

179

</para>

180

</listitem>

181

</varlistentry>

182

183

184

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

185

186

<para>

187

The name (optionally schema-qualified) of an existing btree operator

188

family that describes the sort ordering associated with an ordering

189

operator.

190

</para>

191

192

<para>

193

If neither <literal>FOR SEARCH</> nor <literal>FOR ORDER BY</> is

194

specified, <literal>FOR SEARCH</> is the default.

195

</para>

196

</listitem>

197

</varlistentry>

198

199

200

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

201

202

<para>

203

The index method's support procedure number for a

204

function associated with the operator class.

205

</para>

206

</listitem>

207

</varlistentry>

208

209

210

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

211

212

<para>

213

The name (optionally schema-qualified) of a function that is an

214

index method support procedure for the operator class.

215

</para>

216

</listitem>

217

</varlistentry>

218

219

220

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

221

222

<para>

223

The parameter data type(s) of the function.

224

</para>

225

</listitem>

226

</varlistentry>

227

228

229

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

230

231

<para>

232

The data type actually stored in the index. Normally this is

233

the same as the column data type, but some index methods

234

(currently GIN and GiST) allow it to be different. The

235

<literal>STORAGE</> clause must be omitted unless the index

236

method allows a different type to be used.

237

</para>

238

</listitem>

239

</varlistentry>

240

</variablelist>

241

242

<para>

243

The <literal>OPERATOR</>, <literal>FUNCTION</>, and <literal>STORAGE</>

244

clauses can appear in any order.

245

</para>

246

</refsect1>

247

248

249

<title>Notes</title>

250

251

<para>

252

Because the index machinery does not check access permissions on functions

253

before using them, including a function or operator in an operator class

254

is tantamount to granting public execute permission on it. This is usually

255

not an issue for the sorts of functions that are useful in an operator

256

class.

257

</para>

258

259

<para>

260

The operators should not be defined by SQL functions. A SQL function

261

is likely to be inlined into the calling query, which will prevent

262

the optimizer from recognizing that the query matches an index.

263

</para>

264

265

<para>

266

Before <productname>PostgreSQL</productname> 8.4, the <literal>OPERATOR</>

267

clause could include a <literal>RECHECK</> option. This is no longer

268

supported because whether an index operator is <quote>lossy</> is now

269

determined on-the-fly at run time. This allows efficient handling of

270

cases where an operator might or might not be lossy.

271

</para>

272

</refsect1>

273

274

275

<title>Examples</title>

276

277

<para>

278

The following example command defines a GiST index operator class

279

for the data type <literal>_int4</> (array of <type>int4</type>). See the

280

<xref linkend="intarray"> module for the complete example.

281

</para>

282

283

284

CREATE OPERATOR CLASS gist__int_ops

285

DEFAULT FOR TYPE _int4 USING gist AS

286

OPERATOR 3 &&,

287

OPERATOR 6 = (anyarray, anyarray),

288

OPERATOR 7 @>,

289

OPERATOR 8 <@,

290

OPERATOR 20 @@ (_int4, query_int),

291

FUNCTION 1 g_int_consistent (internal, _int4, int, oid, internal),

292

FUNCTION 2 g_int_union (internal, internal),

293

FUNCTION 3 g_int_compress (internal),

294

FUNCTION 4 g_int_decompress (internal),

295

FUNCTION 5 g_int_penalty (internal, internal, internal),

296

FUNCTION 6 g_int_picksplit (internal, internal),

297

FUNCTION 7 g_int_same (_int4, _int4, internal);

298

</programlisting>

299

</refsect1>

300

301

302

<title>Compatibility</title>

303

304

<para>

305

<command>CREATE OPERATOR CLASS</command> is a

306

<productname>PostgreSQL</productname> extension. There is no

307

<command>CREATE OPERATOR CLASS</command> statement in the SQL

308

standard.

309

</para>

310

</refsect1>

311

312

313

314

315

316

317

318

319

320

</simplelist>

321

</refsect1>

322

</refentry>

Older »