Re: [NOVICE] connectby(... pos_of_sibling) - Mailing list pgsql-patches
From | Bruce Momjian |
---|---|
Subject | Re: [NOVICE] connectby(... pos_of_sibling) |
Date | |
Msg-id | 200307192230.h6JMU2e25027@candle.pha.pa.us Whole thread Raw |
In response to | Re: [NOVICE] connectby(... pos_of_sibling) (Joe Conway <mail@joeconway.com>) |
List | pgsql-patches |
Your patch has been added to the PostgreSQL unapplied patches list at: http://momjian.postgresql.org/cgi-bin/pgpatches I will try to apply it within the next 48 hours. --------------------------------------------------------------------------- Joe Conway wrote: > I'm going to resend the patches that I have outstanding since it appears > some may have been lost. Here's the second of three. > ==================================================== > > > Nabil Sayegh wrote: > > Am Son, 2003-06-22 um 02.09 schrieb Joe Conway: > >>Sounds like all that's needed for your case. But to be complete, in > >>addition to changing tablefunc.c we'd have to: > >>1) come up with a new function call signature that makes sense and does > >>not cause backward compatibility problems for other people > >>2) make needed changes to tablefunc.sql.in > >>3) adjust the README.tablefunc appropriately > >>4) adjust the regression test for new functionality > >>5) be sure we don't break any of the old cases > >> > >>If you want to submit a complete patch, it would be gratefully accepted > >>-- for review at least ;-) > > > > Here's the patch, at least for steps 1-3 > > I don't know anything about regression tests :( > > > > However, I included a patch against 7.3.3 > > > > Nice work Nabil! > > I've merged the patch with cvs HEAD, added to the regression tests, and > verified no backward compatibility issues. Please apply. > > FYI Nabil, if you want to run the regression test, cd to > contrib/tablefunc as user postgres (or whoever postgresql runs as, and > be sure they have full permission on contrib/tablefunc directory) and run: > > make installcheck > > The test script that gets run is in contrib/tablefunc/sql, the expected > output is in contrib/tablefunc/expected, and the actual output is in > contrib/tablefunc/results. If the test fails you'll find regression.diff > in contrib/tablefunc. > > I'll send you a tarred copy of contrib/tablefunc (off list) to try > yourself on 7.3.3, as I don't think this patch will apply cleanly to it. > It ought to work on 7.3.3, and it includes enhance crosstab functionality. > > Thanks! > > Joe > > Index: contrib/tablefunc/README.tablefunc > =================================================================== > RCS file: /opt/src/cvs/pgsql-server/contrib/tablefunc/README.tablefunc,v > retrieving revision 1.6 > diff -c -r1.6 README.tablefunc > *** contrib/tablefunc/README.tablefunc 20 Mar 2003 06:46:30 -0000 1.6 > --- contrib/tablefunc/README.tablefunc 26 Jun 2003 16:44:17 -0000 > *************** > *** 4,9 **** > --- 4,11 ---- > * Sample to demonstrate C functions which return setof scalar > * and setof composite. > * Joe Conway <mail@joeconway.com> > + * And contributors: > + * Nabil Sayegh <postgresql@e-trolley.de> > * > * Copyright 2002 by PostgreSQL Global Development Group > * > *************** > *** 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. > > --- 62,72 ---- > - 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. > > *************** > *** 452,464 **** > ================================================================== > 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 > > --- 456,469 ---- > ================================================================== > 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 > > *************** > *** 474,479 **** > --- 479,489 ---- > > 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 > *************** > *** 500,505 **** > --- 510,525 ---- > > 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 > > *************** > *** 520,541 **** > 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 > --- 540,564 ---- > 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 > *************** > *** 548,554 **** > 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 > --- 571,577 ---- > 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 > *************** > *** 559,564 **** > --- 582,613 ---- > 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) > > ================================================================== > Index: contrib/tablefunc/tablefunc.c > =================================================================== > RCS file: /opt/src/cvs/pgsql-server/contrib/tablefunc/tablefunc.c,v > retrieving revision 1.19 > diff -c -r1.19 tablefunc.c > *** contrib/tablefunc/tablefunc.c 25 Jun 2003 18:13:50 -0000 1.19 > --- contrib/tablefunc/tablefunc.c 26 Jun 2003 16:44:27 -0000 > *************** > *** 4,9 **** > --- 4,11 ---- > * Sample to demonstrate C functions which return setof scalar > * and setof composite. > * Joe Conway <mail@joeconway.com> > + * And contributors: > + * Nabil Sayegh <postgresql@e-trolley.de> > * > * Copyright 2002 by PostgreSQL Global Development Group > * > *************** > *** 45,51 **** > int num_categories, > TupleDesc tupdesc, > MemoryContext per_query_ctx); > ! 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); > --- 47,53 ---- > int num_categories, > TupleDesc tupdesc, > MemoryContext per_query_ctx); > ! 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); > *************** > *** 54,74 **** > --- 56,81 ---- > 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); > *************** > *** 998,1028 **** > * > * 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); > --- 1005,1036 ---- > * > * 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); > *************** > *** 1040,1045 **** > --- 1048,1054 ---- > 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; > *************** > *** 1067,1073 **** > tupdesc = CreateTupleDescCopy(rsinfo->expectedDesc); > > /* does it meet our needs */ > ! validateConnectbyTupleDesc(tupdesc, show_branch); > > /* OK, use it then */ > attinmeta = TupleDescGetAttInMetadata(tupdesc); > --- 1076,1082 ---- > tupdesc = CreateTupleDescCopy(rsinfo->expectedDesc); > > /* does it meet our needs */ > ! validateConnectbyTupleDesc(tupdesc, show_branch, show_serial); > > /* OK, use it then */ > attinmeta = TupleDescGetAttInMetadata(tupdesc); > *************** > *** 1082,1091 **** > --- 1091,1102 ---- > 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; > *************** > *** 1102,1107 **** > --- 1113,1197 ---- > 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() > */ > *************** > *** 1109,1118 **** > --- 1199,1210 ---- > 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) > { > *************** > *** 1120,1125 **** > --- 1212,1219 ---- > int ret; > MemoryContext oldcontext; > > + int serial = 1; > + > /* Connect to SPI manager */ > if ((ret = SPI_connect()) < 0) > elog(ERROR, "connectby: SPI_connect returned %d", ret); > *************** > *** 1136,1147 **** > --- 1230,1244 ---- > 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); > *************** > *** 1155,1166 **** > --- 1252,1266 ---- > 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) > *************** > *** 1170,1187 **** > 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); > --- 1270,1304 ---- > 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); > *************** > *** 1198,1203 **** > --- 1315,1321 ---- > 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; > *************** > *** 1212,1220 **** > 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) > --- 1330,1338 ---- > 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) > *************** > *** 1243,1248 **** > --- 1361,1376 ---- > 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); > > *************** > *** 1290,1295 **** > --- 1418,1431 ---- > 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); > > *************** > *** 1311,1322 **** > --- 1447,1461 ---- > 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); > *************** > *** 1340,1357 **** > * 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"); > } > --- 1479,1501 ---- > * 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"); > } > *************** > *** 1371,1376 **** > --- 1515,1530 ---- > 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 */ > } > > Index: contrib/tablefunc/tablefunc.h > =================================================================== > RCS file: /opt/src/cvs/pgsql-server/contrib/tablefunc/tablefunc.h,v > retrieving revision 1.5 > diff -c -r1.5 tablefunc.h > *** contrib/tablefunc/tablefunc.h 20 Mar 2003 06:46:30 -0000 1.5 > --- contrib/tablefunc/tablefunc.h 26 Jun 2003 16:44:34 -0000 > *************** > *** 4,9 **** > --- 4,11 ---- > * Sample to demonstrate C functions which return setof scalar > * and setof composite. > * Joe Conway <mail@joeconway.com> > + * And contributors: > + * Nabil Sayegh <postgresql@e-trolley.de> > * > * Copyright 2002 by PostgreSQL Global Development Group > * > *************** > *** 36,40 **** > --- 38,43 ---- > extern Datum crosstab(PG_FUNCTION_ARGS); > extern Datum crosstab_hash(PG_FUNCTION_ARGS); > extern Datum connectby_text(PG_FUNCTION_ARGS); > + extern Datum connectby_text_serial(PG_FUNCTION_ARGS); > > #endif /* TABLEFUNC_H */ > Index: contrib/tablefunc/tablefunc.sql.in > =================================================================== > RCS file: /opt/src/cvs/pgsql-server/contrib/tablefunc/tablefunc.sql.in,v > retrieving revision 1.7 > diff -c -r1.7 tablefunc.sql.in > *** contrib/tablefunc/tablefunc.sql.in 14 May 2003 03:25:57 -0000 1.7 > --- contrib/tablefunc/tablefunc.sql.in 26 Jun 2003 16:19:29 -0000 > *************** > *** 64,66 **** > --- 64,78 ---- > 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; > Index: contrib/tablefunc/data/connectby_text.data > =================================================================== > RCS file: /opt/src/cvs/pgsql-server/contrib/tablefunc/data/connectby_text.data,v > retrieving revision 1.1 > diff -c -r1.1 connectby_text.data > *** contrib/tablefunc/data/connectby_text.data 12 Sep 2002 00:14:40 -0000 1.1 > --- contrib/tablefunc/data/connectby_text.data 26 Jun 2003 16:31:47 -0000 > *************** > *** 1,9 **** > ! row1 \N > ! row2 row1 > ! row3 row1 > ! row4 row2 > ! row5 row2 > ! row6 row4 > ! row7 row3 > ! row8 row6 > ! row9 row5 > --- 1,9 ---- > ! row1 \N 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 > Index: contrib/tablefunc/expected/tablefunc.out > =================================================================== > RCS file: /opt/src/cvs/pgsql-server/contrib/tablefunc/expected/tablefunc.out,v > retrieving revision 1.6 > diff -c -r1.6 tablefunc.out > *** contrib/tablefunc/expected/tablefunc.out 20 Mar 2003 06:46:30 -0000 1.6 > --- contrib/tablefunc/expected/tablefunc.out 26 Jun 2003 16:37:27 -0000 > *************** > *** 197,205 **** > -- connectby > -- > -- test connectby with text based hierarchy > ! CREATE TABLE connectby_text(keyid text, parent_keyid text); > \copy connectby_text from 'data/connectby_text.data' > ! -- with branch > SELECT * FROM connectby('connectby_text', 'keyid', 'parent_keyid', 'row2', 0, '~') AS t(keyid text, parent_keyid text,level int, branch text); > keyid | parent_keyid | level | branch > -------+--------------+-------+--------------------- > --- 197,205 ---- > -- connectby > -- > -- test connectby with text based hierarchy > ! CREATE TABLE connectby_text(keyid text, parent_keyid text, pos int); > \copy connectby_text from 'data/connectby_text.data' > ! -- with branch, without orderby > SELECT * FROM connectby('connectby_text', 'keyid', 'parent_keyid', 'row2', 0, '~') AS t(keyid text, parent_keyid text,level int, branch text); > keyid | parent_keyid | level | branch > -------+--------------+-------+--------------------- > *************** > *** 211,217 **** > row9 | row5 | 2 | row2~row5~row9 > (6 rows) > > ! -- without branch > SELECT * FROM connectby('connectby_text', 'keyid', 'parent_keyid', 'row2', 0) AS t(keyid text, parent_keyid text, levelint); > keyid | parent_keyid | level > -------+--------------+------- > --- 211,217 ---- > row9 | row5 | 2 | row2~row5~row9 > (6 rows) > > ! -- without branch, without orderby > SELECT * FROM connectby('connectby_text', 'keyid', 'parent_keyid', 'row2', 0) AS t(keyid text, parent_keyid text, levelint); > keyid | parent_keyid | level > -------+--------------+------- > *************** > *** 221,226 **** > --- 221,250 ---- > row8 | row6 | 3 > row5 | row2 | 1 > row9 | row5 | 2 > + (6 rows) > + > + -- with branch, with orderby > + SELECT * FROM connectby('connectby_text', 'keyid', 'parent_keyid', 'pos', 'row2', 0, '~') AS t(keyid text, parent_keyidtext, 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 > + SELECT * FROM connectby('connectby_text', '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) > > -- test connectby with int based hierarchy > Index: contrib/tablefunc/sql/tablefunc.sql > =================================================================== > RCS file: /opt/src/cvs/pgsql-server/contrib/tablefunc/sql/tablefunc.sql,v > retrieving revision 1.7 > diff -c -r1.7 tablefunc.sql > *** contrib/tablefunc/sql/tablefunc.sql 20 Mar 2003 06:46:30 -0000 1.7 > --- contrib/tablefunc/sql/tablefunc.sql 26 Jun 2003 16:37:23 -0000 > *************** > *** 94,107 **** > -- > > -- test connectby with text based hierarchy > ! CREATE TABLE connectby_text(keyid text, parent_keyid text); > \copy connectby_text from 'data/connectby_text.data' > > ! -- with branch > SELECT * FROM connectby('connectby_text', 'keyid', 'parent_keyid', 'row2', 0, '~') AS t(keyid text, parent_keyid text,level int, branch text); > > ! -- without branch > SELECT * FROM connectby('connectby_text', 'keyid', 'parent_keyid', 'row2', 0) AS t(keyid text, parent_keyid text, levelint); > > -- test connectby with int based hierarchy > CREATE TABLE connectby_int(keyid int, parent_keyid int); > --- 94,113 ---- > -- > > -- test connectby with text based hierarchy > ! CREATE TABLE connectby_text(keyid text, parent_keyid text, pos int); > \copy connectby_text from 'data/connectby_text.data' > > ! -- with branch, without orderby > SELECT * FROM connectby('connectby_text', 'keyid', 'parent_keyid', 'row2', 0, '~') AS t(keyid text, parent_keyid text,level int, branch text); > > ! -- without branch, without orderby > SELECT * FROM connectby('connectby_text', 'keyid', 'parent_keyid', 'row2', 0) AS t(keyid text, parent_keyid text, levelint); > + > + -- with branch, with orderby > + SELECT * FROM connectby('connectby_text', 'keyid', 'parent_keyid', 'pos', 'row2', 0, '~') AS t(keyid text, parent_keyidtext, level int, branch text, pos int) ORDER BY t.pos; > + > + -- without branch, with orderby > + SELECT * FROM connectby('connectby_text', 'keyid', 'parent_keyid', 'pos', 'row2', 0) AS t(keyid text, parent_keyid text,level int, pos int) ORDER BY t.pos; > > -- test connectby with int based hierarchy > CREATE TABLE connectby_int(keyid int, parent_keyid int); > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
pgsql-patches by date: