~ubuntu-branches/ubuntu/precise/postgresql-9.1/precise-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> [, ...] )

} [, ... ]

ALTER OPERATOR FAMILY <replaceable>name</replaceable> USING <replaceable class="parameter">index_method</replaceable> DROP

{ OPERATOR <replaceable class="parameter">strategy_number</replaceable> ( <replaceable class="parameter">op_type</replaceable> [ , <replaceable class="parameter">op_type</replaceable> ] )

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

} [, ... ]

ALTER OPERATOR FAMILY <replaceable>name</replaceable> USING <replaceable class="parameter">index_method</replaceable> RENAME TO <replaceable>new_name</replaceable>

ALTER OPERATOR FAMILY <replaceable>name</replaceable> USING <replaceable class="parameter">index_method</replaceable> OWNER TO <replaceable>new_owner</replaceable>

ALTER OPERATOR FAMILY <replaceable>name</replaceable> USING <replaceable class="parameter">index_method</replaceable> SET SCHEMA <replaceable>new_schema</replaceable>

</synopsis>

</refsynopsisdiv>

<title>Description</title>

<para>

<command>ALTER OPERATOR FAMILY</command> changes the definition of

an operator family. You can add operators and support functions

to the family, remove them from the family,

or change the family's name or owner.

</para>

<para>

When operators and support functions are added to a family with

<command>ALTER OPERATOR FAMILY</command>, they are not part of any

specific operator class within the family, but are just <quote>loose</>

within the family. This indicates that these operators and functions

are compatible with the family's semantics, but are not required for

correct functioning of any specific index. (Operators and functions

that are so required should be declared as part of an operator class,

instead; see <xref linkend="sql-createopclass">.)

<productname>PostgreSQL</productname> will allow loose members of a

family to be dropped from the family at any time, but members of an

operator class cannot be dropped without dropping the whole class and

any indexes that depend on it.

Typically, single-data-type operators

and functions are part of operator classes because they are needed to

support an index on that specific data type, while cross-data-type

operators and functions are made loose members of the family.

</para>

<para>

You must be a superuser to use <command>ALTER OPERATOR FAMILY</>.

(This restriction is made because an erroneous operator family definition

could confuse or even crash the server.)

</para>

<para>

<command>ALTER OPERATOR FAMILY</command> does not presently check

whether the operator family 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 family.

</para>

<para>

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

</para>

</refsect1>

<title>Parameters</title>

<para>

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

family.

</para>

</listitem>

</varlistentry>

100

101

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

102

103

<para>

104

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

105

</para>

106

</listitem>

107

</varlistentry>

108

109

110

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

111

112

<para>

113

The index method's strategy number for an operator

114

associated with the operator family.

115

</para>

116

</listitem>

117

</varlistentry>

118

119

120

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

121

122

<para>

123

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

124

with the operator family.

125

</para>

126

</listitem>

127

</varlistentry>

128

129

130

131

132

<para>

133

In an <literal>OPERATOR</> clause,

134

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

135

signify a left-unary or right-unary operator. Unlike the comparable

136

syntax in <command>CREATE OPERATOR CLASS</>, the operand data types

137

must always be specified.

138

</para>

139

140

<para>

141

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

142

function is intended to support, if different from

143

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

144

it is not necessary to specify <replaceable

145

class="parameter">op_type</replaceable> since the function's input

146

data type(s) are always the correct ones to use. For GIN and GiST

147

indexes it is necessary to specify the input data type the function

148

is to be used with.

149

</para>

150

151

<para>

152

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

153

function is intended to support must be specified.

154

</para>

155

</listitem>

156

</varlistentry>

157

158

159

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

160

161

<para>

162

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

163

family that describes the sort ordering associated with an ordering

164

operator.

165

</para>

166

167

<para>

168

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

169

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

170

</para>

171

</listitem>

172

</varlistentry>

173

174

175

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

176

177

<para>

178

The index method's support procedure number for a

179

function associated with the operator family.

180

</para>

181

</listitem>

182

</varlistentry>

183

184

185

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

186

187

<para>

188

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

189

index method support procedure for the operator family.

190

</para>

191

</listitem>

192

</varlistentry>

193

194

195

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

196

197

<para>

198

The parameter data type(s) of the function.

199

</para>

200

</listitem>

201

</varlistentry>

202

203

204

205

206

<para>

207

The new name of the operator family.

208

</para>

209

</listitem>

210

</varlistentry>

211

212

213

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

214

215

<para>

216

The new owner of the operator family.

217

</para>

218

</listitem>

219

</varlistentry>

220

221

222

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

223

224

<para>

225

The new schema for the operator family.

226

</para>

227

</listitem>

228

</varlistentry>

229

</variablelist>

230

231

<para>

232

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

233

clauses can appear in any order.

234

</para>

235

236

</refsect1>

237

238

239

<title>Notes</title>

240

241

<para>

242

Notice that the <literal>DROP</> syntax only specifies the <quote>slot</>

243

in the operator family, by strategy or support number and input data

244

type(s). The name of the operator or function occupying the slot is not

245

mentioned. Also, for <literal>DROP FUNCTION</> the type(s) to specify

246

are the input data type(s) the function is intended to support; for

247

GIN and GiST indexes this might have nothing to do with the actual input

248

argument types of the function.

249

</para>

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 family

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

family.

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 adds cross-data-type operators and

279

support functions to an operator family that already contains B-tree

280

operator classes for data types <type>int4</> and <type>int2</>.

281

</para>

282

283

284

ALTER OPERATOR FAMILY integer_ops USING btree ADD

285

286

-- int4 vs int2

287

OPERATOR 1 < (int4, int2) ,

288

OPERATOR 2 <= (int4, int2) ,

289

OPERATOR 3 = (int4, int2) ,

290

OPERATOR 4 >= (int4, int2) ,

291

OPERATOR 5 > (int4, int2) ,

292

FUNCTION 1 btint42cmp(int4, int2) ,

293

294

-- int2 vs int4

295

OPERATOR 1 < (int2, int4) ,

296

OPERATOR 2 <= (int2, int4) ,

297

OPERATOR 3 = (int2, int4) ,

298

OPERATOR 4 >= (int2, int4) ,

299

OPERATOR 5 > (int2, int4) ,

300

FUNCTION 1 btint24cmp(int2, int4) ;

301

</programlisting>

302

303

<para>

304

To remove these entries again:

305

</para>

306

307

308

ALTER OPERATOR FAMILY integer_ops USING btree DROP

309

310

-- int4 vs int2

311

OPERATOR 1 (int4, int2) ,

312

OPERATOR 2 (int4, int2) ,

313

OPERATOR 3 (int4, int2) ,

314

OPERATOR 4 (int4, int2) ,

315

OPERATOR 5 (int4, int2) ,

316

FUNCTION 1 (int4, int2) ,

317

318

-- int2 vs int4

319

OPERATOR 1 (int2, int4) ,

320

OPERATOR 2 (int2, int4) ,

321

OPERATOR 3 (int2, int4) ,

322

OPERATOR 4 (int2, int4) ,

323

OPERATOR 5 (int2, int4) ,

324

FUNCTION 1 (int2, int4) ;

325

</programlisting>

326

</refsect1>

327

328

329

<title>Compatibility</title>

330

331

<para>

332

There is no <command>ALTER OPERATOR FAMILY</command> statement in

333

the SQL standard.

334

</para>

335

</refsect1>

336

337

338

339

340

341

342

343

344

345

346

</simplelist>

347

</refsect1>

348

</refentry>

Older »