Re: [NOVICE] connectby(... pos_of_sibling)

Patch applied. Thanks.

---------------------------------------------------------------------------

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(at)joeconway(dot)com>
> + * And contributors:
> + * Nabil Sayegh <postgresql(at)e-trolley(dot)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(at)joeconway(dot)com>
> + * And contributors:
> + * Nabil Sayegh <postgresql(at)e-trolley(dot)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(at)joeconway(dot)com>
> + * And contributors:
> + * Nabil Sayegh <postgresql(at)e-trolley(dot)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, level int);
> 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, level int);
> 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_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
> + 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, level int);
>
> -- 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, level int);
> +
> + -- with branch, with orderby
> + SELECT * FROM connectby('connectby_text', 'keyid', 'parent_keyid', 'pos', 'row2', 0, '~') AS t(keyid text, parent_keyid text, 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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073