Skip site navigation (1) Skip section navigation (2)

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

From: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
To: Joe Conway <mail(at)joeconway(dot)com>
Cc: Nabil Sayegh <postgresql(at)e-trolley(dot)de>,"Patches (PostgreSQL)" <pgsql-patches(at)postgresql(dot)org>
Subject: Re: [NOVICE] connectby(... pos_of_sibling)
Date: 2003-07-27 03:52:03
Message-ID: 200307270352.h6R3q3f09906@candle.pha.pa.us (view raw or flat)
Thread:
Lists: pgsql-novicepgsql-patches
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

In response to

pgsql-novice by date

Next:From: bruceDate: 2003-07-28 01:00:14
Subject: Accessing Postgres db from apache using PHP
Previous:From: Bruno Wolff IIIDate: 2003-07-26 17:30:27
Subject: Re: More than one installation on the same machine

pgsql-patches by date

Next:From: Bruce MomjianDate: 2003-07-27 04:35:54
Subject: Re: [HACKERS] allowed user/db variables
Previous:From: Bruce MomjianDate: 2003-07-27 03:47:46
Subject: Re: nitpick consistency patch for pg_dump.c

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group