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

\fIname\fR\&. The user who defines an operator becomes its owner\&. If a schema name is given then the operator is created in the specified schema\&. Otherwise it is created in the current schema\&.

.PP

The operator name is a sequence of up to

NAMEDATALEN\-1 (63 by default) characters from the following list:

.sp

.if n \{\

.RS 4

.\}

.nf

+ \- * / < > = ~ ! @ # % ^ & | ` ?

.fi

.if n \{\

.RE

.\}

.sp

There are a few restrictions on your choice of name:

.sp

.RS 4

.ie n \{\

\h'-04'\(bu\h'+03'\c

.\}

.el \{\

.sp -1

.IP \(bu 2.3

.\}

\-\-

and

cannot appear anywhere in an operator name, since they will be taken as the start of a comment\&.

.RE

.sp

.RS 4

.ie n \{\

\h'-04'\(bu\h'+03'\c

.\}

.el \{\

.sp -1

.IP \(bu 2.3

.\}

A multicharacter operator name cannot end in

\-, unless the name also contains at least one of these characters:

.sp

.if n \{\

.RS 4

.\}

.nf

~ ! @ # % ^ & | ` ?

.fi

.if n \{\

.RE

.\}

.sp

For example,

@\-

is an allowed operator name, but

*\-

is not\&. This restriction allows

PostgreSQL

to parse SQL\-compliant commands without requiring spaces between tokens\&.

100

.RE

101

.sp

102

.RS 4

103

.ie n \{\

104

\h'-04'\(bu\h'+03'\c

105

.\}

106

.el \{\

107

.sp -1

108

.IP \(bu 2.3

109

.\}

110

The use of

111

112

as an operator name is deprecated\&. It may be disallowed altogether in a future release\&.

113

.RE

114

.PP

115

The operator

116

117

is mapped to

118

119

on input, so these two names are always equivalent\&.

120

.PP

121

At least one of

122

LEFTARG

123

and

124

RIGHTARG

125

must be defined\&. For binary operators, both must be defined\&. For right unary operators, only

126

LEFTARG

127

should be defined, while for left unary operators only

128

RIGHTARG

129

should be defined\&.

130

.PP

131

The

132

\fIfunction_name\fR

133

procedure must have been previously defined using

134

CREATE FUNCTION

135

and must be defined to accept the correct number of arguments (either one or two) of the indicated types\&.

136

.PP

137

The other clauses specify optional operator optimization clauses\&. Their meaning is detailed in

138

Section 35.13, \(lqOperator Optimization Information\(rq, in the documentation\&.

139

.SH "PARAMETERS"

140

.PP

141

\fIname\fR

142

.RS 4

143

The name of the operator to be defined\&. See above for allowable characters\&. The name can be schema\-qualified, for example

144

CREATE OPERATOR myschema\&.+ (\&.\&.\&.)\&. If not, then the operator is created in the current schema\&. Two operators in the same schema can have the same name if they operate on different data types\&. This is called

145

overloading\&.

146

.RE

147

.PP

148

\fIfunction_name\fR

149

.RS 4

150

The function used to implement this operator\&.

151

.RE

152

.PP

153

\fIleft_type\fR

154

.RS 4

155

The data type of the operator\(aqs left operand, if any\&. This option would be omitted for a left\-unary operator\&.

156

.RE

157

.PP

158

\fIright_type\fR

159

.RS 4

160

The data type of the operator\(aqs right operand, if any\&. This option would be omitted for a right\-unary operator\&.

161

.RE

162

.PP

163

\fIcom_op\fR

164

.RS 4

165

The commutator of this operator\&.

166

.RE

167

.PP

168

\fIneg_op\fR

169

.RS 4

170

The negator of this operator\&.

171

.RE

172

.PP

173

\fIres_proc\fR

174

.RS 4

175

The restriction selectivity estimator function for this operator\&.

176

.RE

177

.PP

178

\fIjoin_proc\fR

179

.RS 4

180

The join selectivity estimator function for this operator\&.

181

.RE

182

.PP

183

HASHES

184

.RS 4

185

Indicates this operator can support a hash join\&.

186

.RE

187

.PP

188

MERGES

189

.RS 4

190

Indicates this operator can support a merge join\&.

191

.RE

192

.PP

193

To give a schema\-qualified operator name in

194

\fIcom_op\fR

195

or the other optional arguments, use the

196

OPERATOR()

197

syntax, for example:

198

.sp

199

.if n \{\

200

.RS 4

201

.\}

202

.nf

203

COMMUTATOR = OPERATOR(myschema\&.===) ,

204

.fi

205

.if n \{\

206

.RE

207

.\}

208

.SH "NOTES"

209

.PP

210

Refer to

211

Section 35.12, \(lqUser-defined Operators\(rq, in the documentation

212

for further information\&.

213

.PP

214

It is not possible to specify an operator\(aqs lexical precedence in

215

CREATE OPERATOR, because the parser\(aqs precedence behavior is hard\-wired\&. See

216

Section 4.1.6, \(lqLexical Precedence\(rq, in the documentation

217

for precedence details\&.

218

.PP

219

The obsolete options

220

SORT1,

221

SORT2,

222

LTCMP, and

223

GTCMP

224

were formerly used to specify the names of sort operators associated with a merge\-joinable operator\&. This is no longer necessary, since information about associated operators is found by looking at B\-tree operator families instead\&. If one of these options is given, it is ignored except for implicitly setting

225

MERGES

226

true\&.

227

.PP

228

Use

229

DROP OPERATOR (\fBDROP_OPERATOR\fR(7))

230

to delete user\-defined operators from a database\&. Use

231

ALTER OPERATOR (\fBALTER_OPERATOR\fR(7))

232

to modify operators in a database\&.

233

.SH "EXAMPLES"

234

.PP

235

The following command defines a new operator, area\-equality, for the data type

236

box:

237

.sp

238

.if n \{\

239

.RS 4

240

.\}

241

.nf

242

CREATE OPERATOR === (

243

LEFTARG = box,

244

RIGHTARG = box,

245

PROCEDURE = area_equal_procedure,

246

COMMUTATOR = ===,

247

NEGATOR = !==,

248

RESTRICT = area_restriction_procedure,

249

JOIN = area_join_procedure,

250

HASHES, MERGES

251

);

252

.fi

253

.if n \{\

254

.RE

255

.\}

256

.SH "COMPATIBILITY"

257

.PP

258

CREATE OPERATOR

259

is a

260

PostgreSQL

261

extension\&. There are no provisions for user\-defined operators in the SQL standard\&.

262

.SH "SEE ALSO"

263

ALTER OPERATOR (\fBALTER_OPERATOR\fR(7)), CREATE OPERATOR CLASS (\fBCREATE_OPERATOR_CLASS\fR(7)), DROP OPERATOR (\fBDROP_OPERATOR\fR(7))

Older »