4
* Sample to demonstrate C functions which return setof scalar
6
* Joe Conway <mail@joeconway.com>
8
* Nabil Sayegh <postgresql@e-trolley.de>
10
* Copyright (c) 2002-2005, PostgreSQL Global Development Group
12
* Permission to use, copy, modify, and distribute this software and its
13
* documentation for any purpose, without fee, and without a written agreement
14
* is hereby granted, provided that the above copyright notice and this
15
* paragraph and the following two paragraphs appear in all copies.
17
* IN NO EVENT SHALL THE AUTHORS OR DISTRIBUTORS BE LIABLE TO ANY PARTY FOR
18
* DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING
19
* LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS
20
* DOCUMENTATION, EVEN IF THE AUTHOR OR DISTRIBUTORS HAVE BEEN ADVISED OF THE
21
* POSSIBILITY OF SUCH DAMAGE.
23
* THE AUTHORS AND DISTRIBUTORS SPECIFICALLY DISCLAIM ANY WARRANTIES,
24
* INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY
25
* AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS
26
* ON AN "AS IS" BASIS, AND THE AUTHOR AND DISTRIBUTORS HAS NO OBLIGATIONS TO
27
* PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.
30
Version 0.1 (20 July, 2002):
39
Place these files in a directory called 'tablefunc' under 'contrib' in the
40
PostgreSQL source tree. Then run:
45
You can use tablefunc.sql to create the functions in your database of choice, e.g.
47
psql -U postgres template1 < tablefunc.sql
49
installs following functions into database template1:
51
normal_rand(int numvals, float8 mean, float8 stddev)
52
- returns a set of normally distributed float8 values
55
- returns a set of row_name plus N category value columns
56
- crosstab2(), crosstab3(), and crosstab4() are defined for you,
57
but you can create additional crosstab functions per the instructions
58
in the documentation below.
60
crosstab(text sql, N int)
61
- returns a set of row_name plus N category value columns
62
- requires anonymous composite type syntax in the FROM clause. See
63
the instructions in the documentation below.
65
connectby(text relname, text keyid_fld, text parent_keyid_fld
66
[, text orderby_fld], text start_with, int max_depth
67
[, text branch_delim])
68
- returns keyid, parent_keyid, level, and an optional branch string
69
and an optional serial column for ordering siblings
70
- requires anonymous composite type syntax in the FROM clause. See
71
the instructions in the documentation below.
74
==================================================================
77
normal_rand(int, float8, float8) - returns a set of normally
78
distributed float8 values
82
normal_rand(int numvals, float8 mean, float8 stddev)
87
the number of random values to be returned from the function
90
the mean of the normal distribution of values
93
the standard deviation of the normal distribution of values
97
Returns setof float8, where the returned set of random values are normally
98
distributed (Gaussian distribution)
103
test=# normal_rand(1000, 5, 3);
105
----------------------
119
Returns 1000 values with a mean of 5 and a standard deviation of 3.
121
==================================================================
124
crosstabN(text) - returns a set of row_name plus N category value columns
134
A SQL statement which produces the source set of data. The SQL statement
135
must return one row_name column, one category column, and one value
138
e.g. provided sql must produce a set something like:
141
----------+-------+-------
153
Returns setof tablefunc_crosstab_N, which is defined by:
155
CREATE VIEW tablefunc_crosstab_N AS
157
''::TEXT AS row_name,
158
''::TEXT AS category_1,
159
''::TEXT AS category_2,
163
''::TEXT AS category_N;
165
for the default installed functions, where N is 2, 3, or 4.
167
e.g. the provided crosstab2 function produces a set something like:
168
<== values columns ==>
169
row_name category_1 category_2
170
---------+------------+------------
176
1. The sql result must be ordered by 1,2.
178
2. The number of values columns depends on the tuple description
179
of the function's declared return type.
181
3. Missing values (i.e. not enough adjacent rows of same row_name to
182
fill the number of result values columns) are filled in with nulls.
184
4. Extra values (i.e. too many adjacent rows of same row_name to fill
185
the number of result values columns) are skipped.
187
5. Rows with all nulls in the values columns are skipped.
189
6. The installed defaults are for illustration purposes. You
190
can create your own return types and functions based on the
191
crosstab() function of the installed library.
193
The return type must have a first column that matches the data
194
type of the sql set used as its source. The subsequent category
195
columns must have the same data type as the value column of the
198
Create a VIEW to define your return type, similar to the VIEWS
199
in the provided installation script. Then define a unique function
200
name accepting one text parameter and returning setof your_view_name.
201
For example, if your source data produces row_names that are TEXT,
202
and values that are FLOAT8, and you want 5 category columns:
204
CREATE VIEW my_crosstab_float8_5_cols AS
206
''::TEXT AS row_name,
207
0::FLOAT8 AS category_1,
208
0::FLOAT8 AS category_2,
209
0::FLOAT8 AS category_3,
210
0::FLOAT8 AS category_4,
211
0::FLOAT8 AS category_5;
213
CREATE OR REPLACE FUNCTION crosstab_float8_5_cols(text)
214
RETURNS setof my_crosstab_float8_5_cols
215
AS '$libdir/tablefunc','crosstab' LANGUAGE 'c' STABLE STRICT;
219
create table ct(id serial, rowclass text, rowid text, attribute text, value text);
220
insert into ct(rowclass, rowid, attribute, value) values('group1','test1','att1','val1');
221
insert into ct(rowclass, rowid, attribute, value) values('group1','test1','att2','val2');
222
insert into ct(rowclass, rowid, attribute, value) values('group1','test1','att3','val3');
223
insert into ct(rowclass, rowid, attribute, value) values('group1','test1','att4','val4');
224
insert into ct(rowclass, rowid, attribute, value) values('group1','test2','att1','val5');
225
insert into ct(rowclass, rowid, attribute, value) values('group1','test2','att2','val6');
226
insert into ct(rowclass, rowid, attribute, value) values('group1','test2','att3','val7');
227
insert into ct(rowclass, rowid, attribute, value) values('group1','test2','att4','val8');
229
select * from crosstab3(
230
'select rowid, attribute, value
232
where rowclass = ''group1''
233
and (attribute = ''att2'' or attribute = ''att3'') order by 1,2;');
235
row_name | category_1 | category_2 | category_3
236
----------+------------+------------+------------
237
test1 | val2 | val3 |
238
test2 | val6 | val7 |
241
==================================================================
244
crosstab(text, int) - returns a set of row_name
245
plus N category value columns
249
crosstab(text sql, int N)
255
A SQL statement which produces the source set of data. The SQL statement
256
must return one row_name column, one category column, and one value
259
e.g. provided sql must produce a set something like:
262
----------+-------+-------
274
number of category value columns
278
Returns setof record, which must defined with a column definition
279
in the FROM clause of the SELECT statement, e.g.:
282
FROM crosstab(sql, 2) AS ct(row_name text, category_1 text, category_2 text);
284
the example crosstab function produces a set something like:
285
<== values columns ==>
286
row_name category_1 category_2
287
---------+------------+------------
293
1. The sql result must be ordered by 1,2.
295
2. The number of values columns is determined at run-time. The
296
column definition provided in the FROM clause must provide for
297
N + 1 columns of the proper data types.
299
3. Missing values (i.e. not enough adjacent rows of same row_name to
300
fill the number of result values columns) are filled in with nulls.
302
4. Extra values (i.e. too many adjacent rows of same row_name to fill
303
the number of result values columns) are skipped.
305
5. Rows with all nulls in the values columns are skipped.
310
create table ct(id serial, rowclass text, rowid text, attribute text, value text);
311
insert into ct(rowclass, rowid, attribute, value) values('group1','test1','att1','val1');
312
insert into ct(rowclass, rowid, attribute, value) values('group1','test1','att2','val2');
313
insert into ct(rowclass, rowid, attribute, value) values('group1','test1','att3','val3');
314
insert into ct(rowclass, rowid, attribute, value) values('group1','test1','att4','val4');
315
insert into ct(rowclass, rowid, attribute, value) values('group1','test2','att1','val5');
316
insert into ct(rowclass, rowid, attribute, value) values('group1','test2','att2','val6');
317
insert into ct(rowclass, rowid, attribute, value) values('group1','test2','att3','val7');
318
insert into ct(rowclass, rowid, attribute, value) values('group1','test2','att4','val8');
322
'select rowid, attribute, value
324
where rowclass = ''group1''
325
and (attribute = ''att2'' or attribute = ''att3'') order by 1,2;', 3)
326
AS ct(row_name text, category_1 text, category_2 text, category_3 text);
328
row_name | category_1 | category_2 | category_3
329
----------+------------+------------+------------
330
test1 | val2 | val3 |
331
test2 | val6 | val7 |
334
==================================================================
337
crosstab(text, text) - returns a set of row_name, extra, and
338
category value columns
342
crosstab(text source_sql, text category_sql)
348
A SQL statement which produces the source set of data. The SQL statement
349
must return one row_name column, one category column, and one value
350
column. It may also have one or more "extra" columns.
352
The row_name column must be first. The category and value columns
353
must be the last two columns, in that order. "extra" columns must be
354
columns 2 through (N - 2), where N is the total number of columns.
356
The "extra" columns are assumed to be the same for all rows with the
357
same row_name. The values returned are copied from the first row
358
with a given row_name and subsequent values of these columns are ignored
359
until row_name changes.
361
e.g. source_sql must produce a set something like:
362
SELECT row_name, extra_col, cat, value FROM foo;
364
row_name extra_col cat value
365
----------+------------+-----+---------
366
row1 extra1 cat1 val1
367
row1 extra1 cat2 val2
368
row1 extra1 cat4 val4
369
row2 extra2 cat1 val5
370
row2 extra2 cat2 val6
371
row2 extra2 cat3 val7
372
row2 extra2 cat4 val8
376
A SQL statement which produces the distinct set of categories. The SQL
377
statement must return one category column only. category_sql must produce
378
at least one result row or an error will be generated. category_sql
379
must not produce duplicate categories or an error will be generated.
381
e.g. SELECT DISTINCT cat FROM foo;
392
Returns setof record, which must be defined with a column definition
393
in the FROM clause of the SELECT statement, e.g.:
395
SELECT * FROM crosstab(source_sql, cat_sql)
396
AS ct(row_name text, extra text, cat1 text, cat2 text, cat3 text, cat4 text);
398
the example crosstab function produces a set something like:
399
<== values columns ==>
400
row_name extra cat1 cat2 cat3 cat4
401
---------+-------+------+------+------+------
402
row1 extra1 val1 val2 val4
403
row2 extra2 val5 val6 val7 val8
407
1. source_sql must be ordered by row_name (column 1).
409
2. The number of values columns is determined at run-time. The
410
column definition provided in the FROM clause must provide for
411
the correct number of columns of the proper data types.
413
3. Missing values (i.e. not enough adjacent rows of same row_name to
414
fill the number of result values columns) are filled in with nulls.
416
4. Extra values (i.e. source rows with category not found in category_sql
419
5. Rows with a null row_name column are skipped.
424
create table cth(id serial, rowid text, rowdt timestamp, attribute text, val text);
425
insert into cth values(DEFAULT,'test1','01 March 2003','temperature','42');
426
insert into cth values(DEFAULT,'test1','01 March 2003','test_result','PASS');
427
insert into cth values(DEFAULT,'test1','01 March 2003','volts','2.6987');
428
insert into cth values(DEFAULT,'test2','02 March 2003','temperature','53');
429
insert into cth values(DEFAULT,'test2','02 March 2003','test_result','FAIL');
430
insert into cth values(DEFAULT,'test2','02 March 2003','test_startdate','01 March 2003');
431
insert into cth values(DEFAULT,'test2','02 March 2003','volts','3.1234');
433
SELECT * FROM crosstab
435
'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1',
436
'SELECT DISTINCT attribute FROM cth ORDER BY 1'
444
test_startdate timestamp,
447
rowid | rowdt | temperature | test_result | test_startdate | volts
448
-------+--------------------------+-------------+-------------+--------------------------+--------
449
test1 | Sat Mar 01 00:00:00 2003 | 42 | PASS | | 2.6987
450
test2 | Sun Mar 02 00:00:00 2003 | 53 | FAIL | Sat Mar 01 00:00:00 2003 | 3.1234
453
==================================================================
456
connectby(text, text, text[, text], text, text, int[, text]) - returns a set
457
representing a hierarchy (tree structure)
461
connectby(text relname, text keyid_fld, text parent_keyid_fld
462
[, text orderby_fld], text start_with, int max_depth
463
[, text branch_delim])
469
Name of the source relation
473
Name of the key field
477
Name of the key_parent field
481
If optional ordering of siblings is desired:
482
Name of the field to order siblings
486
root value of the tree input as a text value regardless of keyid_fld type
490
zero (0) for unlimited depth, otherwise restrict level to this depth
494
If optional branch value is desired, this string is used as the delimiter.
495
When not provided, a default value of '~' is used for internal
496
recursion detection only, and no "branch" field is returned.
500
Returns setof record, which must defined with a column definition
501
in the FROM clause of the SELECT statement, e.g.:
503
SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'row2', 0, '~')
504
AS t(keyid text, parent_keyid text, level int, branch text);
508
SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'row2', 0)
509
AS t(keyid text, parent_keyid text, level int);
513
SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'pos', 'row2', 0, '~')
514
AS t(keyid text, parent_keyid text, level int, branch text, pos int);
518
SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'pos', 'row2', 0)
519
AS t(keyid text, parent_keyid text, level int, pos int);
523
1. keyid and parent_keyid must be the same data type
525
2. The column definition *must* include a third column of type INT4 for
526
the level value output
528
3. If the branch field is not desired, omit both the branch_delim input
529
parameter *and* the branch field in the query column definition. Note
530
that when branch_delim is not provided, a default value of '~' is used
531
for branch_delim for internal recursion detection, even though the branch
532
field is not returned.
534
4. If the branch field is desired, it must be the fourth column in the query
535
column definition, and it must be type TEXT.
537
5. The parameters representing table and field names must include double
538
quotes if the names are mixed-case or contain special characters.
540
6. If sorting of siblings is desired, the orderby_fld input parameter *and*
541
a name for the resulting serial field (type INT32) in the query column
542
definition must be given.
546
CREATE TABLE connectby_tree(keyid text, parent_keyid text, pos int);
548
INSERT INTO connectby_tree VALUES('row1',NULL, 0);
549
INSERT INTO connectby_tree VALUES('row2','row1', 0);
550
INSERT INTO connectby_tree VALUES('row3','row1', 0);
551
INSERT INTO connectby_tree VALUES('row4','row2', 1);
552
INSERT INTO connectby_tree VALUES('row5','row2', 0);
553
INSERT INTO connectby_tree VALUES('row6','row4', 0);
554
INSERT INTO connectby_tree VALUES('row7','row3', 0);
555
INSERT INTO connectby_tree VALUES('row8','row6', 0);
556
INSERT INTO connectby_tree VALUES('row9','row5', 0);
558
-- with branch, without orderby_fld
559
SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'row2', 0, '~')
560
AS t(keyid text, parent_keyid text, level int, branch text);
561
keyid | parent_keyid | level | branch
562
-------+--------------+-------+---------------------
564
row4 | row2 | 1 | row2~row4
565
row6 | row4 | 2 | row2~row4~row6
566
row8 | row6 | 3 | row2~row4~row6~row8
567
row5 | row2 | 1 | row2~row5
568
row9 | row5 | 2 | row2~row5~row9
571
-- without branch, without orderby_fld
572
SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'row2', 0)
573
AS t(keyid text, parent_keyid text, level int);
574
keyid | parent_keyid | level
575
-------+--------------+-------
584
-- with branch, with orderby_fld (notice that row5 comes before row4)
585
SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'pos', 'row2', 0, '~')
586
AS t(keyid text, parent_keyid text, level int, branch text, pos int) ORDER BY t.pos;
587
keyid | parent_keyid | level | branch | pos
588
-------+--------------+-------+---------------------+-----
589
row2 | | 0 | row2 | 1
590
row5 | row2 | 1 | row2~row5 | 2
591
row9 | row5 | 2 | row2~row5~row9 | 3
592
row4 | row2 | 1 | row2~row4 | 4
593
row6 | row4 | 2 | row2~row4~row6 | 5
594
row8 | row6 | 3 | row2~row4~row6~row8 | 6
597
-- without branch, with orderby_fld (notice that row5 comes before row4)
598
SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'pos', 'row2', 0)
599
AS t(keyid text, parent_keyid text, level int, pos int) ORDER BY t.pos;
600
keyid | parent_keyid | level | pos
601
-------+--------------+-------+-----
610
==================================================================
added
removed
contrib/tablefunc/README.tablefunc
