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
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
|
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>F.43. tablefunc — functions that return tables (crosstab and others)</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="sslinfo.html" title="F.42. sslinfo — obtain client SSL information" /><link rel="next" href="tcn.html" title="F.44. tcn — a trigger function to notify listeners of changes to table content" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">F.43. tablefunc — functions that return tables (<code class="function">crosstab</code> and others)</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="sslinfo.html" title="F.42. sslinfo — obtain client SSL information">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="contrib.html" title="Appendix F. Additional Supplied Modules and Extensions">Up</a></td><th width="60%" align="center">Appendix F. Additional Supplied Modules and Extensions</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 16.2 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="tcn.html" title="F.44. tcn — a trigger function to notify listeners of changes to table content">Next</a></td></tr></table><hr /></div><div class="sect1" id="TABLEFUNC"><div class="titlepage"><div><div><h2 class="title" style="clear: both">F.43. tablefunc — functions that return tables (<code class="function">crosstab</code> and others) <a href="#TABLEFUNC" class="id_link">#</a></h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="tablefunc.html#TABLEFUNC-FUNCTIONS-SECT">F.43.1. Functions Provided</a></span></dt><dt><span class="sect2"><a href="tablefunc.html#TABLEFUNC-AUTHOR">F.43.2. Author</a></span></dt></dl></div><a id="id-1.11.7.53.2" class="indexterm"></a><p>
The <code class="filename">tablefunc</code> module includes various functions that return
tables (that is, multiple rows). These functions are useful both in their
own right and as examples of how to write C functions that return
multiple rows.
</p><p>
This module is considered <span class="quote">“<span class="quote">trusted</span>”</span>, that is, it can be
installed by non-superusers who have <code class="literal">CREATE</code> privilege
on the current database.
</p><div class="sect2" id="TABLEFUNC-FUNCTIONS-SECT"><div class="titlepage"><div><div><h3 class="title">F.43.1. Functions Provided <a href="#TABLEFUNC-FUNCTIONS-SECT" class="id_link">#</a></h3></div></div></div><p>
<a class="xref" href="tablefunc.html#TABLEFUNC-FUNCTIONS" title="Table F.32. tablefunc Functions">Table F.32</a> summarizes the functions provided
by the <code class="filename">tablefunc</code> module.
</p><div class="table" id="TABLEFUNC-FUNCTIONS"><p class="title"><strong>Table F.32. <code class="filename">tablefunc</code> Functions</strong></p><div class="table-contents"><table class="table" summary="tablefunc Functions" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature">
Function
</p>
<p>
Description
</p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">
<code class="function">normal_rand</code> ( <em class="parameter"><code>numvals</code></em> <code class="type">integer</code>, <em class="parameter"><code>mean</code></em> <code class="type">float8</code>, <em class="parameter"><code>stddev</code></em> <code class="type">float8</code> )
→ <code class="returnvalue">setof float8</code>
</p>
<p>
Produces a set of normally distributed random values.
</p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
<code class="function">crosstab</code> ( <em class="parameter"><code>sql</code></em> <code class="type">text</code> )
→ <code class="returnvalue">setof record</code>
</p>
<p>
Produces a <span class="quote">“<span class="quote">pivot table</span>”</span> containing
row names plus <em class="replaceable"><code>N</code></em> value columns, where
<em class="replaceable"><code>N</code></em> is determined by the row type specified
in the calling query.
</p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
<code class="function">crosstab<em class="replaceable"><code>N</code></em></code> ( <em class="parameter"><code>sql</code></em> <code class="type">text</code> )
→ <code class="returnvalue">setof table_crosstab_<em class="replaceable"><code>N</code></em></code>
</p>
<p>
Produces a <span class="quote">“<span class="quote">pivot table</span>”</span> containing
row names plus <em class="replaceable"><code>N</code></em> value columns.
<code class="function">crosstab2</code>, <code class="function">crosstab3</code>, and
<code class="function">crosstab4</code> are predefined, but you can create additional
<code class="function">crosstab<em class="replaceable"><code>N</code></em></code> functions as described below.
</p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
<code class="function">crosstab</code> ( <em class="parameter"><code>source_sql</code></em> <code class="type">text</code>, <em class="parameter"><code>category_sql</code></em> <code class="type">text</code> )
→ <code class="returnvalue">setof record</code>
</p>
<p>
Produces a <span class="quote">“<span class="quote">pivot table</span>”</span>
with the value columns specified by a second query.
</p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
<code class="function">crosstab</code> ( <em class="parameter"><code>sql</code></em> <code class="type">text</code>, <em class="parameter"><code>N</code></em> <code class="type">integer</code> )
→ <code class="returnvalue">setof record</code>
</p>
<p>
Obsolete version of <code class="function">crosstab(text)</code>.
The parameter <em class="parameter"><code>N</code></em> is now ignored, since the
number of value columns is always determined by the calling query.
</p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
<a id="id-1.11.7.53.5.3.2.2.6.1.1.1" class="indexterm"></a>
<code class="function">connectby</code> ( <em class="parameter"><code>relname</code></em> <code class="type">text</code>, <em class="parameter"><code>keyid_fld</code></em> <code class="type">text</code>, <em class="parameter"><code>parent_keyid_fld</code></em> <code class="type">text</code>
[<span class="optional">, <em class="parameter"><code>orderby_fld</code></em> <code class="type">text</code> </span>], <em class="parameter"><code>start_with</code></em> <code class="type">text</code>, <em class="parameter"><code>max_depth</code></em> <code class="type">integer</code>
[<span class="optional">, <em class="parameter"><code>branch_delim</code></em> <code class="type">text</code> </span>] )
→ <code class="returnvalue">setof record</code>
</p>
<p>
Produces a representation of a hierarchical tree structure.
</p></td></tr></tbody></table></div></div><br class="table-break" /><div class="sect3" id="TABLEFUNC-FUNCTIONS-NORMAL-RAND"><div class="titlepage"><div><div><h4 class="title">F.43.1.1. <code class="function">normal_rand</code> <a href="#TABLEFUNC-FUNCTIONS-NORMAL-RAND" class="id_link">#</a></h4></div></div></div><a id="id-1.11.7.53.5.4.2" class="indexterm"></a><pre class="synopsis">
normal_rand(int numvals, float8 mean, float8 stddev) returns setof float8
</pre><p>
<code class="function">normal_rand</code> produces a set of normally distributed random
values (Gaussian distribution).
</p><p>
<em class="parameter"><code>numvals</code></em> is the number of values to be returned
from the function. <em class="parameter"><code>mean</code></em> is the mean of the normal
distribution of values and <em class="parameter"><code>stddev</code></em> is the standard
deviation of the normal distribution of values.
</p><p>
For example, this call requests 1000 values with a mean of 5 and a
standard deviation of 3:
</p><pre class="screen">
test=# SELECT * FROM normal_rand(1000, 5, 3);
normal_rand
----------------------
1.56556322244898
9.10040991424657
5.36957140345079
-0.369151492880995
0.283600703686639
.
.
.
4.82992125404908
9.71308014517282
2.49639286969028
(1000 rows)
</pre></div><div class="sect3" id="TABLEFUNC-FUNCTIONS-CROSSTAB-TEXT"><div class="titlepage"><div><div><h4 class="title">F.43.1.2. <code class="function">crosstab(text)</code> <a href="#TABLEFUNC-FUNCTIONS-CROSSTAB-TEXT" class="id_link">#</a></h4></div></div></div><a id="id-1.11.7.53.5.5.2" class="indexterm"></a><pre class="synopsis">
crosstab(text sql)
crosstab(text sql, int N)
</pre><p>
The <code class="function">crosstab</code> function is used to produce <span class="quote">“<span class="quote">pivot</span>”</span>
displays, wherein data is listed across the page rather than down.
For example, we might have data like
</p><pre class="programlisting">
row1 val11
row1 val12
row1 val13
...
row2 val21
row2 val22
row2 val23
...
</pre><p>
which we wish to display like
</p><pre class="programlisting">
row1 val11 val12 val13 ...
row2 val21 val22 val23 ...
...
</pre><p>
The <code class="function">crosstab</code> function takes a text parameter that is an SQL
query producing raw data formatted in the first way, and produces a table
formatted in the second way.
</p><p>
The <em class="parameter"><code>sql</code></em> parameter is an SQL statement that produces
the source set of data. This statement must return one
<code class="structfield">row_name</code> column, one
<code class="structfield">category</code> column, and one
<code class="structfield">value</code> column. <em class="parameter"><code>N</code></em> is an
obsolete parameter, ignored if supplied (formerly this had to match the
number of output value columns, but now that is determined by the
calling query).
</p><p>
For example, the provided query might produce a set something like:
</p><pre class="programlisting">
row_name cat value
----------+-------+-------
row1 cat1 val1
row1 cat2 val2
row1 cat3 val3
row1 cat4 val4
row2 cat1 val5
row2 cat2 val6
row2 cat3 val7
row2 cat4 val8
</pre><p>
</p><p>
The <code class="function">crosstab</code> function is declared to return <code class="type">setof
record</code>, so the actual names and types of the output columns must be
defined in the <code class="literal">FROM</code> clause of the calling <code class="command">SELECT</code>
statement, for example:
</p><pre class="programlisting">
SELECT * FROM crosstab('...') AS ct(row_name text, category_1 text, category_2 text);
</pre><p>
This example produces a set something like:
</p><pre class="programlisting">
<== value columns ==>
row_name category_1 category_2
----------+------------+------------
row1 val1 val2
row2 val5 val6
</pre><p>
</p><p>
The <code class="literal">FROM</code> clause must define the output as one
<code class="structfield">row_name</code> column (of the same data type as the first result
column of the SQL query) followed by N <code class="structfield">value</code> columns
(all of the same data type as the third result column of the SQL query).
You can set up as many output value columns as you wish. The names of the
output columns are up to you.
</p><p>
The <code class="function">crosstab</code> function produces one output row for each
consecutive group of input rows with the same
<code class="structfield">row_name</code> value. It fills the output
<code class="structfield">value</code> columns, left to right, with the
<code class="structfield">value</code> fields from these rows. If there
are fewer rows in a group than there are output <code class="structfield">value</code>
columns, the extra output columns are filled with nulls; if there are
more rows, the extra input rows are skipped.
</p><p>
In practice the SQL query should always specify <code class="literal">ORDER BY 1,2</code>
to ensure that the input rows are properly ordered, that is, values with
the same <code class="structfield">row_name</code> are brought together and
correctly ordered within the row. Notice that <code class="function">crosstab</code>
itself does not pay any attention to the second column of the query
result; it's just there to be ordered by, to control the order in which
the third-column values appear across the page.
</p><p>
Here is a complete example:
</p><pre class="programlisting">
CREATE TABLE ct(id SERIAL, rowid TEXT, attribute TEXT, value TEXT);
INSERT INTO ct(rowid, attribute, value) VALUES('test1','att1','val1');
INSERT INTO ct(rowid, attribute, value) VALUES('test1','att2','val2');
INSERT INTO ct(rowid, attribute, value) VALUES('test1','att3','val3');
INSERT INTO ct(rowid, attribute, value) VALUES('test1','att4','val4');
INSERT INTO ct(rowid, attribute, value) VALUES('test2','att1','val5');
INSERT INTO ct(rowid, attribute, value) VALUES('test2','att2','val6');
INSERT INTO ct(rowid, attribute, value) VALUES('test2','att3','val7');
INSERT INTO ct(rowid, attribute, value) VALUES('test2','att4','val8');
SELECT *
FROM crosstab(
'select rowid, attribute, value
from ct
where attribute = ''att2'' or attribute = ''att3''
order by 1,2')
AS ct(row_name text, category_1 text, category_2 text, category_3 text);
row_name | category_1 | category_2 | category_3
----------+------------+------------+------------
test1 | val2 | val3 |
test2 | val6 | val7 |
(2 rows)
</pre><p>
</p><p>
You can avoid always having to write out a <code class="literal">FROM</code> clause to
define the output columns, by setting up a custom crosstab function that
has the desired output row type wired into its definition. This is
described in the next section. Another possibility is to embed the
required <code class="literal">FROM</code> clause in a view definition.
</p><div class="note"><h3 class="title">Note</h3><p>
See also the <code class="command"><a class="link" href="app-psql.html#APP-PSQL-META-COMMANDS-CROSSTABVIEW">\crosstabview</a></code>
command in <span class="application">psql</span>, which provides functionality similar
to <code class="function">crosstab()</code>.
</p></div></div><div class="sect3" id="TABLEFUNC-FUNCTIONS-CROSSTAB-N-TEXT"><div class="titlepage"><div><div><h4 class="title">F.43.1.3. <code class="function">crosstab<em class="replaceable"><code>N</code></em>(text)</code> <a href="#TABLEFUNC-FUNCTIONS-CROSSTAB-N-TEXT" class="id_link">#</a></h4></div></div></div><a id="id-1.11.7.53.5.6.2" class="indexterm"></a><pre class="synopsis">
crosstab<em class="replaceable"><code>N</code></em>(text sql)
</pre><p>
The <code class="function">crosstab<em class="replaceable"><code>N</code></em></code> functions are examples of how
to set up custom wrappers for the general <code class="function">crosstab</code> function,
so that you need not write out column names and types in the calling
<code class="command">SELECT</code> query. The <code class="filename">tablefunc</code> module includes
<code class="function">crosstab2</code>, <code class="function">crosstab3</code>, and
<code class="function">crosstab4</code>, whose output row types are defined as
</p><pre class="programlisting">
CREATE TYPE tablefunc_crosstab_N AS (
row_name TEXT,
category_1 TEXT,
category_2 TEXT,
.
.
.
category_N TEXT
);
</pre><p>
Thus, these functions can be used directly when the input query produces
<code class="structfield">row_name</code> and <code class="structfield">value</code> columns of type
<code class="type">text</code>, and you want 2, 3, or 4 output values columns.
In all other ways they behave exactly as described above for the
general <code class="function">crosstab</code> function.
</p><p>
For instance, the example given in the previous section would also
work as
</p><pre class="programlisting">
SELECT *
FROM crosstab3(
'select rowid, attribute, value
from ct
where attribute = ''att2'' or attribute = ''att3''
order by 1,2');
</pre><p>
</p><p>
These functions are provided mostly for illustration purposes. You
can create your own return types and functions based on the
underlying <code class="function">crosstab()</code> function. There are two ways
to do it:
</p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
Create a composite type describing the desired output columns,
similar to the examples in
<code class="filename">contrib/tablefunc/tablefunc--1.0.sql</code>.
Then define a
unique function name accepting one <code class="type">text</code> parameter and returning
<code class="type">setof your_type_name</code>, but linking to the same underlying
<code class="function">crosstab</code> C function. For example, if your source data
produces row names that are <code class="type">text</code>, and values that are
<code class="type">float8</code>, and you want 5 value columns:
</p><pre class="programlisting">
CREATE TYPE my_crosstab_float8_5_cols AS (
my_row_name text,
my_category_1 float8,
my_category_2 float8,
my_category_3 float8,
my_category_4 float8,
my_category_5 float8
);
CREATE OR REPLACE FUNCTION crosstab_float8_5_cols(text)
RETURNS setof my_crosstab_float8_5_cols
AS '$libdir/tablefunc','crosstab' LANGUAGE C STABLE STRICT;
</pre><p>
</p></li><li class="listitem"><p>
Use <code class="literal">OUT</code> parameters to define the return type implicitly.
The same example could also be done this way:
</p><pre class="programlisting">
CREATE OR REPLACE FUNCTION crosstab_float8_5_cols(
IN text,
OUT my_row_name text,
OUT my_category_1 float8,
OUT my_category_2 float8,
OUT my_category_3 float8,
OUT my_category_4 float8,
OUT my_category_5 float8)
RETURNS setof record
AS '$libdir/tablefunc','crosstab' LANGUAGE C STABLE STRICT;
</pre><p>
</p></li></ul></div><p>
</p></div><div class="sect3" id="TABLEFUNC-FUNCTIONS-CROSSTAB-TEXT-2"><div class="titlepage"><div><div><h4 class="title">F.43.1.4. <code class="function">crosstab(text, text)</code> <a href="#TABLEFUNC-FUNCTIONS-CROSSTAB-TEXT-2" class="id_link">#</a></h4></div></div></div><a id="id-1.11.7.53.5.7.2" class="indexterm"></a><pre class="synopsis">
crosstab(text source_sql, text category_sql)
</pre><p>
The main limitation of the single-parameter form of <code class="function">crosstab</code>
is that it treats all values in a group alike, inserting each value into
the first available column. If you want the value
columns to correspond to specific categories of data, and some groups
might not have data for some of the categories, that doesn't work well.
The two-parameter form of <code class="function">crosstab</code> handles this case by
providing an explicit list of the categories corresponding to the
output columns.
</p><p>
<em class="parameter"><code>source_sql</code></em> is an SQL statement that produces the
source set of data. This statement must return one
<code class="structfield">row_name</code> column, one
<code class="structfield">category</code> column, and one
<code class="structfield">value</code> column. It may also have one or more
<span class="quote">“<span class="quote">extra</span>”</span> columns.
The <code class="structfield">row_name</code> column must be first. The
<code class="structfield">category</code> and <code class="structfield">value</code>
columns must be the last two columns, in that order. Any columns between
<code class="structfield">row_name</code> and
<code class="structfield">category</code> are treated as <span class="quote">“<span class="quote">extra</span>”</span>.
The <span class="quote">“<span class="quote">extra</span>”</span> columns are expected to be the same for all rows
with the same <code class="structfield">row_name</code> value.
</p><p>
For example, <em class="parameter"><code>source_sql</code></em> might produce a set
something like:
</p><pre class="programlisting">
SELECT row_name, extra_col, cat, value FROM foo ORDER BY 1;
row_name extra_col cat value
----------+------------+-----+---------
row1 extra1 cat1 val1
row1 extra1 cat2 val2
row1 extra1 cat4 val4
row2 extra2 cat1 val5
row2 extra2 cat2 val6
row2 extra2 cat3 val7
row2 extra2 cat4 val8
</pre><p>
</p><p>
<em class="parameter"><code>category_sql</code></em> is an SQL statement that produces
the set of categories. This statement must return only one column.
It must produce at least one row, or an error will be generated.
Also, it must not produce duplicate values, or an error will be
generated. <em class="parameter"><code>category_sql</code></em> might be something like:
</p><pre class="programlisting">
SELECT DISTINCT cat FROM foo ORDER BY 1;
cat
-------
cat1
cat2
cat3
cat4
</pre><p>
</p><p>
The <code class="function">crosstab</code> function is declared to return <code class="type">setof
record</code>, so the actual names and types of the output columns must be
defined in the <code class="literal">FROM</code> clause of the calling <code class="command">SELECT</code>
statement, for example:
</p><pre class="programlisting">
SELECT * FROM crosstab('...', '...')
AS ct(row_name text, extra text, cat1 text, cat2 text, cat3 text, cat4 text);
</pre><p>
</p><p>
This will produce a result something like:
</p><pre class="programlisting">
<== value columns ==>
row_name extra cat1 cat2 cat3 cat4
---------+-------+------+------+------+------
row1 extra1 val1 val2 val4
row2 extra2 val5 val6 val7 val8
</pre><p>
</p><p>
The <code class="literal">FROM</code> clause must define the proper number of output
columns of the proper data types. If there are <em class="replaceable"><code>N</code></em>
columns in the <em class="parameter"><code>source_sql</code></em> query's result, the first
<em class="replaceable"><code>N</code></em>-2 of them must match up with the first
<em class="replaceable"><code>N</code></em>-2 output columns. The remaining output columns
must have the type of the last column of the <em class="parameter"><code>source_sql</code></em>
query's result, and there must be exactly as many of them as there
are rows in the <em class="parameter"><code>category_sql</code></em> query's result.
</p><p>
The <code class="function">crosstab</code> function produces one output row for each
consecutive group of input rows with the same
<code class="structfield">row_name</code> value. The output
<code class="structfield">row_name</code> column, plus any <span class="quote">“<span class="quote">extra</span>”</span>
columns, are copied from the first row of the group. The output
<code class="structfield">value</code> columns are filled with the
<code class="structfield">value</code> fields from rows having matching
<code class="structfield">category</code> values. If a row's <code class="structfield">category</code>
does not match any output of the <em class="parameter"><code>category_sql</code></em>
query, its <code class="structfield">value</code> is ignored. Output
columns whose matching category is not present in any input row
of the group are filled with nulls.
</p><p>
In practice the <em class="parameter"><code>source_sql</code></em> query should always
specify <code class="literal">ORDER BY 1</code> to ensure that values with the same
<code class="structfield">row_name</code> are brought together. However,
ordering of the categories within a group is not important.
Also, it is essential to be sure that the order of the
<em class="parameter"><code>category_sql</code></em> query's output matches the specified
output column order.
</p><p>
Here are two complete examples:
</p><pre class="programlisting">
create table sales(year int, month int, qty int);
insert into sales values(2007, 1, 1000);
insert into sales values(2007, 2, 1500);
insert into sales values(2007, 7, 500);
insert into sales values(2007, 11, 1500);
insert into sales values(2007, 12, 2000);
insert into sales values(2008, 1, 1000);
select * from crosstab(
'select year, month, qty from sales order by 1',
'select m from generate_series(1,12) m'
) as (
year int,
"Jan" int,
"Feb" int,
"Mar" int,
"Apr" int,
"May" int,
"Jun" int,
"Jul" int,
"Aug" int,
"Sep" int,
"Oct" int,
"Nov" int,
"Dec" int
);
year | Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec
------+------+------+-----+-----+-----+-----+-----+-----+-----+-----+------+------
2007 | 1000 | 1500 | | | | | 500 | | | | 1500 | 2000
2008 | 1000 | | | | | | | | | | |
(2 rows)
</pre><p>
</p><pre class="programlisting">
CREATE TABLE cth(rowid text, rowdt timestamp, attribute text, val text);
INSERT INTO cth VALUES('test1','01 March 2003','temperature','42');
INSERT INTO cth VALUES('test1','01 March 2003','test_result','PASS');
INSERT INTO cth VALUES('test1','01 March 2003','volts','2.6987');
INSERT INTO cth VALUES('test2','02 March 2003','temperature','53');
INSERT INTO cth VALUES('test2','02 March 2003','test_result','FAIL');
INSERT INTO cth VALUES('test2','02 March 2003','test_startdate','01 March 2003');
INSERT INTO cth VALUES('test2','02 March 2003','volts','3.1234');
SELECT * FROM crosstab
(
'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1',
'SELECT DISTINCT attribute FROM cth ORDER BY 1'
)
AS
(
rowid text,
rowdt timestamp,
temperature int4,
test_result text,
test_startdate timestamp,
volts float8
);
rowid | rowdt | temperature | test_result | test_startdate | volts
-------+--------------------------+-------------+-------------+--------------------------+--------
test1 | Sat Mar 01 00:00:00 2003 | 42 | PASS | | 2.6987
test2 | Sun Mar 02 00:00:00 2003 | 53 | FAIL | Sat Mar 01 00:00:00 2003 | 3.1234
(2 rows)
</pre><p>
</p><p>
You can create predefined functions to avoid having to write out
the result column names and types in each query. See the examples
in the previous section. The underlying C function for this form
of <code class="function">crosstab</code> is named <code class="literal">crosstab_hash</code>.
</p></div><div class="sect3" id="TABLEFUNC-FUNCTIONS-CONNECTBY"><div class="titlepage"><div><div><h4 class="title">F.43.1.5. <code class="function">connectby</code> <a href="#TABLEFUNC-FUNCTIONS-CONNECTBY" class="id_link">#</a></h4></div></div></div><a id="id-1.11.7.53.5.8.2" class="indexterm"></a><pre class="synopsis">
connectby(text relname, text keyid_fld, text parent_keyid_fld
[, text orderby_fld ], text start_with, int max_depth
[, text branch_delim ])
</pre><p>
The <code class="function">connectby</code> function produces a display of hierarchical
data that is stored in a table. The table must have a key field that
uniquely identifies rows, and a parent-key field that references the
parent (if any) of each row. <code class="function">connectby</code> can display the
sub-tree descending from any row.
</p><p>
<a class="xref" href="tablefunc.html#TABLEFUNC-CONNECTBY-PARAMETERS" title="Table F.33. connectby Parameters">Table F.33</a> explains the
parameters.
</p><div class="table" id="TABLEFUNC-CONNECTBY-PARAMETERS"><p class="title"><strong>Table F.33. <code class="function">connectby</code> Parameters</strong></p><div class="table-contents"><table class="table" summary="connectby Parameters" border="1"><colgroup><col /><col /></colgroup><thead><tr><th>Parameter</th><th>Description</th></tr></thead><tbody><tr><td><em class="parameter"><code>relname</code></em></td><td>Name of the source relation</td></tr><tr><td><em class="parameter"><code>keyid_fld</code></em></td><td>Name of the key field</td></tr><tr><td><em class="parameter"><code>parent_keyid_fld</code></em></td><td>Name of the parent-key field</td></tr><tr><td><em class="parameter"><code>orderby_fld</code></em></td><td>Name of the field to order siblings by (optional)</td></tr><tr><td><em class="parameter"><code>start_with</code></em></td><td>Key value of the row to start at</td></tr><tr><td><em class="parameter"><code>max_depth</code></em></td><td>Maximum depth to descend to, or zero for unlimited depth</td></tr><tr><td><em class="parameter"><code>branch_delim</code></em></td><td>String to separate keys with in branch output (optional)</td></tr></tbody></table></div></div><br class="table-break" /><p>
The key and parent-key fields can be any data type, but they must be
the same type. Note that the <em class="parameter"><code>start_with</code></em> value must be
entered as a text string, regardless of the type of the key field.
</p><p>
The <code class="function">connectby</code> function is declared to return <code class="type">setof
record</code>, so the actual names and types of the output columns must be
defined in the <code class="literal">FROM</code> clause of the calling <code class="command">SELECT</code>
statement, for example:
</p><pre class="programlisting">
SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'pos', 'row2', 0, '~')
AS t(keyid text, parent_keyid text, level int, branch text, pos int);
</pre><p>
The first two output columns are used for the current row's key and
its parent row's key; they must match the type of the table's key field.
The third output column is the depth in the tree and must be of type
<code class="type">integer</code>. If a <em class="parameter"><code>branch_delim</code></em> parameter was
given, the next output column is the branch display and must be of type
<code class="type">text</code>. Finally, if an <em class="parameter"><code>orderby_fld</code></em>
parameter was given, the last output column is a serial number, and must
be of type <code class="type">integer</code>.
</p><p>
The <span class="quote">“<span class="quote">branch</span>”</span> output column shows the path of keys taken to
reach the current row. The keys are separated by the specified
<em class="parameter"><code>branch_delim</code></em> string. If no branch display is
wanted, omit both the <em class="parameter"><code>branch_delim</code></em> parameter
and the branch column in the output column list.
</p><p>
If the ordering of siblings of the same parent is important,
include the <em class="parameter"><code>orderby_fld</code></em> parameter to
specify which field to order siblings by. This field can be of any
sortable data type. The output column list must include a final
integer serial-number column, if and only if
<em class="parameter"><code>orderby_fld</code></em> is specified.
</p><p>
The parameters representing table and field names are copied as-is
into the SQL queries that <code class="function">connectby</code> generates internally.
Therefore, include double quotes if the names are mixed-case or contain
special characters. You may also need to schema-qualify the table name.
</p><p>
In large tables, performance will be poor unless there is an index on
the parent-key field.
</p><p>
It is important that the <em class="parameter"><code>branch_delim</code></em> string
not appear in any key values, else <code class="function">connectby</code> may incorrectly
report an infinite-recursion error. Note that if
<em class="parameter"><code>branch_delim</code></em> is not provided, a default value
of <code class="literal">~</code> is used for recursion detection purposes.
</p><p>
Here is an example:
</p><pre class="programlisting">
CREATE TABLE connectby_tree(keyid text, parent_keyid text, pos int);
INSERT INTO connectby_tree VALUES('row1',NULL, 0);
INSERT INTO connectby_tree VALUES('row2','row1', 0);
INSERT INTO connectby_tree VALUES('row3','row1', 0);
INSERT INTO connectby_tree VALUES('row4','row2', 1);
INSERT INTO connectby_tree VALUES('row5','row2', 0);
INSERT INTO connectby_tree VALUES('row6','row4', 0);
INSERT INTO connectby_tree VALUES('row7','row3', 0);
INSERT INTO connectby_tree VALUES('row8','row6', 0);
INSERT INTO connectby_tree VALUES('row9','row5', 0);
-- with branch, without orderby_fld (order of results is not guaranteed)
SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'row2', 0, '~')
AS t(keyid text, parent_keyid text, level int, branch text);
keyid | parent_keyid | level | branch
-------+--------------+-------+---------------------
row2 | | 0 | row2
row4 | row2 | 1 | row2~row4
row6 | row4 | 2 | row2~row4~row6
row8 | row6 | 3 | row2~row4~row6~row8
row5 | row2 | 1 | row2~row5
row9 | row5 | 2 | row2~row5~row9
(6 rows)
-- without branch, without orderby_fld (order of results is not guaranteed)
SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'row2', 0)
AS t(keyid text, parent_keyid text, level int);
keyid | parent_keyid | level
-------+--------------+-------
row2 | | 0
row4 | row2 | 1
row6 | row4 | 2
row8 | row6 | 3
row5 | row2 | 1
row9 | row5 | 2
(6 rows)
-- with branch, with orderby_fld (notice that row5 comes before row4)
SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'pos', 'row2', 0, '~')
AS t(keyid text, parent_keyid text, level int, branch text, pos int);
keyid | parent_keyid | level | branch | pos
-------+--------------+-------+---------------------+-----
row2 | | 0 | row2 | 1
row5 | row2 | 1 | row2~row5 | 2
row9 | row5 | 2 | row2~row5~row9 | 3
row4 | row2 | 1 | row2~row4 | 4
row6 | row4 | 2 | row2~row4~row6 | 5
row8 | row6 | 3 | row2~row4~row6~row8 | 6
(6 rows)
-- without branch, with orderby_fld (notice that row5 comes before row4)
SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'pos', 'row2', 0)
AS t(keyid text, parent_keyid text, level int, pos int);
keyid | parent_keyid | level | pos
-------+--------------+-------+-----
row2 | | 0 | 1
row5 | row2 | 1 | 2
row9 | row5 | 2 | 3
row4 | row2 | 1 | 4
row6 | row4 | 2 | 5
row8 | row6 | 3 | 6
(6 rows)
</pre><p>
</p></div></div><div class="sect2" id="TABLEFUNC-AUTHOR"><div class="titlepage"><div><div><h3 class="title">F.43.2. Author <a href="#TABLEFUNC-AUTHOR" class="id_link">#</a></h3></div></div></div><p>
Joe Conway
</p></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="sslinfo.html" title="F.42. sslinfo — obtain client SSL information">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="contrib.html" title="Appendix F. Additional Supplied Modules and Extensions">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="tcn.html" title="F.44. tcn — a trigger function to notify listeners of changes to table content">Next</a></td></tr><tr><td width="40%" align="left" valign="top">F.42. sslinfo — obtain client SSL information </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 16.2 Documentation">Home</a></td><td width="40%" align="right" valign="top"> F.44. tcn — a trigger function to notify listeners of changes to table content</td></tr></table></div></body></html>
|
