diff -rc postgresql-7.3.3/contrib/tablefunc/README.tablefunc postgresql-7.3.3-tablefunc_connectby_orderby_fld/contrib/tablefunc/README.tablefunc *** postgresql-7.3.3/contrib/tablefunc/README.tablefunc Sat Nov 23 02:54:23 2002 --- postgresql-7.3.3-tablefunc_connectby_orderby_fld/contrib/tablefunc/README.tablefunc Wed Jun 25 19:54:01 2003 *************** *** 60,68 **** - requires anonymous composite type syntax in the FROM clause. See the instructions in the documentation below. ! connectby(text relname, text keyid_fld, text parent_keyid_fld, ! text start_with, int max_depth [, text branch_delim]) - returns keyid, parent_keyid, level, and an optional branch string - requires anonymous composite type syntax in the FROM clause. See the instructions in the documentation below. --- 60,70 ---- - requires anonymous composite type syntax in the FROM clause. See the instructions in the documentation below. ! 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. *************** *** 333,345 **** ================================================================== Name ! connectby(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 start_with, int max_depth [, text branch_delim]) Inputs --- 335,348 ---- ================================================================== 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 *************** *** 355,360 **** --- 358,368 ---- 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 *************** *** 381,386 **** --- 389,404 ---- 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 *************** *** 401,422 **** 5. The parameters representing table and field names must include double quotes if the names are mixed-case or contain special characters. Example usage ! CREATE TABLE connectby_tree(keyid text, parent_keyid text); ! INSERT INTO connectby_tree VALUES('row1',NULL); ! INSERT INTO connectby_tree VALUES('row2','row1'); ! INSERT INTO connectby_tree VALUES('row3','row1'); ! INSERT INTO connectby_tree VALUES('row4','row2'); ! INSERT INTO connectby_tree VALUES('row5','row2'); ! INSERT INTO connectby_tree VALUES('row6','row4'); ! INSERT INTO connectby_tree VALUES('row7','row3'); ! INSERT INTO connectby_tree VALUES('row8','row6'); ! INSERT INTO connectby_tree VALUES('row9','row5'); ! -- with branch 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 --- 419,443 ---- 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 *************** *** 429,435 **** row9 | row5 | 2 | row2~row5~row9 (6 rows) ! -- without branch SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'row2', 0) AS t(keyid text, parent_keyid text, level int); keyid | parent_keyid | level --- 450,456 ---- 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 *************** *** 442,447 **** --- 463,494 ---- 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 diff -rc postgresql-7.3.3/contrib/tablefunc/tablefunc.c postgresql-7.3.3-tablefunc_connectby_orderby_fld/contrib/tablefunc/tablefunc.c *** postgresql-7.3.3/contrib/tablefunc/tablefunc.c Fri May 16 08:07:58 2003 --- postgresql-7.3.3-tablefunc_connectby_orderby_fld/contrib/tablefunc/tablefunc.c Wed Jun 25 19:58:28 2003 *************** *** 39,45 **** #include "tablefunc.h" ! static void validateConnectbyTupleDesc(TupleDesc tupdesc, bool show_branch); static bool compatCrosstabTupleDescs(TupleDesc tupdesc1, TupleDesc tupdesc2); static bool compatConnectbyTupleDescs(TupleDesc tupdesc1, TupleDesc tupdesc2); static void get_normal_pair(float8 *x1, float8 *x2); --- 39,45 ---- #include "tablefunc.h" ! static void validateConnectbyTupleDesc(TupleDesc tupdesc, bool show_branch, bool show_serial); static bool compatCrosstabTupleDescs(TupleDesc tupdesc1, TupleDesc tupdesc2); static bool compatConnectbyTupleDescs(TupleDesc tupdesc1, TupleDesc tupdesc2); static void get_normal_pair(float8 *x1, float8 *x2); *************** *** 48,68 **** --- 48,73 ---- static Tuplestorestate *connectby(char *relname, char *key_fld, char *parent_key_fld, + char *orderby_fld, char *branch_delim, char *start_with, int max_depth, bool show_branch, + bool show_serial, MemoryContext per_query_ctx, AttInMetadata *attinmeta); static Tuplestorestate *build_tuplestore_recursively(char *key_fld, char *parent_key_fld, char *relname, + char *orderby_fld, char *branch_delim, char *start_with, char *branch, int level, + int *serial, int max_depth, bool show_branch, + bool show_serial, MemoryContext per_query_ctx, AttInMetadata *attinmeta, Tuplestorestate *tupstore); *************** *** 598,628 **** * * e.g. given table foo: * ! * keyid parent_keyid ! * ------+-------------- ! * row1 NULL ! * row2 row1 ! * row3 row1 ! * row4 row2 ! * row5 row2 ! * row6 row4 ! * row7 row3 ! * row8 row6 ! * row9 row5 * * ! * connectby(text relname, text keyid_fld, text parent_keyid_fld, ! * text start_with, int max_depth [, text branch_delim]) ! * connectby('foo', 'keyid', 'parent_keyid', 'row2', 0, '~') returns: * ! * keyid parent_id level branch * ------+-----------+--------+----------------------- ! * row2 NULL 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 * */ PG_FUNCTION_INFO_V1(connectby_text); --- 603,634 ---- * * e.g. given table foo: * ! * keyid parent_keyid pos ! * ------+------------+-- ! * row1 NULL 0 ! * row2 row1 0 ! * row3 row1 0 ! * row4 row2 1 ! * row5 row2 0 ! * row6 row4 0 ! * row7 row3 0 ! * row8 row6 0 ! * row9 row5 0 * * ! * connectby(text relname, text keyid_fld, text parent_keyid_fld ! * [, text orderby_fld], text start_with, int max_depth ! * [, text branch_delim]) ! * connectby('foo', 'keyid', 'parent_keyid', 'pos', 'row2', 0, '~') returns: * ! * keyid parent_id level branch serial * ------+-----------+--------+----------------------- ! * row2 NULL 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 * */ PG_FUNCTION_INFO_V1(connectby_text); *************** *** 640,645 **** --- 646,652 ---- int max_depth = PG_GETARG_INT32(4); char *branch_delim = NULL; bool show_branch = false; + bool show_serial = false; ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo; TupleDesc tupdesc; AttInMetadata *attinmeta; *************** *** 667,673 **** tupdesc = CreateTupleDescCopy(rsinfo->expectedDesc); /* does it meet our needs */ ! validateConnectbyTupleDesc(tupdesc, show_branch); /* OK, use it then */ attinmeta = TupleDescGetAttInMetadata(tupdesc); --- 674,680 ---- tupdesc = CreateTupleDescCopy(rsinfo->expectedDesc); /* does it meet our needs */ ! validateConnectbyTupleDesc(tupdesc, show_branch, show_serial); /* OK, use it then */ attinmeta = TupleDescGetAttInMetadata(tupdesc); *************** *** 682,691 **** --- 689,700 ---- rsinfo->setResult = connectby(relname, key_fld, parent_key_fld, + NULL, branch_delim, start_with, max_depth, show_branch, + show_serial, per_query_ctx, attinmeta); rsinfo->setDesc = tupdesc; *************** *** 702,707 **** --- 711,796 ---- return (Datum) 0; } + PG_FUNCTION_INFO_V1(connectby_text_serial); + + Datum + connectby_text_serial(PG_FUNCTION_ARGS) + { + char *relname = GET_STR(PG_GETARG_TEXT_P(0)); + char *key_fld = GET_STR(PG_GETARG_TEXT_P(1)); + char *parent_key_fld = GET_STR(PG_GETARG_TEXT_P(2)); + char *orderby_fld = GET_STR(PG_GETARG_TEXT_P(3)); + char *start_with = GET_STR(PG_GETARG_TEXT_P(4)); + int max_depth = PG_GETARG_INT32(5); + char *branch_delim = NULL; + bool show_branch = false; + bool show_serial = true; + + ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo; + TupleDesc tupdesc; + AttInMetadata *attinmeta; + MemoryContext per_query_ctx; + MemoryContext oldcontext; + + /* check to see if caller supports us returning a tuplestore */ + if (!rsinfo || !(rsinfo->allowedModes & SFRM_Materialize)) + elog(ERROR, "connectby: materialize mode required, but it is not " + "allowed in this context"); + + if (fcinfo->nargs == 7) + { + branch_delim = GET_STR(PG_GETARG_TEXT_P(6)); + show_branch = true; + } + else + /* default is no show, tilde for the delimiter */ + branch_delim = pstrdup("~"); + + per_query_ctx = rsinfo->econtext->ecxt_per_query_memory; + oldcontext = MemoryContextSwitchTo(per_query_ctx); + + /* get the requested return tuple description */ + tupdesc = CreateTupleDescCopy(rsinfo->expectedDesc); + + /* does it meet our needs */ + validateConnectbyTupleDesc(tupdesc, show_branch, show_serial); + + /* OK, use it then */ + attinmeta = TupleDescGetAttInMetadata(tupdesc); + + /* check to see if caller supports us returning a tuplestore */ + if (!rsinfo->allowedModes & SFRM_Materialize) + elog(ERROR, "connectby requires Materialize mode, but it is not " + "allowed in this context"); + + /* OK, go to work */ + rsinfo->returnMode = SFRM_Materialize; + rsinfo->setResult = connectby(relname, + key_fld, + parent_key_fld, + orderby_fld, + branch_delim, + start_with, + max_depth, + show_branch, + show_serial, + per_query_ctx, + attinmeta); + rsinfo->setDesc = tupdesc; + + MemoryContextSwitchTo(oldcontext); + + /* + * SFRM_Materialize mode expects us to return a NULL Datum. The actual + * tuples are in our tuplestore and passed back through + * rsinfo->setResult. rsinfo->setDesc is set to the tuple description + * that we actually used to build our tuples with, so the caller can + * verify we did what it was expecting. + */ + return (Datum) 0; + } + + /* * connectby - does the real work for connectby_text() */ *************** *** 709,718 **** --- 798,809 ---- connectby(char *relname, char *key_fld, char *parent_key_fld, + char *orderby_fld, char *branch_delim, char *start_with, int max_depth, bool show_branch, + bool show_serial, MemoryContext per_query_ctx, AttInMetadata *attinmeta) { *************** *** 720,725 **** --- 811,818 ---- int ret; MemoryContext oldcontext; + int serial = 1; + /* Connect to SPI manager */ if ((ret = SPI_connect()) < 0) elog(ERROR, "connectby: SPI_connect returned %d", ret); *************** *** 736,747 **** --- 829,843 ---- tupstore = build_tuplestore_recursively(key_fld, parent_key_fld, relname, + orderby_fld, branch_delim, start_with, start_with, /* current_branch */ 0, /* initial level is 0 */ + &serial, /* initial serial is 1 */ max_depth, show_branch, + show_serial, per_query_ctx, attinmeta, tupstore); *************** *** 759,770 **** --- 855,869 ---- build_tuplestore_recursively(char *key_fld, char *parent_key_fld, char *relname, + char *orderby_fld, char *branch_delim, char *start_with, char *branch, int level, + int *serial, int max_depth, bool show_branch, + bool show_serial, MemoryContext per_query_ctx, AttInMetadata *attinmeta, Tuplestorestate *tupstore) *************** *** 774,791 **** StringInfo sql = makeStringInfo(); int ret; int proc; if (max_depth > 0 && level > max_depth) return tupstore; /* Build initial sql statement */ ! appendStringInfo(sql, "SELECT %s, %s FROM %s WHERE %s = '%s' AND %s IS NOT NULL", key_fld, parent_key_fld, relname, parent_key_fld, start_with, key_fld); /* Retrieve the desired rows */ ret = SPI_exec(sql->data, 0); --- 873,907 ---- StringInfo sql = makeStringInfo(); int ret; int proc; + int serial_column; if (max_depth > 0 && level > max_depth) return tupstore; /* Build initial sql statement */ ! if (!show_serial) ! { ! appendStringInfo(sql, "SELECT %s, %s FROM %s WHERE %s = '%s' AND %s IS NOT NULL", key_fld, parent_key_fld, relname, parent_key_fld, start_with, key_fld); + serial_column=0; + } + else + { + appendStringInfo(sql, "SELECT %s, %s FROM %s WHERE %s = '%s' AND %s IS NOT NULL ORDER BY %s", + key_fld, + parent_key_fld, + relname, + parent_key_fld, + start_with, + key_fld, + orderby_fld); + serial_column=1; + } /* Retrieve the desired rows */ ret = SPI_exec(sql->data, 0); *************** *** 802,807 **** --- 918,924 ---- char *current_key; char *current_key_parent; char current_level[INT32_STRLEN]; + char serial_str[INT32_STRLEN]; char *current_branch; char **values; StringInfo branchstr = NULL; *************** *** 816,824 **** chk_current_key = makeStringInfo(); if (show_branch) ! values = (char **) palloc(CONNECTBY_NCOLS * sizeof(char *)); else ! values = (char **) palloc(CONNECTBY_NCOLS_NOBRANCH * sizeof(char *)); /* First time through, do a little setup */ if (level == 0) --- 933,941 ---- chk_current_key = makeStringInfo(); if (show_branch) ! values = (char **) palloc((CONNECTBY_NCOLS + serial_column) * sizeof(char *)); else ! values = (char **) palloc((CONNECTBY_NCOLS_NOBRANCH + serial_column) * sizeof(char *)); /* First time through, do a little setup */ if (level == 0) *************** *** 847,852 **** --- 964,979 ---- if (show_branch) values[3] = start_with; + /* root starts the serial with 1 */ + if (show_serial) + { + sprintf(serial_str, "%d", (*serial)++); + if (show_branch) + values[4] = serial_str; + else + values[3] = serial_str; + } + /* construct the tuple */ tuple = BuildTupleFromCStrings(attinmeta, values); *************** *** 894,899 **** --- 1021,1034 ---- values[2] = current_level; if (show_branch) values[3] = current_branch; + if (show_serial) + { + sprintf(serial_str, "%d", (*serial)++); + if (show_branch) + values[4] = serial_str; + else + values[3] = serial_str; + } tuple = BuildTupleFromCStrings(attinmeta, values); *************** *** 915,926 **** --- 1050,1064 ---- tupstore = build_tuplestore_recursively(key_fld, parent_key_fld, relname, + orderby_fld, branch_delim, values[0], current_branch, level + 1, + serial, max_depth, show_branch, + show_serial, per_query_ctx, attinmeta, tupstore); *************** *** 944,961 **** * Check expected (query runtime) tupdesc suitable for Connectby */ static void ! validateConnectbyTupleDesc(TupleDesc tupdesc, bool show_branch) { /* are there the correct number of columns */ if (show_branch) { ! if (tupdesc->natts != CONNECTBY_NCOLS) elog(ERROR, "Query-specified return tuple not valid for Connectby: " "wrong number of columns"); } else { ! if (tupdesc->natts != CONNECTBY_NCOLS_NOBRANCH) elog(ERROR, "Query-specified return tuple not valid for Connectby: " "wrong number of columns"); } --- 1082,1104 ---- * Check expected (query runtime) tupdesc suitable for Connectby */ static void ! validateConnectbyTupleDesc(TupleDesc tupdesc, bool show_branch, bool show_serial) { + int serial_column=0; + + if (show_serial) + serial_column=1; + /* are there the correct number of columns */ if (show_branch) { ! if (tupdesc->natts != (CONNECTBY_NCOLS + serial_column)) elog(ERROR, "Query-specified return tuple not valid for Connectby: " "wrong number of columns"); } else { ! if (tupdesc->natts != CONNECTBY_NCOLS_NOBRANCH + serial_column) elog(ERROR, "Query-specified return tuple not valid for Connectby: " "wrong number of columns"); } *************** *** 973,980 **** /* check that the type of the forth column is TEXT if applicable */ if (show_branch && tupdesc->attrs[3]->atttypid != TEXTOID) elog(ERROR, "Query-specified return tuple not valid for Connectby: " ! "third column must be type %s", format_type_be(TEXTOID)); /* OK, the tupdesc is valid for our purposes */ } --- 1116,1133 ---- /* check that the type of the forth column is TEXT if applicable */ if (show_branch && tupdesc->attrs[3]->atttypid != TEXTOID) elog(ERROR, "Query-specified return tuple not valid for Connectby: " ! "fourth column must be type %s", format_type_be(TEXTOID)); + /* check that the type of the fifth column is INT4 */ + if (show_branch && show_serial && tupdesc->attrs[4]->atttypid != INT4OID) + elog(ERROR, "Query-specified return tuple not valid for Connectby: " + "fifth column must be type %s", format_type_be(INT4OID)); + + /* check that the type of the fifth column is INT4 */ + if (!show_branch && show_serial && tupdesc->attrs[3]->atttypid != INT4OID) + elog(ERROR, "Query-specified return tuple not valid for Connectby: " + "fourth column must be type %s", format_type_be(INT4OID)); + /* OK, the tupdesc is valid for our purposes */ } diff -rc postgresql-7.3.3/contrib/tablefunc/tablefunc.h postgresql-7.3.3-tablefunc_connectby_orderby_fld/contrib/tablefunc/tablefunc.h *** postgresql-7.3.3/contrib/tablefunc/tablefunc.h Wed Sep 4 22:31:08 2002 --- postgresql-7.3.3-tablefunc_connectby_orderby_fld/contrib/tablefunc/tablefunc.h Wed Jun 25 03:02:02 2003 *************** *** 35,39 **** --- 35,40 ---- extern Datum normal_rand(PG_FUNCTION_ARGS); extern Datum crosstab(PG_FUNCTION_ARGS); extern Datum connectby_text(PG_FUNCTION_ARGS); + extern Datum connectby_text_serial(PG_FUNCTION_ARGS); #endif /* TABLEFUNC_H */ diff -rc postgresql-7.3.3/contrib/tablefunc/tablefunc.sql.in postgresql-7.3.3-tablefunc_connectby_orderby_fld/contrib/tablefunc/tablefunc.sql.in *** postgresql-7.3.3/contrib/tablefunc/tablefunc.sql.in Fri Oct 18 20:41:21 2002 --- postgresql-7.3.3-tablefunc_connectby_orderby_fld/contrib/tablefunc/tablefunc.sql.in Wed Jun 25 19:23:38 2003 *************** *** 61,63 **** --- 61,75 ---- RETURNS setof record AS 'MODULE_PATHNAME','connectby_text' LANGUAGE 'C' STABLE STRICT; + + -- These 2 take the name of a field to ORDER BY as 4th arg (for sorting siblings) + + CREATE OR REPLACE FUNCTION connectby(text,text,text,text,text,int,text) + RETURNS setof record + AS 'MODULE_PATHNAME','connectby_text_serial' + LANGUAGE 'C' STABLE STRICT; + + CREATE OR REPLACE FUNCTION connectby(text,text,text,text,text,int) + RETURNS setof record + AS 'MODULE_PATHNAME','connectby_text_serial' + LANGUAGE 'C' STABLE STRICT;