diff options
Diffstat (limited to 'contrib/tablefunc/README.tablefunc')
| -rw-r--r-- | contrib/tablefunc/README.tablefunc | 642 |
1 files changed, 0 insertions, 642 deletions
diff --git a/contrib/tablefunc/README.tablefunc b/contrib/tablefunc/README.tablefunc deleted file mode 100644 index c54f53231bb..00000000000 --- a/contrib/tablefunc/README.tablefunc +++ /dev/null @@ -1,642 +0,0 @@ -/* - * tablefunc - * - * Sample to demonstrate C functions which return setof scalar - * and setof composite. - * Joe Conway <[email protected]> - * And contributors: - * Nabil Sayegh <[email protected]> - * - * Copyright (c) 2002-2007, PostgreSQL Global Development Group - * - * Permission to use, copy, modify, and distribute this software and its - * documentation for any purpose, without fee, and without a written agreement - * is hereby granted, provided that the above copyright notice and this - * paragraph and the following two paragraphs appear in all copies. - * - * IN NO EVENT SHALL THE AUTHORS OR DISTRIBUTORS BE LIABLE TO ANY PARTY FOR - * DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING - * LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS - * DOCUMENTATION, EVEN IF THE AUTHOR OR DISTRIBUTORS HAVE BEEN ADVISED OF THE - * POSSIBILITY OF SUCH DAMAGE. - * - * THE AUTHORS AND DISTRIBUTORS SPECIFICALLY DISCLAIM ANY WARRANTIES, - * INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY - * AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS - * ON AN "AS IS" BASIS, AND THE AUTHOR AND DISTRIBUTORS HAS NO OBLIGATIONS TO - * PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS. - * - */ -Version 0.1 (20 July, 2002): - First release - -Release Notes: - - Version 0.1 - - initial release - -Installation: - Place these files in a directory called 'tablefunc' under 'contrib' in the - PostgreSQL source tree. Then run: - - make - make install - - You can use tablefunc.sql to create the functions in your database of choice, e.g. - - psql -U postgres template1 < tablefunc.sql - - installs following functions into database template1: - - normal_rand(int numvals, float8 mean, float8 stddev) - - returns a set of normally distributed float8 values - - crosstabN(text sql) - - returns a set of row_name plus N category value columns - - crosstab2(), crosstab3(), and crosstab4() are defined for you, - but you can create additional crosstab functions per the instructions - in the documentation below. - - crosstab(text sql) - - returns a set of row_name plus N category value columns - - requires anonymous composite type syntax in the FROM clause. See - the instructions in the documentation below. - - crosstab(text sql, N int) - - obsolete version of crosstab() - - the argument N is now ignored, since the number of value columns - is always determined by the calling query - - connectby(text relname, text keyid_fld, text parent_keyid_fld - [, text orderby_fld], text start_with, int max_depth - [, text branch_delim]) - - returns keyid, parent_keyid, level, and an optional branch string - and an optional serial column for ordering siblings - - requires anonymous composite type syntax in the FROM clause. See - the instructions in the documentation below. - -Documentation -================================================================== -Name - -normal_rand(int, float8, float8) - returns a set of normally - distributed float8 values - -Synopsis - -normal_rand(int numvals, float8 mean, float8 stddev) - -Inputs - - numvals - the number of random values to be returned from the function - - mean - the mean of the normal distribution of values - - stddev - the standard deviation of the normal distribution of values - -Outputs - - Returns setof float8, where the returned set of random values are normally - distributed (Gaussian distribution) - -Example usage - - test=# SELECT * FROM - test=# 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) - - Returns 1000 values with a mean of 5 and a standard deviation of 3. - -================================================================== -Name - -crosstabN(text) - returns a set of row_name plus N category value columns - -Synopsis - -crosstabN(text sql) - -Inputs - - sql - - A SQL statement which produces the source set of data. The SQL statement - must return one row_name column, one category column, and one value - column. row_name and value must be of type text. - - e.g. provided sql must produce a set something like: - - 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 - -Outputs - - Returns setof tablefunc_crosstab_N, which is defined by: - - CREATE TYPE tablefunc_crosstab_N AS ( - row_name TEXT, - category_1 TEXT, - category_2 TEXT, - . - . - . - category_N TEXT - ); - - for the default installed functions, where N is 2, 3, or 4. - - e.g. the provided crosstab2 function produces a set something like: - <== values columns ==> - row_name category_1 category_2 - ---------+------------+------------ - row1 val1 val2 - row2 val5 val6 - -Notes - - 1. The sql result must be ordered by 1,2. - - 2. The number of values columns depends on the tuple description - of the function's declared return type. - - 3. Missing values (i.e. not enough adjacent rows of same row_name to - fill the number of result values columns) are filled in with nulls. - - 4. Extra values (i.e. too many adjacent rows of same row_name to fill - the number of result values columns) are skipped. - - 5. Rows with all nulls in the values columns are skipped. - - 6. The installed defaults are for illustration purposes. You - can create your own return types and functions based on the - crosstab() function of the installed library. See below for - details. - - -Example usage - -create table ct(id serial, rowclass text, rowid text, attribute text, value text); -insert into ct(rowclass, rowid, attribute, value) values('group1','test1','att1','val1'); -insert into ct(rowclass, rowid, attribute, value) values('group1','test1','att2','val2'); -insert into ct(rowclass, rowid, attribute, value) values('group1','test1','att3','val3'); -insert into ct(rowclass, rowid, attribute, value) values('group1','test1','att4','val4'); -insert into ct(rowclass, rowid, attribute, value) values('group1','test2','att1','val5'); -insert into ct(rowclass, rowid, attribute, value) values('group1','test2','att2','val6'); -insert into ct(rowclass, rowid, attribute, value) values('group1','test2','att3','val7'); -insert into ct(rowclass, rowid, attribute, value) values('group1','test2','att4','val8'); - -select * from crosstab3( - 'select rowid, attribute, value - from ct - where rowclass = ''group1'' - and (attribute = ''att2'' or attribute = ''att3'') order by 1,2;'); - - row_name | category_1 | category_2 | category_3 -----------+------------+------------+------------ - test1 | val2 | val3 | - test2 | val6 | val7 | -(2 rows) - -================================================================== -Name - -crosstab(text) - returns a set of row_names plus category value columns - -Synopsis - -crosstab(text sql) - -crosstab(text sql, int N) - -Inputs - - sql - - A SQL statement which produces the source set of data. The SQL statement - must return one row_name column, one category column, and one value - column. - - e.g. provided sql must produce a set something like: - - 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 - - N - - Obsolete argument; ignored if supplied (formerly this had to match - the number of category columns determined by the calling query) - -Outputs - - Returns setof record, which must be defined with a column definition - in the FROM clause of the SELECT statement, e.g.: - - SELECT * - FROM crosstab(sql) AS ct(row_name text, category_1 text, category_2 text); - - the example crosstab function produces a set something like: - <== values columns ==> - row_name category_1 category_2 - ---------+------------+------------ - row1 val1 val2 - row2 val5 val6 - -Notes - - 1. The sql result must be ordered by 1,2. - - 2. The number of values columns is determined by the column definition - provided in the FROM clause. The FROM clause must define one - row_name column (of the same datatype as the first result column - of the sql query) followed by N category columns (of the same - datatype as the third result column of the sql query). You can - set up as many category columns as you wish. - - 3. Missing values (i.e. not enough adjacent rows of same row_name to - fill the number of result values columns) are filled in with nulls. - - 4. Extra values (i.e. too many adjacent rows of same row_name to fill - the number of result values columns) are skipped. - - 5. Rows with all nulls in the values columns are skipped. - - 6. You can avoid always having to write out a FROM clause that defines the - output columns by setting up a custom crosstab function that has - the desired output row type wired into its definition. - - There are two ways you can set up a custom crosstab function: - - A. Create a composite type to define your return type, similar to the - examples in the installation script. Then define a unique function - name accepting one text parameter and returning setof your_type_name. - For example, if your source data produces row_names that are TEXT, - and values that are FLOAT8, and you want 5 category columns: - - CREATE TYPE my_crosstab_float8_5_cols AS ( - row_name TEXT, - category_1 FLOAT8, - category_2 FLOAT8, - category_3 FLOAT8, - category_4 FLOAT8, - 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; - - B. Use OUT parameters to define the return type implicitly. - The same example could also be done this way: - - CREATE OR REPLACE FUNCTION crosstab_float8_5_cols(IN text, - OUT row_name TEXT, - OUT category_1 FLOAT8, - OUT category_2 FLOAT8, - OUT category_3 FLOAT8, - OUT category_4 FLOAT8, - OUT category_5 FLOAT8) - RETURNS setof record - AS '$libdir/tablefunc','crosstab' LANGUAGE C STABLE STRICT; - - -Example usage - -create table ct(id serial, rowclass text, rowid text, attribute text, value text); -insert into ct(rowclass, rowid, attribute, value) values('group1','test1','att1','val1'); -insert into ct(rowclass, rowid, attribute, value) values('group1','test1','att2','val2'); -insert into ct(rowclass, rowid, attribute, value) values('group1','test1','att3','val3'); -insert into ct(rowclass, rowid, attribute, value) values('group1','test1','att4','val4'); -insert into ct(rowclass, rowid, attribute, value) values('group1','test2','att1','val5'); -insert into ct(rowclass, rowid, attribute, value) values('group1','test2','att2','val6'); -insert into ct(rowclass, rowid, attribute, value) values('group1','test2','att3','val7'); -insert into ct(rowclass, rowid, attribute, value) values('group1','test2','att4','val8'); - -SELECT * -FROM crosstab( - 'select rowid, attribute, value - from ct - where rowclass = ''group1'' - and (attribute = ''att2'' or attribute = ''att3'') order by 1,2;', 3) -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) - -================================================================== -Name - -crosstab(text, text) - returns a set of row_name, extra, and - category value columns - -Synopsis - -crosstab(text source_sql, text category_sql) - -Inputs - - source_sql - - A SQL statement which produces the source set of data. The SQL statement - must return one row_name column, one category column, and one value - column. It may also have one or more "extra" columns. - - The row_name column must be first. The category and value columns - must be the last two columns, in that order. "extra" columns must be - columns 2 through (N - 2), where N is the total number of columns. - - The "extra" columns are assumed to be the same for all rows with the - same row_name. The values returned are copied from the first row - with a given row_name and subsequent values of these columns are ignored - until row_name changes. - - e.g. source_sql must produce a set something like: - SELECT row_name, extra_col, cat, value FROM foo; - - 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 - - category_sql - - A SQL statement which produces the distinct set of categories. The SQL - statement must return one category column only. category_sql must produce - at least one result row or an error will be generated. category_sql - must not produce duplicate categories or an error will be generated. - - e.g. SELECT DISTINCT cat FROM foo; - - cat - ------- - cat1 - cat2 - cat3 - cat4 - -Outputs - - Returns setof record, which must be defined with a column definition - in the FROM clause of the SELECT statement, e.g.: - - SELECT * FROM crosstab(source_sql, cat_sql) - AS ct(row_name text, extra text, cat1 text, cat2 text, cat3 text, cat4 text); - - the example crosstab function produces a set something like: - <== values columns ==> - row_name extra cat1 cat2 cat3 cat4 - ---------+-------+------+------+------+------ - row1 extra1 val1 val2 val4 - row2 extra2 val5 val6 val7 val8 - -Notes - - 1. source_sql must be ordered by row_name (column 1). - - 2. The number of values columns is determined at run-time. The - column definition provided in the FROM clause must provide for - the correct number of columns of the proper data types. - - 3. Missing values (i.e. not enough adjacent rows of same row_name to - fill the number of result values columns) are filled in with nulls. - - 4. Extra values (i.e. source rows with category not found in category_sql - result) are skipped. - - 5. Rows with a null row_name column are skipped. - - 6. You can create predefined functions to avoid having to write out - the result column names/types in each query. See the examples - for crosstab(text). - - -Example usage - -create table cth(id serial, rowid text, rowdt timestamp, attribute text, val text); -insert into cth values(DEFAULT,'test1','01 March 2003','temperature','42'); -insert into cth values(DEFAULT,'test1','01 March 2003','test_result','PASS'); -insert into cth values(DEFAULT,'test1','01 March 2003','volts','2.6987'); -insert into cth values(DEFAULT,'test2','02 March 2003','temperature','53'); -insert into cth values(DEFAULT,'test2','02 March 2003','test_result','FAIL'); -insert into cth values(DEFAULT,'test2','02 March 2003','test_startdate','01 March 2003'); -insert into cth values(DEFAULT,'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) - -================================================================== -Name - -connectby(text, text, text[, text], text, text, int[, text]) - returns a set - representing a hierarchy (tree structure) - -Synopsis - -connectby(text relname, text keyid_fld, text parent_keyid_fld - [, text orderby_fld], text start_with, int max_depth - [, text branch_delim]) - -Inputs - - relname - - Name of the source relation - - keyid_fld - - Name of the key field - - parent_keyid_fld - - Name of the key_parent field - - orderby_fld - - If optional ordering of siblings is desired: - Name of the field to order siblings - - start_with - - root value of the tree input as a text value regardless of keyid_fld type - - max_depth - - zero (0) for unlimited depth, otherwise restrict level to this depth - - branch_delim - - If optional branch value is desired, this string is used as the delimiter. - When not provided, a default value of '~' is used for internal - recursion detection only, and no "branch" field is returned. - -Outputs - - Returns setof record, which must defined with a column definition - in the FROM clause of the SELECT statement, e.g.: - - SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'row2', 0, '~') - AS t(keyid text, parent_keyid text, level int, branch text); - - - or - - - SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'row2', 0) - AS t(keyid text, parent_keyid text, level int); - - - or - - - SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'pos', 'row2', 0, '~') - AS t(keyid text, parent_keyid text, level int, branch text, pos int); - - - or - - - SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'pos', 'row2', 0) - AS t(keyid text, parent_keyid text, level int, pos int); - -Notes - - 1. keyid and parent_keyid must be the same data type - - 2. The column definition *must* include a third column of type INT4 for - the level value output - - 3. If the branch field is not desired, omit both the branch_delim input - parameter *and* the branch field in the query column definition. Note - that when branch_delim is not provided, a default value of '~' is used - for branch_delim for internal recursion detection, even though the branch - field is not returned. - - 4. If the branch field is desired, it must be the fourth column in the query - column definition, and it must be type TEXT. - - 5. The parameters representing table and field names must include double - quotes if the names are mixed-case or contain special characters. - - 6. If sorting of siblings is desired, the orderby_fld input parameter *and* - a name for the resulting serial field (type INT32) in the query column - definition must be given. - -Example usage - -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 -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 -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) ORDER BY t.pos; - 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) ORDER BY t.pos; - 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) - -================================================================== --- Joe Conway - |