Joe, would you comment on this change to tablefunc connectby?

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

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
> 
> bye
> -- 
>  e-Trolley Sayegh & John, Nabil Sayegh
>  Tel.: 0700 etrolley /// 0700 38765539
>  Fax.: +49 69 8299381-8
>  PGP : http://www.e-trolley.de

[ Attachment, skipping... ]

> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

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

Reply via email to