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);
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Reply via email to