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 <[EMAIL PROTECTED]>
> +  * And contributors:
> +  * Nabil Sayegh <[EMAIL PROTECTED]>
>    *
>    * 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 <[EMAIL PROTECTED]>
> +  * And contributors:
> +  * Nabil Sayegh <[EMAIL PROTECTED]>
>    *
>    * 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 <[EMAIL PROTECTED]>
> +  * And contributors:
> +  * Nabil Sayegh <[EMAIL PROTECTED]>
>    *
>    * 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
  [EMAIL PROTECTED]               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Reply via email to