1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
|
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<HTML
><HEAD
><TITLE
>The PostgreSQL Type System</TITLE
><META
NAME="GENERATOR"
CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK
REV="MADE"
HREF="mailto:pgsql-docs@postgresql.org"><LINK
REL="HOME"
TITLE="PostgreSQL 9.3.12 Documentation"
HREF="index.html"><LINK
REL="UP"
TITLE="Extending SQL"
HREF="extend.html"><LINK
REL="PREVIOUS"
TITLE="How Extensibility Works"
HREF="extend-how.html"><LINK
REL="NEXT"
TITLE="User-defined Functions"
HREF="xfunc.html"><LINK
REL="STYLESHEET"
TYPE="text/css"
HREF="stylesheet.css"><META
HTTP-EQUIV="Content-Type"
CONTENT="text/html; charset=ISO-8859-1"><META
NAME="creation"
CONTENT="2016-03-28T20:28:06"></HEAD
><BODY
CLASS="SECT1"
><DIV
CLASS="NAVHEADER"
><TABLE
SUMMARY="Header navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TH
COLSPAN="5"
ALIGN="center"
VALIGN="bottom"
><A
HREF="index.html"
>PostgreSQL 9.3.12 Documentation</A
></TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
TITLE="How Extensibility Works"
HREF="extend-how.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="extend.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="60%"
ALIGN="center"
VALIGN="bottom"
>Chapter 35. Extending <ACRONYM
CLASS="ACRONYM"
>SQL</ACRONYM
></TD
><TD
WIDTH="20%"
ALIGN="right"
VALIGN="top"
><A
TITLE="User-defined Functions"
HREF="xfunc.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
></TABLE
><HR
ALIGN="LEFT"
WIDTH="100%"></DIV
><DIV
CLASS="SECT1"
><H1
CLASS="SECT1"
><A
NAME="EXTEND-TYPE-SYSTEM"
>35.2. The <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
> Type System</A
></H1
><P
> <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
> data types are divided into base
types, composite types, domains, and pseudo-types.
</P
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="AEN52900"
>35.2.1. Base Types</A
></H2
><P
> Base types are those, like <TT
CLASS="TYPE"
>int4</TT
>, that are
implemented below the level of the <ACRONYM
CLASS="ACRONYM"
>SQL</ACRONYM
> language
(typically in a low-level language such as C). They generally
correspond to what are often known as abstract data types.
<SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
> can only operate on such
types through functions provided by the user and only understands
the behavior of such types to the extent that the user describes
them. Base types are further subdivided into scalar and array
types. For each scalar type, a corresponding array type is
automatically created that can hold variable-size arrays of that
scalar type.
</P
></DIV
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="AEN52906"
>35.2.2. Composite Types</A
></H2
><P
> Composite types, or row types, are created whenever the user
creates a table. It is also possible to use <A
HREF="sql-createtype.html"
>CREATE TYPE</A
> to
define a <SPAN
CLASS="QUOTE"
>"stand-alone"</SPAN
> composite type with no associated
table. A composite type is simply a list of types with
associated field names. A value of a composite type is a row or
record of field values. The user can access the component fields
from <ACRONYM
CLASS="ACRONYM"
>SQL</ACRONYM
> queries. Refer to <A
HREF="rowtypes.html"
>Section 8.16</A
>
for more information on composite types.
</P
></DIV
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="EXTEND-TYPE-SYSTEM-DOMAINS"
>35.2.3. Domains</A
></H2
><P
> A domain is based on a particular base type and for many purposes
is interchangeable with its base type. However, a domain can
have constraints that restrict its valid values to a subset of
what the underlying base type would allow.
</P
><P
> Domains can be created using the <ACRONYM
CLASS="ACRONYM"
>SQL</ACRONYM
> command
<A
HREF="sql-createdomain.html"
>CREATE DOMAIN</A
>.
Their creation and use is not discussed in this chapter.
</P
></DIV
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="AEN52919"
>35.2.4. Pseudo-Types</A
></H2
><P
> There are a few <SPAN
CLASS="QUOTE"
>"pseudo-types"</SPAN
> for special purposes.
Pseudo-types cannot appear as columns of tables or attributes of
composite types, but they can be used to declare the argument and
result types of functions. This provides a mechanism within the
type system to identify special classes of functions. <A
HREF="datatype-pseudo.html#DATATYPE-PSEUDOTYPES-TABLE"
>Table 8-24</A
> lists the existing
pseudo-types.
</P
></DIV
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="EXTEND-TYPES-POLYMORPHIC"
>35.2.5. Polymorphic Types</A
></H2
><P
> Five pseudo-types of special interest are <TT
CLASS="TYPE"
>anyelement</TT
>,
<TT
CLASS="TYPE"
>anyarray</TT
>, <TT
CLASS="TYPE"
>anynonarray</TT
>, <TT
CLASS="TYPE"
>anyenum</TT
>,
and <TT
CLASS="TYPE"
>anyrange</TT
>,
which are collectively called <I
CLASS="FIRSTTERM"
>polymorphic types</I
>.
Any function declared using these types is said to be
a <I
CLASS="FIRSTTERM"
>polymorphic function</I
>. A polymorphic function can
operate on many different data types, with the specific data type(s)
being determined by the data types actually passed to it in a particular
call.
</P
><P
> Polymorphic arguments and results are tied to each other and are resolved
to a specific data type when a query calling a polymorphic function is
parsed. Each position (either argument or return value) declared as
<TT
CLASS="TYPE"
>anyelement</TT
> is allowed to have any specific actual
data type, but in any given call they must all be the
<SPAN
CLASS="emphasis"
><I
CLASS="EMPHASIS"
>same</I
></SPAN
> actual type. Each
position declared as <TT
CLASS="TYPE"
>anyarray</TT
> can have any array data type,
but similarly they must all be the same type. And similarly,
positions declared as <TT
CLASS="TYPE"
>anyrange</TT
> must all be the same range
type. Furthermore, if there are
positions declared <TT
CLASS="TYPE"
>anyarray</TT
> and others declared
<TT
CLASS="TYPE"
>anyelement</TT
>, the actual array type in the
<TT
CLASS="TYPE"
>anyarray</TT
> positions must be an array whose elements are
the same type appearing in the <TT
CLASS="TYPE"
>anyelement</TT
> positions.
Similarly, if there are positions declared <TT
CLASS="TYPE"
>anyrange</TT
>
and others declared <TT
CLASS="TYPE"
>anyelement</TT
>, the actual range type in
the <TT
CLASS="TYPE"
>anyrange</TT
> positions must be a range whose subtype is
the same type appearing in the <TT
CLASS="TYPE"
>anyelement</TT
> positions.
<TT
CLASS="TYPE"
>anynonarray</TT
> is treated exactly the same as <TT
CLASS="TYPE"
>anyelement</TT
>,
but adds the additional constraint that the actual type must not be
an array type.
<TT
CLASS="TYPE"
>anyenum</TT
> is treated exactly the same as <TT
CLASS="TYPE"
>anyelement</TT
>,
but adds the additional constraint that the actual type must
be an enum type.
</P
><P
> Thus, when more than one argument position is declared with a polymorphic
type, the net effect is that only certain combinations of actual argument
types are allowed. For example, a function declared as
<TT
CLASS="LITERAL"
>equal(anyelement, anyelement)</TT
> will take any two input values,
so long as they are of the same data type.
</P
><P
> When the return value of a function is declared as a polymorphic type,
there must be at least one argument position that is also polymorphic,
and the actual data type supplied as the argument determines the actual
result type for that call. For example, if there were not already
an array subscripting mechanism, one could define a function that
implements subscripting as <TT
CLASS="LITERAL"
>subscript(anyarray, integer)
returns anyelement</TT
>. This declaration constrains the actual first
argument to be an array type, and allows the parser to infer the correct
result type from the actual first argument's type. Another example
is that a function declared as <TT
CLASS="LITERAL"
>f(anyarray) returns anyenum</TT
>
will only accept arrays of enum types.
</P
><P
> Note that <TT
CLASS="TYPE"
>anynonarray</TT
> and <TT
CLASS="TYPE"
>anyenum</TT
> do not represent
separate type variables; they are the same type as
<TT
CLASS="TYPE"
>anyelement</TT
>, just with an additional constraint. For
example, declaring a function as <TT
CLASS="LITERAL"
>f(anyelement, anyenum)</TT
>
is equivalent to declaring it as <TT
CLASS="LITERAL"
>f(anyenum, anyenum)</TT
>:
both actual arguments have to be the same enum type.
</P
><P
> A variadic function (one taking a variable number of arguments, as in
<A
HREF="xfunc-sql.html#XFUNC-SQL-VARIADIC-FUNCTIONS"
>Section 35.4.5</A
>) can be
polymorphic: this is accomplished by declaring its last parameter as
<TT
CLASS="LITERAL"
>VARIADIC</TT
> <TT
CLASS="TYPE"
>anyarray</TT
>. For purposes of argument
matching and determining the actual result type, such a function behaves
the same as if you had written the appropriate number of
<TT
CLASS="TYPE"
>anynonarray</TT
> parameters.
</P
></DIV
></DIV
><DIV
CLASS="NAVFOOTER"
><HR
ALIGN="LEFT"
WIDTH="100%"><TABLE
SUMMARY="Footer navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
><A
HREF="extend-how.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="index.html"
ACCESSKEY="H"
>Home</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
><A
HREF="xfunc.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
>How Extensibility Works</TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="extend.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
>User-defined Functions</TD
></TR
></TABLE
></DIV
></BODY
></HTML
>
|