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

Hi all,

I have a question about connectby():
Is it possible to have the siblings sorted ?

pos gives the order of the siblings:

--------------------------8<-------------------------
CREATE TABLE nav (
id_nav serial primary key,
id2_nav integer references nav,
nav text not null,
pos integer not null
);

COPY nav (id_nav, id2_nav, nav, pos) FROM stdin;
1 \N World 0
2 1 Top 1
3 1 Left 2
4 1 Support 0
5 2 Home 0
6 2 News 1
7 4 Contact 0
8 4 Search 1
9 4 Sitemap 2
10 4 Imprint 3
\.

SELECT pg_catalog.setval ('nav_id_nav_seq', 10, true);
-------------------------8<---------------------------

If I join the resulting tree with another table, the order could be
destroyed again (?)
So I think there should also be a temporary SEQ that can be used in the
ORDER BY clause at the end.

Any idea ?
--
e-Trolley Sayegh & John, Nabil Sayegh
Tel.: 0700 etrolley /// 0700 38765539
Fax.: +49 69 8299381-8
PGP : http://www.e-trolley.de

Nabil Sayegh wrote:
> If I join the resulting tree with another table, the order could be
> destroyed again (?)
> So I think there should also be a temporary SEQ that can be used in the
> ORDER BY clause at the end.
>

The only way to do what you want (if I understand correctly) currently
is to use padded keys and then sort by branch. Here's a bit of a
workaround that might do the trick for you:

create or replace function pad_id(int,int) returns text as 'select
repeat(''0'', $2 - length($1::text)) || $1' language 'sql';

create view nav_vw as select pad_id(id_nav,4) as id_nav,
pad_id(id2_nav,4) as id2_nav, nav, pos from nav;

select ss.id_nav,ss.id2_nav,ss.level,ss.branch,n.nav,n.pos from nav_vw
n, (select id_nav,id2_nav,level,branch from
connectby('nav_vw','id_nav','id2_nav','0001',0,'~') as (id_nav text,
id2_nav text, level int, branch text)) as ss where n.id_nav = ss.id_nav
order by ss.branch;
id_nav | id2_nav | level | branch | nav | pos
--------+---------+-------+----------------+---------+-----
0001 | | 0 | 0001 | World | 0
0002 | 0001 | 1 | 0001~0002 | Top | 1
0005 | 0002 | 2 | 0001~0002~0005 | Home | 0
0006 | 0002 | 2 | 0001~0002~0006 | News | 1
0003 | 0001 | 1 | 0001~0003 | Left | 2
0004 | 0001 | 1 | 0001~0004 | Support | 0
0007 | 0004 | 2 | 0001~0004~0007 | Contact | 0
0008 | 0004 | 2 | 0001~0004~0008 | Search | 1
0009 | 0004 | 2 | 0001~0004~0009 | Sitemap | 2
0010 | 0004 | 2 | 0001~0004~0010 | Imprint | 3
(10 rows)

In 7.4 there may be a slightly better workaround (if a submitted patch
gets accepted). You can convert branch into an array of integers, and
order by that:

select ss.id_nav, ss.id2_nav, ss.level,
string_to_array(ss.branch,'~')::int[] as branch, n.nav,n.pos from nav n,
(select id_nav,id2_nav,level,branch from
connectby('nav','id_nav','id2_nav','1',0,'~') as (id_nav int, id2_nav
int, level int, branch text)) as ss where n.id_nav = ss.id_nav order by
string_to_array(ss.branch,'~')::int[];
id_nav | id2_nav | level | branch | nav | pos
--------+---------+-------+----------+---------+-----
1 | | 0 | {1} | World | 0
2 | 1 | 1 | {1,2} | Top | 1
5 | 2 | 2 | {1,2,5} | Home | 0
6 | 2 | 2 | {1,2,6} | News | 1
3 | 1 | 1 | {1,3} | Left | 2
4 | 1 | 1 | {1,4} | Support | 0
7 | 4 | 2 | {1,4,7} | Contact | 0
8 | 4 | 2 | {1,4,8} | Search | 1
9 | 4 | 2 | {1,4,9} | Sitemap | 2
10 | 4 | 2 | {1,4,10} | Imprint | 3
(10 rows)

I'll think more about a row number column though. Maybe for 7.4 (but
then again, times running out and I have a few things in front of this,
so no promises).

Joe

Am Sam, 2003-06-21 um 06.20 schrieb Joe Conway:
> Nabil Sayegh wrote:
> > If I join the resulting tree with another table, the order could be
> > destroyed again (?)
> > So I think there should also be a temporary SEQ that can be used in the
> > ORDER BY clause at the end.
> >
>
> The only way to do what you want (if I understand correctly) currently
> is to use padded keys and then sort by branch. Here's a bit of a
> workaround that might do the trick for you:
>
> create or replace function pad_id(int,int) returns text as 'select
> repeat(''0'', $2 - length($1::text)) || $1' language 'sql';
>
> create view nav_vw as select pad_id(id_nav,4) as id_nav,
> pad_id(id2_nav,4) as id2_nav, nav, pos from nav;
>
> select ss.id_nav,ss.id2_nav,ss.level,ss.branch,n.nav,n.pos from nav_vw
> n, (select id_nav,id2_nav,level,branch from
> connectby('nav_vw','id_nav','id2_nav','0001',0,'~') as (id_nav text,
> id2_nav text, level int, branch text)) as ss where n.id_nav = ss.id_nav
> order by ss.branch;

Thanks for your reply.
I think for the moment I can live with that.
Although sorting by branch means sorting by primary key (which is more
or less random).
Even the array wouldn't be much better, it would just help getting rid
of the need for padding.

I think the best solution would be to:

0) new parameter for connectby() telling the column to ORDER BY
1) create a new column with SERIAL
2) when selecting all children of an element ORDER BY pos
3) order by 1)

For example (if the pos column is harcoded):

The line:
--------------------8<------------------------------------------------
appendStringInfo(sql, "SELECT %s, %s FROM %s WHERE %s = '%s' AND %s
IS NOT NULL ORDER BY pos",
--------------------8<------------------------------------------------
has to be changed

build_tuplestore_recursively should get a static counter which should be
incremented and stored with each tuple.

Shouldn't that be all ?

thx
--
e-Trolley Sayegh & John, Nabil Sayegh
Tel.: 0700 etrolley /// 0700 38765539
Fax.: +49 69 8299381-8
PGP : http://www.e-trolley.de

Nabil Sayegh wrote:
> For example (if the pos column is harcoded):
>
> The line:
> --------------------8<------------------------------------------------
> appendStringInfo(sql, "SELECT %s, %s FROM %s WHERE %s = '%s' AND %s
> IS NOT NULL ORDER BY pos",
> --------------------8<------------------------------------------------
> has to be changed
>
> build_tuplestore_recursively should get a static counter which should be
> incremented and stored with each tuple.
>
> Shouldn't that be all ?

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 ;-)

Otherwise, I'll see what I can do between now and the 7.4 feature freeze
(July 1), but as I said, no promises.

Joe

Am Son, 2003-06-22 um 02.09 schrieb Joe Conway:

> Otherwise, I'll see what I can do between now and the 7.4 feature freeze
> (July 1), but as I said, no promises.

No problem.

Just one last question:
Will this feature be available to 7.3 ?
I'm upgrading our servers to 7.3 now, and have enough trouble with some
date issues, and sometimes it takes long for new packages to hit debian
testing.

bye
--
e-Trolley Sayegh & John, Nabil Sayegh
Tel.: 0700 etrolley /// 0700 38765539
Fax.: +49 69 8299381-8
PGP : http://www.e-trolley.de

Nabil Sayegh wrote:
> Just one last question:
> Will this feature be available to 7.3 ?

Whenever I do make the changes, I'll try to keep them compatible with
7.3 and post a copy. Shouldn't be too hard -- the current cvs version
(i.e. 7.4devel) of contrib/tablefunc still works with 7.3 and is posted
here:
http://www.joeconway.com/

Joe

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

want an auto timestamping on my table.. SQL of create:
create table healthnotes ( "posted" timestamptz not null default
current_timestamp,
"notes" text,
primary key (posted))

was succ. but how do I set it to autotimestamp when adding entries?

tried:

insert into healthnotes values ('my text notes entry my text notes
entry my text notes entry')

but said the value for timestamp was too long
clearly the autotimestamp isn't set up yet

Michael

Am Mit, 2003-06-25 um 20.17 schrieb Michael Hanna:
> want an auto timestamping on my table.. SQL of create:
> create table healthnotes ( "posted" timestamptz not null default
> current_timestamp,
> "notes" text,
> primary key (posted))
>
> was succ. but how do I set it to autotimestamp when adding entries?
>
> tried:
>
> insert into healthnotes values ('my text notes entry my text notes
> entry my text notes entry')
>
> but said the value for timestamp was too long
> clearly the autotimestamp isn't set up yet

insert into healthnotes (notes) values ('my text notes entry my text
notes entry my text notes entry');

HTH
--
e-Trolley Sayegh & John, Nabil Sayegh
Tel.: 0700 etrolley /// 0700 38765539
Fax.: +49 69 8299381-8
PGP : http://www.e-trolley.de

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

Hi, it's me again.

I thought about that branch as array thing.

What would yout think about a function that ascends a tree from a given
node to the topmost and returns the path as a table ?

Implementing the array n connectby is fine, but as I don't like arrays
too much :) maybe there should be a dedicated reverselookup, too.
If someone is only interested in the path to 1 node, this function could
be handier and possibly faster.

Am Sam, 2003-06-21 um 06.20 schrieb Joe Conway:

> In 7.4 there may be a slightly better workaround (if a submitted patch
> gets accepted). You can convert branch into an array of integers, and
> order by that:
>
> select ss.id_nav, ss.id2_nav, ss.level,
> string_to_array(ss.branch,'~')::int[] as branch, n.nav,n.pos from nav n,
> (select id_nav,id2_nav,level,branch from
> connectby('nav','id_nav','id2_nav','1',0,'~') as (id_nav int, id2_nav
> int, level int, branch text)) as ss where n.id_nav = ss.id_nav order by
> string_to_array(ss.branch,'~')::int[];
> id_nav | id2_nav | level | branch | nav | pos
> --------+---------+-------+----------+---------+-----
> 1 | | 0 | {1} | World | 0
> 2 | 1 | 1 | {1,2} | Top | 1
> 5 | 2 | 2 | {1,2,5} | Home | 0
> 6 | 2 | 2 | {1,2,6} | News | 1
> 3 | 1 | 1 | {1,3} | Left | 2
> 4 | 1 | 1 | {1,4} | Support | 0
> 7 | 4 | 2 | {1,4,7} | Contact | 0
> 8 | 4 | 2 | {1,4,8} | Search | 1
> 9 | 4 | 2 | {1,4,9} | Sitemap | 2
> 10 | 4 | 2 | {1,4,10} | Imprint | 3
> (10 rows)

--
e-Trolley Sayegh & John, Nabil Sayegh
Tel.: 0700 etrolley /// 0700 38765539
Fax.: +49 69 8299381-8
PGP : http://www.e-trolley.de

Nabil Sayegh wrote:
> What would yout think about a function that ascends a tree from a given
> node to the topmost and returns the path as a table ?
>

Not sure I understand what you're proposing correctly, but can't you
just reverse the parent/child id fields?

Joe

Am Sam, 2003-06-28 um 01.26 schrieb Joe Conway:
> Nabil Sayegh wrote:
> > What would yout think about a function that ascends a tree from a given
> > node to the topmost and returns the path as a table ?
> >
>
> Not sure I understand what you're proposing correctly, but can't you
> just reverse the parent/child id fields?

Hm, Eh, yup, that helped :)

thx

P.S: That should go into the README
--
e-Trolley Sayegh & John, Nabil Sayegh
Tel.: 0700 etrolley /// 0700 38765539
Fax.: +49 69 8299381-8
PGP : http://www.e-trolley.de

Hi!

I know I'm butting in and I'll admit I can't follow exactly what the
discussion has been, but it seems (to me at least) to be related to an
article I recently read (following a link on Mark Pilgrim's site
diveintomark.org:

Storing Hierarchical Data in a Database by Gijs Van Tulder
<http://www.sitepoint.com/article/1105/1>

The further readings suggested at the end of the article were helpful
to me as well.

If it's not helpful, sorry for the interruption! I'll go back to
lurking now.

Michael Glaesemann
grzm myrealbox com

On Saturday, Jun 28, 2003, at 08:40 Asia/Tokyo, Nabil Sayegh wrote:

> Am Sam, 2003-06-28 um 01.26 schrieb Joe Conway:
>> Nabil Sayegh wrote:
>>> What would yout think about a function that ascends a tree from a
>>> given
>>> node to the topmost and returns the path as a table ?
>>>
>>
>> Not sure I understand what you're proposing correctly, but can't you
>> just reverse the parent/child id fields?
>
> Hm, Eh, yup, that helped :)
>
> thx
>
> P.S: That should go into the README
> --
> e-Trolley Sayegh & John, Nabil Sayegh
> Tel.: 0700 etrolley /// 0700 38765539
> Fax.: +49 69 8299381-8
> PGP : http://www.e-trolley.de
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 8: explain analyze is your friend
>
>

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
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Bruce Momjian wrote:
> Joe, would you comment on this change to tablefunc connectby?
>

I actually tied up a few loose ends on this and submitted it to PATCHES
on 6/26/2003. See:
http://archives.postgresql.org/pgsql-patches/2003-06/msg00357.php

Joe

I can not find any patch from that date with your name on it, and I
can't find a patch at all with connectby from you.

Of course, please repost it if you have it (archives are down), but then
again, where has the patch gone? Why isn't it in my mailbox?

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

Joe Conway wrote:
> Bruce Momjian wrote:
> > Joe, would you comment on this change to tablefunc connectby?
> >
>
> I actually tied up a few loose ends on this and submitted it to PATCHES
> on 6/26/2003. See:
> http://archives.postgresql.org/pgsql-patches/2003-06/msg00357.php
>
> Joe
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

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

Your patch has been added to the PostgreSQL unapplied patches list at:

http://momjian.postgresql.org/cgi-bin/pgpatches

I will try to apply it within the next 48 hours.

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

Joe Conway wrote:
> I'm going to resend the patches that I have outstanding since it appears
> some may have been lost. Here's the second of three.
> ====================================================
>
>
> Nabil Sayegh wrote:
> > Am Son, 2003-06-22 um 02.09 schrieb Joe Conway:
> >>Sounds like all that's needed for your case. But to be complete, in
> >>addition to changing tablefunc.c we'd have to:
> >>1) come up with a new function call signature that makes sense and does
> >>not cause backward compatibility problems for other people
> >>2) make needed changes to tablefunc.sql.in
> >>3) adjust the README.tablefunc appropriately
> >>4) adjust the regression test for new functionality
> >>5) be sure we don't break any of the old cases
> >>
> >>If you want to submit a complete patch, it would be gratefully accepted
> >>-- for review at least ;-)
> >
> > Here's the patch, at least for steps 1-3
> > I don't know anything about regression tests :(
> >
> > However, I included a patch against 7.3.3
> >
>
> Nice work Nabil!
>
> I've merged the patch with cvs HEAD, added to the regression tests, and
> verified no backward compatibility issues. Please apply.
>
> FYI Nabil, if you want to run the regression test, cd to
> contrib/tablefunc as user postgres (or whoever postgresql runs as, and
> be sure they have full permission on contrib/tablefunc directory) and run:
>
> make installcheck
>
> The test script that gets run is in contrib/tablefunc/sql, the expected
> output is in contrib/tablefunc/expected, and the actual output is in
> contrib/tablefunc/results. If the test fails you'll find regression.diff
> in contrib/tablefunc.
>
> I'll send you a tarred copy of contrib/tablefunc (off list) to try
> yourself on 7.3.3, as I don't think this patch will apply cleanly to it.
> It ought to work on 7.3.3, and it includes enhance crosstab functionality.
>
> Thanks!
>
> Joe
>

> Index: contrib/tablefunc/README.tablefunc
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/contrib/tablefunc/README.tablefunc,v
> retrieving revision 1.6
> diff -c -r1.6 README.tablefunc
> *** contrib/tablefunc/README.tablefunc 20 Mar 2003 06:46:30 -0000 1.6
> --- contrib/tablefunc/README.tablefunc 26 Jun 2003 16:44:17 -0000
> ***************
> *** 4,9 ****
> --- 4,11 ----
> * Sample to demonstrate C functions which return setof scalar
> * and setof composite.
> * Joe Conway <mail(at)joeconway(dot)com>
> + * And contributors:
> + * Nabil Sayegh <postgresql(at)e-trolley(dot)de>
> *
> * Copyright 2002 by PostgreSQL Global Development Group
> *
> ***************
> *** 60,68 ****
> - requires anonymous composite type syntax in the FROM clause. See
> the instructions in the documentation below.
>
> ! connectby(text relname, text keyid_fld, text parent_keyid_fld,
> ! text start_with, int max_depth [, text branch_delim])
> - returns keyid, parent_keyid, level, and an optional branch string
> - requires anonymous composite type syntax in the FROM clause. See
> the instructions in the documentation below.
>
> --- 62,72 ----
> - requires anonymous composite type syntax in the FROM clause. See
> the instructions in the documentation below.
>
> ! connectby(text relname, text keyid_fld, text parent_keyid_fld
> ! [, text orderby_fld], text start_with, int max_depth
> ! [, text branch_delim])
> - returns keyid, parent_keyid, level, and an optional branch string
> + and an optional serial column for ordering siblings
> - requires anonymous composite type syntax in the FROM clause. See
> the instructions in the documentation below.
>
> ***************
> *** 452,464 ****
> ==================================================================
> Name
>
> ! connectby(text, text, text, text, int[, text]) - returns a set
> representing a hierarchy (tree structure)
>
> Synopsis
>
> ! connectby(text relname, text keyid_fld, text parent_keyid_fld,
> ! text start_with, int max_depth [, text branch_delim])
>
> Inputs
>
> --- 456,469 ----
> ==================================================================
> Name
>
> ! connectby(text, text, text[, text], text, text, int[, text]) - returns a set
> representing a hierarchy (tree structure)
>
> Synopsis
>
> ! connectby(text relname, text keyid_fld, text parent_keyid_fld
> ! [, text orderby_fld], text start_with, int max_depth
> ! [, text branch_delim])
>
> Inputs
>
> ***************
> *** 474,479 ****
> --- 479,489 ----
>
> Name of the key_parent field
>
> + orderby_fld
> +
> + If optional ordering of siblings is desired:
> + Name of the field to order siblings
> +
> start_with
>
> root value of the tree input as a text value regardless of keyid_fld type
> ***************
> *** 500,505 ****
> --- 510,525 ----
>
> SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'row2', 0)
> AS t(keyid text, parent_keyid text, level int);
> +
> + - or -
> +
> + SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'pos', 'row2', 0, '~')
> + AS t(keyid text, parent_keyid text, level int, branch text, pos int);
> +
> + - or -
> +
> + SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'pos', 'row2', 0)
> + AS t(keyid text, parent_keyid text, level int, pos int);
>
> Notes
>
> ***************
> *** 520,541 ****
> 5. The parameters representing table and field names must include double
> quotes if the names are mixed-case or contain special characters.
>
>
> Example usage
>
> ! CREATE TABLE connectby_tree(keyid text, parent_keyid text);
>
> ! INSERT INTO connectby_tree VALUES('row1',NULL);
> ! INSERT INTO connectby_tree VALUES('row2','row1');
> ! INSERT INTO connectby_tree VALUES('row3','row1');
> ! INSERT INTO connectby_tree VALUES('row4','row2');
> ! INSERT INTO connectby_tree VALUES('row5','row2');
> ! INSERT INTO connectby_tree VALUES('row6','row4');
> ! INSERT INTO connectby_tree VALUES('row7','row3');
> ! INSERT INTO connectby_tree VALUES('row8','row6');
> ! INSERT INTO connectby_tree VALUES('row9','row5');
>
> ! -- with branch
> SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'row2', 0, '~')
> AS t(keyid text, parent_keyid text, level int, branch text);
> keyid | parent_keyid | level | branch
> --- 540,564 ----
> 5. The parameters representing table and field names must include double
> quotes if the names are mixed-case or contain special characters.
>
> + 6. If sorting of siblings is desired, the orderby_fld input parameter *and*
> + a name for the resulting serial field (type INT32) in the query column
> + definition must be given.
>
> Example usage
>
> ! CREATE TABLE connectby_tree(keyid text, parent_keyid text, pos int);
>
> ! INSERT INTO connectby_tree VALUES('row1',NULL, 0);
> ! INSERT INTO connectby_tree VALUES('row2','row1', 0);
> ! INSERT INTO connectby_tree VALUES('row3','row1', 0);
> ! INSERT INTO connectby_tree VALUES('row4','row2', 1);
> ! INSERT INTO connectby_tree VALUES('row5','row2', 0);
> ! INSERT INTO connectby_tree VALUES('row6','row4', 0);
> ! INSERT INTO connectby_tree VALUES('row7','row3', 0);
> ! INSERT INTO connectby_tree VALUES('row8','row6', 0);
> ! INSERT INTO connectby_tree VALUES('row9','row5', 0);
>
> ! -- with branch, without orderby_fld
> SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'row2', 0, '~')
> AS t(keyid text, parent_keyid text, level int, branch text);
> keyid | parent_keyid | level | branch
> ***************
> *** 548,554 ****
> row9 | row5 | 2 | row2~row5~row9
> (6 rows)
>
> ! -- without branch
> SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'row2', 0)
> AS t(keyid text, parent_keyid text, level int);
> keyid | parent_keyid | level
> --- 571,577 ----
> row9 | row5 | 2 | row2~row5~row9
> (6 rows)
>
> ! -- without branch, without orderby_fld
> SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'row2', 0)
> AS t(keyid text, parent_keyid text, level int);
> keyid | parent_keyid | level
> ***************
> *** 559,564 ****
> --- 582,613 ----
> row8 | row6 | 3
> row5 | row2 | 1
> row9 | row5 | 2
> + (6 rows)
> +
> + -- with branch, with orderby_fld (notice that row5 comes before row4)
> + SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'pos', 'row2', 0, '~')
> + AS t(keyid text, parent_keyid text, level int, branch text, pos int) ORDER BY t.pos;
> + keyid | parent_keyid | level | branch | pos
> + -------+--------------+-------+---------------------+-----
> + row2 | | 0 | row2 | 1
> + row5 | row2 | 1 | row2~row5 | 2
> + row9 | row5 | 2 | row2~row5~row9 | 3
> + row4 | row2 | 1 | row2~row4 | 4
> + row6 | row4 | 2 | row2~row4~row6 | 5
> + row8 | row6 | 3 | row2~row4~row6~row8 | 6
> + (6 rows)
> +
> + -- without branch, with orderby_fld (notice that row5 comes before row4)
> + SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'pos', 'row2', 0)
> + AS t(keyid text, parent_keyid text, level int, pos int) ORDER BY t.pos;
> + keyid | parent_keyid | level | pos
> + -------+--------------+-------+-----
> + row2 | | 0 | 1
> + row5 | row2 | 1 | 2
> + row9 | row5 | 2 | 3
> + row4 | row2 | 1 | 4
> + row6 | row4 | 2 | 5
> + row8 | row6 | 3 | 6
> (6 rows)
>
> ==================================================================
> Index: contrib/tablefunc/tablefunc.c
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/contrib/tablefunc/tablefunc.c,v
> retrieving revision 1.19
> diff -c -r1.19 tablefunc.c
> *** contrib/tablefunc/tablefunc.c 25 Jun 2003 18:13:50 -0000 1.19
> --- contrib/tablefunc/tablefunc.c 26 Jun 2003 16:44:27 -0000
> ***************
> *** 4,9 ****
> --- 4,11 ----
> * Sample to demonstrate C functions which return setof scalar
> * and setof composite.
> * Joe Conway <mail(at)joeconway(dot)com>
> + * And contributors:
> + * Nabil Sayegh <postgresql(at)e-trolley(dot)de>
> *
> * Copyright 2002 by PostgreSQL Global Development Group
> *
> ***************
> *** 45,51 ****
> int num_categories,
> TupleDesc tupdesc,
> MemoryContext per_query_ctx);
> ! static void validateConnectbyTupleDesc(TupleDesc tupdesc, bool show_branch);
> static bool compatCrosstabTupleDescs(TupleDesc tupdesc1, TupleDesc tupdesc2);
> static bool compatConnectbyTupleDescs(TupleDesc tupdesc1, TupleDesc tupdesc2);
> static void get_normal_pair(float8 *x1, float8 *x2);
> --- 47,53 ----
> int num_categories,
> TupleDesc tupdesc,
> MemoryContext per_query_ctx);
> ! static void validateConnectbyTupleDesc(TupleDesc tupdesc, bool show_branch, bool show_serial);
> static bool compatCrosstabTupleDescs(TupleDesc tupdesc1, TupleDesc tupdesc2);
> static bool compatConnectbyTupleDescs(TupleDesc tupdesc1, TupleDesc tupdesc2);
> static void get_normal_pair(float8 *x1, float8 *x2);
> ***************
> *** 54,74 ****
> --- 56,81 ----
> static Tuplestorestate *connectby(char *relname,
> char *key_fld,
> char *parent_key_fld,
> + char *orderby_fld,
> char *branch_delim,
> char *start_with,
> int max_depth,
> bool show_branch,
> + bool show_serial,
> MemoryContext per_query_ctx,
> AttInMetadata *attinmeta);
> static Tuplestorestate *build_tuplestore_recursively(char *key_fld,
> char *parent_key_fld,
> char *relname,
> + char *orderby_fld,
> char *branch_delim,
> char *start_with,
> char *branch,
> int level,
> + int *serial,
> int max_depth,
> bool show_branch,
> + bool show_serial,
> MemoryContext per_query_ctx,
> AttInMetadata *attinmeta,
> Tuplestorestate *tupstore);
> ***************
> *** 998,1028 ****
> *
> * e.g. given table foo:
> *
> ! * keyid parent_keyid
> ! * ------+--------------
> ! * row1 NULL
> ! * row2 row1
> ! * row3 row1
> ! * row4 row2
> ! * row5 row2
> ! * row6 row4
> ! * row7 row3
> ! * row8 row6
> ! * row9 row5
> ! *
> ! *
> ! * connectby(text relname, text keyid_fld, text parent_keyid_fld,
> ! * text start_with, int max_depth [, text branch_delim])
> ! * connectby('foo', 'keyid', 'parent_keyid', 'row2', 0, '~') returns:
> *
> ! * keyid parent_id level branch
> * ------+-----------+--------+-----------------------
> ! * row2 NULL 0 row2
> ! * row4 row2 1 row2~row4
> ! * row6 row4 2 row2~row4~row6
> ! * row8 row6 3 row2~row4~row6~row8
> ! * row5 row2 1 row2~row5
> ! * row9 row5 2 row2~row5~row9
> *
> */
> PG_FUNCTION_INFO_V1(connectby_text);
> --- 1005,1036 ----
> *
> * e.g. given table foo:
> *
> ! * keyid parent_keyid pos
> ! * ------+------------+--
> ! * row1 NULL 0
> ! * row2 row1 0
> ! * row3 row1 0
> ! * row4 row2 1
> ! * row5 row2 0
> ! * row6 row4 0
> ! * row7 row3 0
> ! * row8 row6 0
> ! * row9 row5 0
> ! *
> ! *
> ! * connectby(text relname, text keyid_fld, text parent_keyid_fld
> ! * [, text orderby_fld], text start_with, int max_depth
> ! * [, text branch_delim])
> ! * connectby('foo', 'keyid', 'parent_keyid', 'pos', 'row2', 0, '~') returns:
> *
> ! * keyid parent_id level branch serial
> * ------+-----------+--------+-----------------------
> ! * row2 NULL 0 row2 1
> ! * row5 row2 1 row2~row5 2
> ! * row9 row5 2 row2~row5~row9 3
> ! * row4 row2 1 row2~row4 4
> ! * row6 row4 2 row2~row4~row6 5
> ! * row8 row6 3 row2~row4~row6~row8 6
> *
> */
> PG_FUNCTION_INFO_V1(connectby_text);
> ***************
> *** 1040,1045 ****
> --- 1048,1054 ----
> int max_depth = PG_GETARG_INT32(4);
> char *branch_delim = NULL;
> bool show_branch = false;
> + bool show_serial = false;
> ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
> TupleDesc tupdesc;
> AttInMetadata *attinmeta;
> ***************
> *** 1067,1073 ****
> tupdesc = CreateTupleDescCopy(rsinfo->expectedDesc);
>
> /* does it meet our needs */
> ! validateConnectbyTupleDesc(tupdesc, show_branch);
>
> /* OK, use it then */
> attinmeta = TupleDescGetAttInMetadata(tupdesc);
> --- 1076,1082 ----
> tupdesc = CreateTupleDescCopy(rsinfo->expectedDesc);
>
> /* does it meet our needs */
> ! validateConnectbyTupleDesc(tupdesc, show_branch, show_serial);
>
> /* OK, use it then */
> attinmeta = TupleDescGetAttInMetadata(tupdesc);
> ***************
> *** 1082,1091 ****
> --- 1091,1102 ----
> rsinfo->setResult = connectby(relname,
> key_fld,
> parent_key_fld,
> + NULL,
> branch_delim,
> start_with,
> max_depth,
> show_branch,
> + show_serial,
> per_query_ctx,
> attinmeta);
> rsinfo->setDesc = tupdesc;
> ***************
> *** 1102,1107 ****
> --- 1113,1197 ----
> return (Datum) 0;
> }
>
> + PG_FUNCTION_INFO_V1(connectby_text_serial);
> + Datum
> + connectby_text_serial(PG_FUNCTION_ARGS)
> + {
> + char *relname = GET_STR(PG_GETARG_TEXT_P(0));
> + char *key_fld = GET_STR(PG_GETARG_TEXT_P(1));
> + char *parent_key_fld = GET_STR(PG_GETARG_TEXT_P(2));
> + char *orderby_fld = GET_STR(PG_GETARG_TEXT_P(3));
> + char *start_with = GET_STR(PG_GETARG_TEXT_P(4));
> + int max_depth = PG_GETARG_INT32(5);
> + char *branch_delim = NULL;
> + bool show_branch = false;
> + bool show_serial = true;
> +
> + ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
> + TupleDesc tupdesc;
> + AttInMetadata *attinmeta;
> + MemoryContext per_query_ctx;
> + MemoryContext oldcontext;
> +
> + /* check to see if caller supports us returning a tuplestore */
> + if (!rsinfo || !(rsinfo->allowedModes & SFRM_Materialize))
> + elog(ERROR, "connectby: materialize mode required, but it is not "
> + "allowed in this context");
> +
> + if (fcinfo->nargs == 7)
> + {
> + branch_delim = GET_STR(PG_GETARG_TEXT_P(6));
> + show_branch = true;
> + }
> + else
> + /* default is no show, tilde for the delimiter */
> + branch_delim = pstrdup("~");
> +
> + per_query_ctx = rsinfo->econtext->ecxt_per_query_memory;
> + oldcontext = MemoryContextSwitchTo(per_query_ctx);
> +
> + /* get the requested return tuple description */
> + tupdesc = CreateTupleDescCopy(rsinfo->expectedDesc);
> +
> + /* does it meet our needs */
> + validateConnectbyTupleDesc(tupdesc, show_branch, show_serial);
> +
> + /* OK, use it then */
> + attinmeta = TupleDescGetAttInMetadata(tupdesc);
> +
> + /* check to see if caller supports us returning a tuplestore */
> + if (!rsinfo->allowedModes & SFRM_Materialize)
> + elog(ERROR, "connectby requires Materialize mode, but it is not "
> + "allowed in this context");
> +
> + /* OK, go to work */
> + rsinfo->returnMode = SFRM_Materialize;
> + rsinfo->setResult = connectby(relname,
> + key_fld,
> + parent_key_fld,
> + orderby_fld,
> + branch_delim,
> + start_with,
> + max_depth,
> + show_branch,
> + show_serial,
> + per_query_ctx,
> + attinmeta);
> + rsinfo->setDesc = tupdesc;
> +
> + MemoryContextSwitchTo(oldcontext);
> +
> + /*
> + * SFRM_Materialize mode expects us to return a NULL Datum. The actual
> + * tuples are in our tuplestore and passed back through
> + * rsinfo->setResult. rsinfo->setDesc is set to the tuple description
> + * that we actually used to build our tuples with, so the caller can
> + * verify we did what it was expecting.
> + */
> + return (Datum) 0;
> + }
> +
> +
> /*
> * connectby - does the real work for connectby_text()
> */
> ***************
> *** 1109,1118 ****
> --- 1199,1210 ----
> connectby(char *relname,
> char *key_fld,
> char *parent_key_fld,
> + char *orderby_fld,
> char *branch_delim,
> char *start_with,
> int max_depth,
> bool show_branch,
> + bool show_serial,
> MemoryContext per_query_ctx,
> AttInMetadata *attinmeta)
> {
> ***************
> *** 1120,1125 ****
> --- 1212,1219 ----
> int ret;
> MemoryContext oldcontext;
>
> + int serial = 1;
> +
> /* Connect to SPI manager */
> if ((ret = SPI_connect()) < 0)
> elog(ERROR, "connectby: SPI_connect returned %d", ret);
> ***************
> *** 1136,1147 ****
> --- 1230,1244 ----
> tupstore = build_tuplestore_recursively(key_fld,
> parent_key_fld,
> relname,
> + orderby_fld,
> branch_delim,
> start_with,
> start_with, /* current_branch */
> 0, /* initial level is 0 */
> + &serial, /* initial serial is 1 */
> max_depth,
> show_branch,
> + show_serial,
> per_query_ctx,
> attinmeta,
> tupstore);
> ***************
> *** 1155,1166 ****
> --- 1252,1266 ----
> build_tuplestore_recursively(char *key_fld,
> char *parent_key_fld,
> char *relname,
> + char *orderby_fld,
> char *branch_delim,
> char *start_with,
> char *branch,
> int level,
> + int *serial,
> int max_depth,
> bool show_branch,
> + bool show_serial,
> MemoryContext per_query_ctx,
> AttInMetadata *attinmeta,
> Tuplestorestate *tupstore)
> ***************
> *** 1170,1187 ****
> StringInfo sql = makeStringInfo();
> int ret;
> int proc;
>
> if (max_depth > 0 && level > max_depth)
> return tupstore;
>
> /* Build initial sql statement */
> ! appendStringInfo(sql, "SELECT %s, %s FROM %s WHERE %s = '%s' AND %s IS NOT NULL",
> key_fld,
> parent_key_fld,
> relname,
> parent_key_fld,
> start_with,
> key_fld);
>
> /* Retrieve the desired rows */
> ret = SPI_exec(sql->data, 0);
> --- 1270,1304 ----
> StringInfo sql = makeStringInfo();
> int ret;
> int proc;
> + int serial_column;
>
> if (max_depth > 0 && level > max_depth)
> return tupstore;
>
> /* Build initial sql statement */
> ! if (!show_serial)
> ! {
> ! appendStringInfo(sql, "SELECT %s, %s FROM %s WHERE %s = '%s' AND %s IS NOT NULL",
> key_fld,
> parent_key_fld,
> relname,
> parent_key_fld,
> start_with,
> key_fld);
> + serial_column=0;
> + }
> + else
> + {
> + appendStringInfo(sql, "SELECT %s, %s FROM %s WHERE %s = '%s' AND %s IS NOT NULL ORDER BY %s",
> + key_fld,
> + parent_key_fld,
> + relname,
> + parent_key_fld,
> + start_with,
> + key_fld,
> + orderby_fld);
> + serial_column=1;
> + }
>
> /* Retrieve the desired rows */
> ret = SPI_exec(sql->data, 0);
> ***************
> *** 1198,1203 ****
> --- 1315,1321 ----
> char *current_key;
> char *current_key_parent;
> char current_level[INT32_STRLEN];
> + char serial_str[INT32_STRLEN];
> char *current_branch;
> char **values;
> StringInfo branchstr = NULL;
> ***************
> *** 1212,1220 ****
> chk_current_key = makeStringInfo();
>
> if (show_branch)
> ! values = (char **) palloc(CONNECTBY_NCOLS * sizeof(char *));
> else
> ! values = (char **) palloc(CONNECTBY_NCOLS_NOBRANCH * sizeof(char *));
>
> /* First time through, do a little setup */
> if (level == 0)
> --- 1330,1338 ----
> chk_current_key = makeStringInfo();
>
> if (show_branch)
> ! values = (char **) palloc((CONNECTBY_NCOLS + serial_column) * sizeof(char *));
> else
> ! values = (char **) palloc((CONNECTBY_NCOLS_NOBRANCH + serial_column) * sizeof(char *));
>
> /* First time through, do a little setup */
> if (level == 0)
> ***************
> *** 1243,1248 ****
> --- 1361,1376 ----
> if (show_branch)
> values[3] = start_with;
>
> + /* root starts the serial with 1 */
> + if (show_serial)
> + {
> + sprintf(serial_str, "%d", (*serial)++);
> + if (show_branch)
> + values[4] = serial_str;
> + else
> + values[3] = serial_str;
> + }
> +
> /* construct the tuple */
> tuple = BuildTupleFromCStrings(attinmeta, values);
>
> ***************
> *** 1290,1295 ****
> --- 1418,1431 ----
> values[2] = current_level;
> if (show_branch)
> values[3] = current_branch;
> + if (show_serial)
> + {
> + sprintf(serial_str, "%d", (*serial)++);
> + if (show_branch)
> + values[4] = serial_str;
> + else
> + values[3] = serial_str;
> + }
>
> tuple = BuildTupleFromCStrings(attinmeta, values);
>
> ***************
> *** 1311,1322 ****
> --- 1447,1461 ----
> tupstore = build_tuplestore_recursively(key_fld,
> parent_key_fld,
> relname,
> + orderby_fld,
> branch_delim,
> values[0],
> current_branch,
> level + 1,
> + serial,
> max_depth,
> show_branch,
> + show_serial,
> per_query_ctx,
> attinmeta,
> tupstore);
> ***************
> *** 1340,1357 ****
> * Check expected (query runtime) tupdesc suitable for Connectby
> */
> static void
> ! validateConnectbyTupleDesc(TupleDesc tupdesc, bool show_branch)
> {
> /* are there the correct number of columns */
> if (show_branch)
> {
> ! if (tupdesc->natts != CONNECTBY_NCOLS)
> elog(ERROR, "Query-specified return tuple not valid for Connectby: "
> "wrong number of columns");
> }
> else
> {
> ! if (tupdesc->natts != CONNECTBY_NCOLS_NOBRANCH)
> elog(ERROR, "Query-specified return tuple not valid for Connectby: "
> "wrong number of columns");
> }
> --- 1479,1501 ----
> * Check expected (query runtime) tupdesc suitable for Connectby
> */
> static void
> ! validateConnectbyTupleDesc(TupleDesc tupdesc, bool show_branch, bool show_serial)
> {
> + int serial_column=0;
> +
> + if (show_serial)
> + serial_column=1;
> +
> /* are there the correct number of columns */
> if (show_branch)
> {
> ! if (tupdesc->natts != (CONNECTBY_NCOLS + serial_column))
> elog(ERROR, "Query-specified return tuple not valid for Connectby: "
> "wrong number of columns");
> }
> else
> {
> ! if (tupdesc->natts != CONNECTBY_NCOLS_NOBRANCH + serial_column)
> elog(ERROR, "Query-specified return tuple not valid for Connectby: "
> "wrong number of columns");
> }
> ***************
> *** 1371,1376 ****
> --- 1515,1530 ----
> elog(ERROR, "Query-specified return tuple not valid for Connectby: "
> "fourth column must be type %s", format_type_be(TEXTOID));
>
> + /* check that the type of the fifth column is INT4 */
> + if (show_branch && show_serial && tupdesc->attrs[4]->atttypid != INT4OID)
> + elog(ERROR, "Query-specified return tuple not valid for Connectby: "
> + "fifth column must be type %s", format_type_be(INT4OID));
> +
> + /* check that the type of the fifth column is INT4 */
> + if (!show_branch && show_serial && tupdesc->attrs[3]->atttypid != INT4OID)
> + elog(ERROR, "Query-specified return tuple not valid for Connectby: "
> + "fourth column must be type %s", format_type_be(INT4OID));
> +
> /* OK, the tupdesc is valid for our purposes */
> }
>
> Index: contrib/tablefunc/tablefunc.h
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/contrib/tablefunc/tablefunc.h,v
> retrieving revision 1.5
> diff -c -r1.5 tablefunc.h
> *** contrib/tablefunc/tablefunc.h 20 Mar 2003 06:46:30 -0000 1.5
> --- contrib/tablefunc/tablefunc.h 26 Jun 2003 16:44:34 -0000
> ***************
> *** 4,9 ****
> --- 4,11 ----
> * Sample to demonstrate C functions which return setof scalar
> * and setof composite.
> * Joe Conway <mail(at)joeconway(dot)com>
> + * And contributors:
> + * Nabil Sayegh <postgresql(at)e-trolley(dot)de>
> *
> * Copyright 2002 by PostgreSQL Global Development Group
> *
> ***************
> *** 36,40 ****
> --- 38,43 ----
> extern Datum crosstab(PG_FUNCTION_ARGS);
> extern Datum crosstab_hash(PG_FUNCTION_ARGS);
> extern Datum connectby_text(PG_FUNCTION_ARGS);
> + extern Datum connectby_text_serial(PG_FUNCTION_ARGS);
>
> #endif /* TABLEFUNC_H */
> Index: contrib/tablefunc/tablefunc.sql.in
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/contrib/tablefunc/tablefunc.sql.in,v
> retrieving revision 1.7
> diff -c -r1.7 tablefunc.sql.in
> *** contrib/tablefunc/tablefunc.sql.in 14 May 2003 03:25:57 -0000 1.7
> --- contrib/tablefunc/tablefunc.sql.in 26 Jun 2003 16:19:29 -0000
> ***************
> *** 64,66 ****
> --- 64,78 ----
> RETURNS setof record
> AS 'MODULE_PATHNAME','connectby_text'
> LANGUAGE 'C' STABLE STRICT;
> +
> + -- These 2 take the name of a field to ORDER BY as 4th arg (for sorting siblings)
> +
> + CREATE OR REPLACE FUNCTION connectby(text,text,text,text,text,int,text)
> + RETURNS setof record
> + AS 'MODULE_PATHNAME','connectby_text_serial'
> + LANGUAGE 'C' STABLE STRICT;
> +
> + CREATE OR REPLACE FUNCTION connectby(text,text,text,text,text,int)
> + RETURNS setof record
> + AS 'MODULE_PATHNAME','connectby_text_serial'
> + LANGUAGE 'C' STABLE STRICT;
> Index: contrib/tablefunc/data/connectby_text.data
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/contrib/tablefunc/data/connectby_text.data,v
> retrieving revision 1.1
> diff -c -r1.1 connectby_text.data
> *** contrib/tablefunc/data/connectby_text.data 12 Sep 2002 00:14:40 -0000 1.1
> --- contrib/tablefunc/data/connectby_text.data 26 Jun 2003 16:31:47 -0000
> ***************
> *** 1,9 ****
> ! row1 \N
> ! row2 row1
> ! row3 row1
> ! row4 row2
> ! row5 row2
> ! row6 row4
> ! row7 row3
> ! row8 row6
> ! row9 row5
> --- 1,9 ----
> ! row1 \N 0
> ! row2 row1 0
> ! row3 row1 0
> ! row4 row2 1
> ! row5 row2 0
> ! row6 row4 0
> ! row7 row3 0
> ! row8 row6 0
> ! row9 row5 0
> Index: contrib/tablefunc/expected/tablefunc.out
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/contrib/tablefunc/expected/tablefunc.out,v
> retrieving revision 1.6
> diff -c -r1.6 tablefunc.out
> *** contrib/tablefunc/expected/tablefunc.out 20 Mar 2003 06:46:30 -0000 1.6
> --- contrib/tablefunc/expected/tablefunc.out 26 Jun 2003 16:37:27 -0000
> ***************
> *** 197,205 ****
> -- connectby
> --
> -- test connectby with text based hierarchy
> ! CREATE TABLE connectby_text(keyid text, parent_keyid text);
> \copy connectby_text from 'data/connectby_text.data'
> ! -- with branch
> SELECT * FROM connectby('connectby_text', 'keyid', 'parent_keyid', 'row2', 0, '~') AS t(keyid text, parent_keyid text, level int, branch text);
> keyid | parent_keyid | level | branch
> -------+--------------+-------+---------------------
> --- 197,205 ----
> -- connectby
> --
> -- test connectby with text based hierarchy
> ! CREATE TABLE connectby_text(keyid text, parent_keyid text, pos int);
> \copy connectby_text from 'data/connectby_text.data'
> ! -- with branch, without orderby
> SELECT * FROM connectby('connectby_text', 'keyid', 'parent_keyid', 'row2', 0, '~') AS t(keyid text, parent_keyid text, level int, branch text);
> keyid | parent_keyid | level | branch
> -------+--------------+-------+---------------------
> ***************
> *** 211,217 ****
> row9 | row5 | 2 | row2~row5~row9
> (6 rows)
>
> ! -- without branch
> SELECT * FROM connectby('connectby_text', 'keyid', 'parent_keyid', 'row2', 0) AS t(keyid text, parent_keyid text, level int);
> keyid | parent_keyid | level
> -------+--------------+-------
> --- 211,217 ----
> row9 | row5 | 2 | row2~row5~row9
> (6 rows)
>
> ! -- without branch, without orderby
> SELECT * FROM connectby('connectby_text', 'keyid', 'parent_keyid', 'row2', 0) AS t(keyid text, parent_keyid text, level int);
> keyid | parent_keyid | level
> -------+--------------+-------
> ***************
> *** 221,226 ****
> --- 221,250 ----
> row8 | row6 | 3
> row5 | row2 | 1
> row9 | row5 | 2
> + (6 rows)
> +
> + -- with branch, with orderby
> + SELECT * FROM connectby('connectby_text', 'keyid', 'parent_keyid', 'pos', 'row2', 0, '~') AS t(keyid text, parent_keyid text, level int, branch text, pos int) ORDER BY t.pos;
> + keyid | parent_keyid | level | branch | pos
> + -------+--------------+-------+---------------------+-----
> + row2 | | 0 | row2 | 1
> + row5 | row2 | 1 | row2~row5 | 2
> + row9 | row5 | 2 | row2~row5~row9 | 3
> + row4 | row2 | 1 | row2~row4 | 4
> + row6 | row4 | 2 | row2~row4~row6 | 5
> + row8 | row6 | 3 | row2~row4~row6~row8 | 6
> + (6 rows)
> +
> + -- without branch, with orderby
> + SELECT * FROM connectby('connectby_text', 'keyid', 'parent_keyid', 'pos', 'row2', 0) AS t(keyid text, parent_keyid text, level int, pos int) ORDER BY t.pos;
> + keyid | parent_keyid | level | pos
> + -------+--------------+-------+-----
> + row2 | | 0 | 1
> + row5 | row2 | 1 | 2
> + row9 | row5 | 2 | 3
> + row4 | row2 | 1 | 4
> + row6 | row4 | 2 | 5
> + row8 | row6 | 3 | 6
> (6 rows)
>
> -- test connectby with int based hierarchy
> Index: contrib/tablefunc/sql/tablefunc.sql
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/contrib/tablefunc/sql/tablefunc.sql,v
> retrieving revision 1.7
> diff -c -r1.7 tablefunc.sql
> *** contrib/tablefunc/sql/tablefunc.sql 20 Mar 2003 06:46:30 -0000 1.7
> --- contrib/tablefunc/sql/tablefunc.sql 26 Jun 2003 16:37:23 -0000
> ***************
> *** 94,107 ****
> --
>
> -- test connectby with text based hierarchy
> ! CREATE TABLE connectby_text(keyid text, parent_keyid text);
> \copy connectby_text from 'data/connectby_text.data'
>
> ! -- with branch
> SELECT * FROM connectby('connectby_text', 'keyid', 'parent_keyid', 'row2', 0, '~') AS t(keyid text, parent_keyid text, level int, branch text);
>
> ! -- without branch
> SELECT * FROM connectby('connectby_text', 'keyid', 'parent_keyid', 'row2', 0) AS t(keyid text, parent_keyid text, level int);
>
> -- test connectby with int based hierarchy
> CREATE TABLE connectby_int(keyid int, parent_keyid int);
> --- 94,113 ----
> --
>
> -- test connectby with text based hierarchy
> ! CREATE TABLE connectby_text(keyid text, parent_keyid text, pos int);
> \copy connectby_text from 'data/connectby_text.data'
>
> ! -- with branch, without orderby
> SELECT * FROM connectby('connectby_text', 'keyid', 'parent_keyid', 'row2', 0, '~') AS t(keyid text, parent_keyid text, level int, branch text);
>
> ! -- without branch, without orderby
> SELECT * FROM connectby('connectby_text', 'keyid', 'parent_keyid', 'row2', 0) AS t(keyid text, parent_keyid text, level int);
> +
> + -- with branch, with orderby
> + SELECT * FROM connectby('connectby_text', 'keyid', 'parent_keyid', 'pos', 'row2', 0, '~') AS t(keyid text, parent_keyid text, level int, branch text, pos int) ORDER BY t.pos;
> +
> + -- without branch, with orderby
> + SELECT * FROM connectby('connectby_text', 'keyid', 'parent_keyid', 'pos', 'row2', 0) AS t(keyid text, parent_keyid text, level int, pos int) ORDER BY t.pos;
>
> -- test connectby with int based hierarchy
> CREATE TABLE connectby_int(keyid int, parent_keyid int);
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Patch applied. Thanks.

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

Joe Conway wrote:
> I'm going to resend the patches that I have outstanding since it appears
> some may have been lost. Here's the second of three.
> ====================================================
>
>
> Nabil Sayegh wrote:
> > Am Son, 2003-06-22 um 02.09 schrieb Joe Conway:
> >>Sounds like all that's needed for your case. But to be complete, in
> >>addition to changing tablefunc.c we'd have to:
> >>1) come up with a new function call signature that makes sense and does
> >>not cause backward compatibility problems for other people
> >>2) make needed changes to tablefunc.sql.in
> >>3) adjust the README.tablefunc appropriately
> >>4) adjust the regression test for new functionality
> >>5) be sure we don't break any of the old cases
> >>
> >>If you want to submit a complete patch, it would be gratefully accepted
> >>-- for review at least ;-)
> >
> > Here's the patch, at least for steps 1-3
> > I don't know anything about regression tests :(
> >
> > However, I included a patch against 7.3.3
> >
>
> Nice work Nabil!
>
> I've merged the patch with cvs HEAD, added to the regression tests, and
> verified no backward compatibility issues. Please apply.
>
> FYI Nabil, if you want to run the regression test, cd to
> contrib/tablefunc as user postgres (or whoever postgresql runs as, and
> be sure they have full permission on contrib/tablefunc directory) and run:
>
> make installcheck
>
> The test script that gets run is in contrib/tablefunc/sql, the expected
> output is in contrib/tablefunc/expected, and the actual output is in
> contrib/tablefunc/results. If the test fails you'll find regression.diff
> in contrib/tablefunc.
>
> I'll send you a tarred copy of contrib/tablefunc (off list) to try
> yourself on 7.3.3, as I don't think this patch will apply cleanly to it.
> It ought to work on 7.3.3, and it includes enhance crosstab functionality.
>
> Thanks!
>
> Joe
>

> Index: contrib/tablefunc/README.tablefunc
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/contrib/tablefunc/README.tablefunc,v
> retrieving revision 1.6
> diff -c -r1.6 README.tablefunc
> *** contrib/tablefunc/README.tablefunc 20 Mar 2003 06:46:30 -0000 1.6
> --- contrib/tablefunc/README.tablefunc 26 Jun 2003 16:44:17 -0000
> ***************
> *** 4,9 ****
> --- 4,11 ----
> * Sample to demonstrate C functions which return setof scalar
> * and setof composite.
> * Joe Conway <mail(at)joeconway(dot)com>
> + * And contributors:
> + * Nabil Sayegh <postgresql(at)e-trolley(dot)de>
> *
> * Copyright 2002 by PostgreSQL Global Development Group
> *
> ***************
> *** 60,68 ****
> - requires anonymous composite type syntax in the FROM clause. See
> the instructions in the documentation below.
>
> ! connectby(text relname, text keyid_fld, text parent_keyid_fld,
> ! text start_with, int max_depth [, text branch_delim])
> - returns keyid, parent_keyid, level, and an optional branch string
> - requires anonymous composite type syntax in the FROM clause. See
> the instructions in the documentation below.
>
> --- 62,72 ----
> - requires anonymous composite type syntax in the FROM clause. See
> the instructions in the documentation below.
>
> ! connectby(text relname, text keyid_fld, text parent_keyid_fld
> ! [, text orderby_fld], text start_with, int max_depth
> ! [, text branch_delim])
> - returns keyid, parent_keyid, level, and an optional branch string
> + and an optional serial column for ordering siblings
> - requires anonymous composite type syntax in the FROM clause. See
> the instructions in the documentation below.
>
> ***************
> *** 452,464 ****
> ==================================================================
> Name
>
> ! connectby(text, text, text, text, int[, text]) - returns a set
> representing a hierarchy (tree structure)
>
> Synopsis
>
> ! connectby(text relname, text keyid_fld, text parent_keyid_fld,
> ! text start_with, int max_depth [, text branch_delim])
>
> Inputs
>
> --- 456,469 ----
> ==================================================================
> Name
>
> ! connectby(text, text, text[, text], text, text, int[, text]) - returns a set
> representing a hierarchy (tree structure)
>
> Synopsis
>
> ! connectby(text relname, text keyid_fld, text parent_keyid_fld
> ! [, text orderby_fld], text start_with, int max_depth
> ! [, text branch_delim])
>
> Inputs
>
> ***************
> *** 474,479 ****
> --- 479,489 ----
>
> Name of the key_parent field
>
> + orderby_fld
> +
> + If optional ordering of siblings is desired:
> + Name of the field to order siblings
> +
> start_with
>
> root value of the tree input as a text value regardless of keyid_fld type
> ***************
> *** 500,505 ****
> --- 510,525 ----
>
> SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'row2', 0)
> AS t(keyid text, parent_keyid text, level int);
> +
> + - or -
> +
> + SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'pos', 'row2', 0, '~')
> + AS t(keyid text, parent_keyid text, level int, branch text, pos int);
> +
> + - or -
> +
> + SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'pos', 'row2', 0)
> + AS t(keyid text, parent_keyid text, level int, pos int);
>
> Notes
>
> ***************
> *** 520,541 ****
> 5. The parameters representing table and field names must include double
> quotes if the names are mixed-case or contain special characters.
>
>
> Example usage
>
> ! CREATE TABLE connectby_tree(keyid text, parent_keyid text);
>
> ! INSERT INTO connectby_tree VALUES('row1',NULL);
> ! INSERT INTO connectby_tree VALUES('row2','row1');
> ! INSERT INTO connectby_tree VALUES('row3','row1');
> ! INSERT INTO connectby_tree VALUES('row4','row2');
> ! INSERT INTO connectby_tree VALUES('row5','row2');
> ! INSERT INTO connectby_tree VALUES('row6','row4');
> ! INSERT INTO connectby_tree VALUES('row7','row3');
> ! INSERT INTO connectby_tree VALUES('row8','row6');
> ! INSERT INTO connectby_tree VALUES('row9','row5');
>
> ! -- with branch
> SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'row2', 0, '~')
> AS t(keyid text, parent_keyid text, level int, branch text);
> keyid | parent_keyid | level | branch
> --- 540,564 ----
> 5. The parameters representing table and field names must include double
> quotes if the names are mixed-case or contain special characters.
>
> + 6. If sorting of siblings is desired, the orderby_fld input parameter *and*
> + a name for the resulting serial field (type INT32) in the query column
> + definition must be given.
>
> Example usage
>
> ! CREATE TABLE connectby_tree(keyid text, parent_keyid text, pos int);
>
> ! INSERT INTO connectby_tree VALUES('row1',NULL, 0);
> ! INSERT INTO connectby_tree VALUES('row2','row1', 0);
> ! INSERT INTO connectby_tree VALUES('row3','row1', 0);
> ! INSERT INTO connectby_tree VALUES('row4','row2', 1);
> ! INSERT INTO connectby_tree VALUES('row5','row2', 0);
> ! INSERT INTO connectby_tree VALUES('row6','row4', 0);
> ! INSERT INTO connectby_tree VALUES('row7','row3', 0);
> ! INSERT INTO connectby_tree VALUES('row8','row6', 0);
> ! INSERT INTO connectby_tree VALUES('row9','row5', 0);
>
> ! -- with branch, without orderby_fld
> SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'row2', 0, '~')
> AS t(keyid text, parent_keyid text, level int, branch text);
> keyid | parent_keyid | level | branch
> ***************
> *** 548,554 ****
> row9 | row5 | 2 | row2~row5~row9
> (6 rows)
>
> ! -- without branch
> SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'row2', 0)
> AS t(keyid text, parent_keyid text, level int);
> keyid | parent_keyid | level
> --- 571,577 ----
> row9 | row5 | 2 | row2~row5~row9
> (6 rows)
>
> ! -- without branch, without orderby_fld
> SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'row2', 0)
> AS t(keyid text, parent_keyid text, level int);
> keyid | parent_keyid | level
> ***************
> *** 559,564 ****
> --- 582,613 ----
> row8 | row6 | 3
> row5 | row2 | 1
> row9 | row5 | 2
> + (6 rows)
> +
> + -- with branch, with orderby_fld (notice that row5 comes before row4)
> + SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'pos', 'row2', 0, '~')
> + AS t(keyid text, parent_keyid text, level int, branch text, pos int) ORDER BY t.pos;
> + keyid | parent_keyid | level | branch | pos
> + -------+--------------+-------+---------------------+-----
> + row2 | | 0 | row2 | 1
> + row5 | row2 | 1 | row2~row5 | 2
> + row9 | row5 | 2 | row2~row5~row9 | 3
> + row4 | row2 | 1 | row2~row4 | 4
> + row6 | row4 | 2 | row2~row4~row6 | 5
> + row8 | row6 | 3 | row2~row4~row6~row8 | 6
> + (6 rows)
> +
> + -- without branch, with orderby_fld (notice that row5 comes before row4)
> + SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'pos', 'row2', 0)
> + AS t(keyid text, parent_keyid text, level int, pos int) ORDER BY t.pos;
> + keyid | parent_keyid | level | pos
> + -------+--------------+-------+-----
> + row2 | | 0 | 1
> + row5 | row2 | 1 | 2
> + row9 | row5 | 2 | 3
> + row4 | row2 | 1 | 4
> + row6 | row4 | 2 | 5
> + row8 | row6 | 3 | 6
> (6 rows)
>
> ==================================================================
> Index: contrib/tablefunc/tablefunc.c
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/contrib/tablefunc/tablefunc.c,v
> retrieving revision 1.19
> diff -c -r1.19 tablefunc.c
> *** contrib/tablefunc/tablefunc.c 25 Jun 2003 18:13:50 -0000 1.19
> --- contrib/tablefunc/tablefunc.c 26 Jun 2003 16:44:27 -0000
> ***************
> *** 4,9 ****
> --- 4,11 ----
> * Sample to demonstrate C functions which return setof scalar
> * and setof composite.
> * Joe Conway <mail(at)joeconway(dot)com>
> + * And contributors:
> + * Nabil Sayegh <postgresql(at)e-trolley(dot)de>
> *
> * Copyright 2002 by PostgreSQL Global Development Group
> *
> ***************
> *** 45,51 ****
> int num_categories,
> TupleDesc tupdesc,
> MemoryContext per_query_ctx);
> ! static void validateConnectbyTupleDesc(TupleDesc tupdesc, bool show_branch);
> static bool compatCrosstabTupleDescs(TupleDesc tupdesc1, TupleDesc tupdesc2);
> static bool compatConnectbyTupleDescs(TupleDesc tupdesc1, TupleDesc tupdesc2);
> static void get_normal_pair(float8 *x1, float8 *x2);
> --- 47,53 ----
> int num_categories,
> TupleDesc tupdesc,
> MemoryContext per_query_ctx);
> ! static void validateConnectbyTupleDesc(TupleDesc tupdesc, bool show_branch, bool show_serial);
> static bool compatCrosstabTupleDescs(TupleDesc tupdesc1, TupleDesc tupdesc2);
> static bool compatConnectbyTupleDescs(TupleDesc tupdesc1, TupleDesc tupdesc2);
> static void get_normal_pair(float8 *x1, float8 *x2);
> ***************
> *** 54,74 ****
> --- 56,81 ----
> static Tuplestorestate *connectby(char *relname,
> char *key_fld,
> char *parent_key_fld,
> + char *orderby_fld,
> char *branch_delim,
> char *start_with,
> int max_depth,
> bool show_branch,
> + bool show_serial,
> MemoryContext per_query_ctx,
> AttInMetadata *attinmeta);
> static Tuplestorestate *build_tuplestore_recursively(char *key_fld,
> char *parent_key_fld,
> char *relname,
> + char *orderby_fld,
> char *branch_delim,
> char *start_with,
> char *branch,
> int level,
> + int *serial,
> int max_depth,
> bool show_branch,
> + bool show_serial,
> MemoryContext per_query_ctx,
> AttInMetadata *attinmeta,
> Tuplestorestate *tupstore);
> ***************
> *** 998,1028 ****
> *
> * e.g. given table foo:
> *
> ! * keyid parent_keyid
> ! * ------+--------------
> ! * row1 NULL
> ! * row2 row1
> ! * row3 row1
> ! * row4 row2
> ! * row5 row2
> ! * row6 row4
> ! * row7 row3
> ! * row8 row6
> ! * row9 row5
> ! *
> ! *
> ! * connectby(text relname, text keyid_fld, text parent_keyid_fld,
> ! * text start_with, int max_depth [, text branch_delim])
> ! * connectby('foo', 'keyid', 'parent_keyid', 'row2', 0, '~') returns:
> *
> ! * keyid parent_id level branch
> * ------+-----------+--------+-----------------------
> ! * row2 NULL 0 row2
> ! * row4 row2 1 row2~row4
> ! * row6 row4 2 row2~row4~row6
> ! * row8 row6 3 row2~row4~row6~row8
> ! * row5 row2 1 row2~row5
> ! * row9 row5 2 row2~row5~row9
> *
> */
> PG_FUNCTION_INFO_V1(connectby_text);
> --- 1005,1036 ----
> *
> * e.g. given table foo:
> *
> ! * keyid parent_keyid pos
> ! * ------+------------+--
> ! * row1 NULL 0
> ! * row2 row1 0
> ! * row3 row1 0
> ! * row4 row2 1
> ! * row5 row2 0
> ! * row6 row4 0
> ! * row7 row3 0
> ! * row8 row6 0
> ! * row9 row5 0
> ! *
> ! *
> ! * connectby(text relname, text keyid_fld, text parent_keyid_fld
> ! * [, text orderby_fld], text start_with, int max_depth
> ! * [, text branch_delim])
> ! * connectby('foo', 'keyid', 'parent_keyid', 'pos', 'row2', 0, '~') returns:
> *
> ! * keyid parent_id level branch serial
> * ------+-----------+--------+-----------------------
> ! * row2 NULL 0 row2 1
> ! * row5 row2 1 row2~row5 2
> ! * row9 row5 2 row2~row5~row9 3
> ! * row4 row2 1 row2~row4 4
> ! * row6 row4 2 row2~row4~row6 5
> ! * row8 row6 3 row2~row4~row6~row8 6
> *
> */
> PG_FUNCTION_INFO_V1(connectby_text);
> ***************
> *** 1040,1045 ****
> --- 1048,1054 ----
> int max_depth = PG_GETARG_INT32(4);
> char *branch_delim = NULL;
> bool show_branch = false;
> + bool show_serial = false;
> ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
> TupleDesc tupdesc;
> AttInMetadata *attinmeta;
> ***************
> *** 1067,1073 ****
> tupdesc = CreateTupleDescCopy(rsinfo->expectedDesc);
>
> /* does it meet our needs */
> ! validateConnectbyTupleDesc(tupdesc, show_branch);
>
> /* OK, use it then */
> attinmeta = TupleDescGetAttInMetadata(tupdesc);
> --- 1076,1082 ----
> tupdesc = CreateTupleDescCopy(rsinfo->expectedDesc);
>
> /* does it meet our needs */
> ! validateConnectbyTupleDesc(tupdesc, show_branch, show_serial);
>
> /* OK, use it then */
> attinmeta = TupleDescGetAttInMetadata(tupdesc);
> ***************
> *** 1082,1091 ****
> --- 1091,1102 ----
> rsinfo->setResult = connectby(relname,
> key_fld,
> parent_key_fld,
> + NULL,
> branch_delim,
> start_with,
> max_depth,
> show_branch,
> + show_serial,
> per_query_ctx,
> attinmeta);
> rsinfo->setDesc = tupdesc;
> ***************
> *** 1102,1107 ****
> --- 1113,1197 ----
> return (Datum) 0;
> }
>
> + PG_FUNCTION_INFO_V1(connectby_text_serial);
> + Datum
> + connectby_text_serial(PG_FUNCTION_ARGS)
> + {
> + char *relname = GET_STR(PG_GETARG_TEXT_P(0));
> + char *key_fld = GET_STR(PG_GETARG_TEXT_P(1));
> + char *parent_key_fld = GET_STR(PG_GETARG_TEXT_P(2));
> + char *orderby_fld = GET_STR(PG_GETARG_TEXT_P(3));
> + char *start_with = GET_STR(PG_GETARG_TEXT_P(4));
> + int max_depth = PG_GETARG_INT32(5);
> + char *branch_delim = NULL;
> + bool show_branch = false;
> + bool show_serial = true;
> +
> + ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
> + TupleDesc tupdesc;
> + AttInMetadata *attinmeta;
> + MemoryContext per_query_ctx;
> + MemoryContext oldcontext;
> +
> + /* check to see if caller supports us returning a tuplestore */
> + if (!rsinfo || !(rsinfo->allowedModes & SFRM_Materialize))
> + elog(ERROR, "connectby: materialize mode required, but it is not "
> + "allowed in this context");
> +
> + if (fcinfo->nargs == 7)
> + {
> + branch_delim = GET_STR(PG_GETARG_TEXT_P(6));
> + show_branch = true;
> + }
> + else
> + /* default is no show, tilde for the delimiter */
> + branch_delim = pstrdup("~");
> +
> + per_query_ctx = rsinfo->econtext->ecxt_per_query_memory;
> + oldcontext = MemoryContextSwitchTo(per_query_ctx);
> +
> + /* get the requested return tuple description */
> + tupdesc = CreateTupleDescCopy(rsinfo->expectedDesc);
> +
> + /* does it meet our needs */
> + validateConnectbyTupleDesc(tupdesc, show_branch, show_serial);
> +
> + /* OK, use it then */
> + attinmeta = TupleDescGetAttInMetadata(tupdesc);
> +
> + /* check to see if caller supports us returning a tuplestore */
> + if (!rsinfo->allowedModes & SFRM_Materialize)
> + elog(ERROR, "connectby requires Materialize mode, but it is not "
> + "allowed in this context");
> +
> + /* OK, go to work */
> + rsinfo->returnMode = SFRM_Materialize;
> + rsinfo->setResult = connectby(relname,
> + key_fld,
> + parent_key_fld,
> + orderby_fld,
> + branch_delim,
> + start_with,
> + max_depth,
> + show_branch,
> + show_serial,
> + per_query_ctx,
> + attinmeta);
> + rsinfo->setDesc = tupdesc;
> +
> + MemoryContextSwitchTo(oldcontext);
> +
> + /*
> + * SFRM_Materialize mode expects us to return a NULL Datum. The actual
> + * tuples are in our tuplestore and passed back through
> + * rsinfo->setResult. rsinfo->setDesc is set to the tuple description
> + * that we actually used to build our tuples with, so the caller can
> + * verify we did what it was expecting.
> + */
> + return (Datum) 0;
> + }
> +
> +
> /*
> * connectby - does the real work for connectby_text()
> */
> ***************
> *** 1109,1118 ****
> --- 1199,1210 ----
> connectby(char *relname,
> char *key_fld,
> char *parent_key_fld,
> + char *orderby_fld,
> char *branch_delim,
> char *start_with,
> int max_depth,
> bool show_branch,
> + bool show_serial,
> MemoryContext per_query_ctx,
> AttInMetadata *attinmeta)
> {
> ***************
> *** 1120,1125 ****
> --- 1212,1219 ----
> int ret;
> MemoryContext oldcontext;
>
> + int serial = 1;
> +
> /* Connect to SPI manager */
> if ((ret = SPI_connect()) < 0)
> elog(ERROR, "connectby: SPI_connect returned %d", ret);
> ***************
> *** 1136,1147 ****
> --- 1230,1244 ----
> tupstore = build_tuplestore_recursively(key_fld,
> parent_key_fld,
> relname,
> + orderby_fld,
> branch_delim,
> start_with,
> start_with, /* current_branch */
> 0, /* initial level is 0 */
> + &serial, /* initial serial is 1 */
> max_depth,
> show_branch,
> + show_serial,
> per_query_ctx,
> attinmeta,
> tupstore);
> ***************
> *** 1155,1166 ****
> --- 1252,1266 ----
> build_tuplestore_recursively(char *key_fld,
> char *parent_key_fld,
> char *relname,
> + char *orderby_fld,
> char *branch_delim,
> char *start_with,
> char *branch,
> int level,
> + int *serial,
> int max_depth,
> bool show_branch,
> + bool show_serial,
> MemoryContext per_query_ctx,
> AttInMetadata *attinmeta,
> Tuplestorestate *tupstore)
> ***************
> *** 1170,1187 ****
> StringInfo sql = makeStringInfo();
> int ret;
> int proc;
>
> if (max_depth > 0 && level > max_depth)
> return tupstore;
>
> /* Build initial sql statement */
> ! appendStringInfo(sql, "SELECT %s, %s FROM %s WHERE %s = '%s' AND %s IS NOT NULL",
> key_fld,
> parent_key_fld,
> relname,
> parent_key_fld,
> start_with,
> key_fld);
>
> /* Retrieve the desired rows */
> ret = SPI_exec(sql->data, 0);
> --- 1270,1304 ----
> StringInfo sql = makeStringInfo();
> int ret;
> int proc;
> + int serial_column;
>
> if (max_depth > 0 && level > max_depth)
> return tupstore;
>
> /* Build initial sql statement */
> ! if (!show_serial)
> ! {
> ! appendStringInfo(sql, "SELECT %s, %s FROM %s WHERE %s = '%s' AND %s IS NOT NULL",
> key_fld,
> parent_key_fld,
> relname,
> parent_key_fld,
> start_with,
> key_fld);
> + serial_column=0;
> + }
> + else
> + {
> + appendStringInfo(sql, "SELECT %s, %s FROM %s WHERE %s = '%s' AND %s IS NOT NULL ORDER BY %s",
> + key_fld,
> + parent_key_fld,
> + relname,
> + parent_key_fld,
> + start_with,
> + key_fld,
> + orderby_fld);
> + serial_column=1;
> + }
>
> /* Retrieve the desired rows */
> ret = SPI_exec(sql->data, 0);
> ***************
> *** 1198,1203 ****
> --- 1315,1321 ----
> char *current_key;
> char *current_key_parent;
> char current_level[INT32_STRLEN];
> + char serial_str[INT32_STRLEN];
> char *current_branch;
> char **values;
> StringInfo branchstr = NULL;
> ***************
> *** 1212,1220 ****
> chk_current_key = makeStringInfo();
>
> if (show_branch)
> ! values = (char **) palloc(CONNECTBY_NCOLS * sizeof(char *));
> else
> ! values = (char **) palloc(CONNECTBY_NCOLS_NOBRANCH * sizeof(char *));
>
> /* First time through, do a little setup */
> if (level == 0)
> --- 1330,1338 ----
> chk_current_key = makeStringInfo();
>
> if (show_branch)
> ! values = (char **) palloc((CONNECTBY_NCOLS + serial_column) * sizeof(char *));
> else
> ! values = (char **) palloc((CONNECTBY_NCOLS_NOBRANCH + serial_column) * sizeof(char *));
>
> /* First time through, do a little setup */
> if (level == 0)
> ***************
> *** 1243,1248 ****
> --- 1361,1376 ----
> if (show_branch)
> values[3] = start_with;
>
> + /* root starts the serial with 1 */
> + if (show_serial)
> + {
> + sprintf(serial_str, "%d", (*serial)++);
> + if (show_branch)
> + values[4] = serial_str;
> + else
> + values[3] = serial_str;
> + }
> +
> /* construct the tuple */
> tuple = BuildTupleFromCStrings(attinmeta, values);
>
> ***************
> *** 1290,1295 ****
> --- 1418,1431 ----
> values[2] = current_level;
> if (show_branch)
> values[3] = current_branch;
> + if (show_serial)
> + {
> + sprintf(serial_str, "%d", (*serial)++);
> + if (show_branch)
> + values[4] = serial_str;
> + else
> + values[3] = serial_str;
> + }
>
> tuple = BuildTupleFromCStrings(attinmeta, values);
>
> ***************
> *** 1311,1322 ****
> --- 1447,1461 ----
> tupstore = build_tuplestore_recursively(key_fld,
> parent_key_fld,
> relname,
> + orderby_fld,
> branch_delim,
> values[0],
> current_branch,
> level + 1,
> + serial,
> max_depth,
> show_branch,
> + show_serial,
> per_query_ctx,
> attinmeta,
> tupstore);
> ***************
> *** 1340,1357 ****
> * Check expected (query runtime) tupdesc suitable for Connectby
> */
> static void
> ! validateConnectbyTupleDesc(TupleDesc tupdesc, bool show_branch)
> {
> /* are there the correct number of columns */
> if (show_branch)
> {
> ! if (tupdesc->natts != CONNECTBY_NCOLS)
> elog(ERROR, "Query-specified return tuple not valid for Connectby: "
> "wrong number of columns");
> }
> else
> {
> ! if (tupdesc->natts != CONNECTBY_NCOLS_NOBRANCH)
> elog(ERROR, "Query-specified return tuple not valid for Connectby: "
> "wrong number of columns");
> }
> --- 1479,1501 ----
> * Check expected (query runtime) tupdesc suitable for Connectby
> */
> static void
> ! validateConnectbyTupleDesc(TupleDesc tupdesc, bool show_branch, bool show_serial)
> {
> + int serial_column=0;
> +
> + if (show_serial)
> + serial_column=1;
> +
> /* are there the correct number of columns */
> if (show_branch)
> {
> ! if (tupdesc->natts != (CONNECTBY_NCOLS + serial_column))
> elog(ERROR, "Query-specified return tuple not valid for Connectby: "
> "wrong number of columns");
> }
> else
> {
> ! if (tupdesc->natts != CONNECTBY_NCOLS_NOBRANCH + serial_column)
> elog(ERROR, "Query-specified return tuple not valid for Connectby: "
> "wrong number of columns");
> }
> ***************
> *** 1371,1376 ****
> --- 1515,1530 ----
> elog(ERROR, "Query-specified return tuple not valid for Connectby: "
> "fourth column must be type %s", format_type_be(TEXTOID));
>
> + /* check that the type of the fifth column is INT4 */
> + if (show_branch && show_serial && tupdesc->attrs[4]->atttypid != INT4OID)
> + elog(ERROR, "Query-specified return tuple not valid for Connectby: "
> + "fifth column must be type %s", format_type_be(INT4OID));
> +
> + /* check that the type of the fifth column is INT4 */
> + if (!show_branch && show_serial && tupdesc->attrs[3]->atttypid != INT4OID)
> + elog(ERROR, "Query-specified return tuple not valid for Connectby: "
> + "fourth column must be type %s", format_type_be(INT4OID));
> +
> /* OK, the tupdesc is valid for our purposes */
> }
>
> Index: contrib/tablefunc/tablefunc.h
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/contrib/tablefunc/tablefunc.h,v
> retrieving revision 1.5
> diff -c -r1.5 tablefunc.h
> *** contrib/tablefunc/tablefunc.h 20 Mar 2003 06:46:30 -0000 1.5
> --- contrib/tablefunc/tablefunc.h 26 Jun 2003 16:44:34 -0000
> ***************
> *** 4,9 ****
> --- 4,11 ----
> * Sample to demonstrate C functions which return setof scalar
> * and setof composite.
> * Joe Conway <mail(at)joeconway(dot)com>
> + * And contributors:
> + * Nabil Sayegh <postgresql(at)e-trolley(dot)de>
> *
> * Copyright 2002 by PostgreSQL Global Development Group
> *
> ***************
> *** 36,40 ****
> --- 38,43 ----
> extern Datum crosstab(PG_FUNCTION_ARGS);
> extern Datum crosstab_hash(PG_FUNCTION_ARGS);
> extern Datum connectby_text(PG_FUNCTION_ARGS);
> + extern Datum connectby_text_serial(PG_FUNCTION_ARGS);
>
> #endif /* TABLEFUNC_H */
> Index: contrib/tablefunc/tablefunc.sql.in
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/contrib/tablefunc/tablefunc.sql.in,v
> retrieving revision 1.7
> diff -c -r1.7 tablefunc.sql.in
> *** contrib/tablefunc/tablefunc.sql.in 14 May 2003 03:25:57 -0000 1.7
> --- contrib/tablefunc/tablefunc.sql.in 26 Jun 2003 16:19:29 -0000
> ***************
> *** 64,66 ****
> --- 64,78 ----
> RETURNS setof record
> AS 'MODULE_PATHNAME','connectby_text'
> LANGUAGE 'C' STABLE STRICT;
> +
> + -- These 2 take the name of a field to ORDER BY as 4th arg (for sorting siblings)
> +
> + CREATE OR REPLACE FUNCTION connectby(text,text,text,text,text,int,text)
> + RETURNS setof record
> + AS 'MODULE_PATHNAME','connectby_text_serial'
> + LANGUAGE 'C' STABLE STRICT;
> +
> + CREATE OR REPLACE FUNCTION connectby(text,text,text,text,text,int)
> + RETURNS setof record
> + AS 'MODULE_PATHNAME','connectby_text_serial'
> + LANGUAGE 'C' STABLE STRICT;
> Index: contrib/tablefunc/data/connectby_text.data
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/contrib/tablefunc/data/connectby_text.data,v
> retrieving revision 1.1
> diff -c -r1.1 connectby_text.data
> *** contrib/tablefunc/data/connectby_text.data 12 Sep 2002 00:14:40 -0000 1.1
> --- contrib/tablefunc/data/connectby_text.data 26 Jun 2003 16:31:47 -0000
> ***************
> *** 1,9 ****
> ! row1 \N
> ! row2 row1
> ! row3 row1
> ! row4 row2
> ! row5 row2
> ! row6 row4
> ! row7 row3
> ! row8 row6
> ! row9 row5
> --- 1,9 ----
> ! row1 \N 0
> ! row2 row1 0
> ! row3 row1 0
> ! row4 row2 1
> ! row5 row2 0
> ! row6 row4 0
> ! row7 row3 0
> ! row8 row6 0
> ! row9 row5 0
> Index: contrib/tablefunc/expected/tablefunc.out
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/contrib/tablefunc/expected/tablefunc.out,v
> retrieving revision 1.6
> diff -c -r1.6 tablefunc.out
> *** contrib/tablefunc/expected/tablefunc.out 20 Mar 2003 06:46:30 -0000 1.6
> --- contrib/tablefunc/expected/tablefunc.out 26 Jun 2003 16:37:27 -0000
> ***************
> *** 197,205 ****
> -- connectby
> --
> -- test connectby with text based hierarchy
> ! CREATE TABLE connectby_text(keyid text, parent_keyid text);
> \copy connectby_text from 'data/connectby_text.data'
> ! -- with branch
> SELECT * FROM connectby('connectby_text', 'keyid', 'parent_keyid', 'row2', 0, '~') AS t(keyid text, parent_keyid text, level int, branch text);
> keyid | parent_keyid | level | branch
> -------+--------------+-------+---------------------
> --- 197,205 ----
> -- connectby
> --
> -- test connectby with text based hierarchy
> ! CREATE TABLE connectby_text(keyid text, parent_keyid text, pos int);
> \copy connectby_text from 'data/connectby_text.data'
> ! -- with branch, without orderby
> SELECT * FROM connectby('connectby_text', 'keyid', 'parent_keyid', 'row2', 0, '~') AS t(keyid text, parent_keyid text, level int, branch text);
> keyid | parent_keyid | level | branch
> -------+--------------+-------+---------------------
> ***************
> *** 211,217 ****
> row9 | row5 | 2 | row2~row5~row9
> (6 rows)
>
> ! -- without branch
> SELECT * FROM connectby('connectby_text', 'keyid', 'parent_keyid', 'row2', 0) AS t(keyid text, parent_keyid text, level int);
> keyid | parent_keyid | level
> -------+--------------+-------
> --- 211,217 ----
> row9 | row5 | 2 | row2~row5~row9
> (6 rows)
>
> ! -- without branch, without orderby
> SELECT * FROM connectby('connectby_text', 'keyid', 'parent_keyid', 'row2', 0) AS t(keyid text, parent_keyid text, level int);
> keyid | parent_keyid | level
> -------+--------------+-------
> ***************
> *** 221,226 ****
> --- 221,250 ----
> row8 | row6 | 3
> row5 | row2 | 1
> row9 | row5 | 2
> + (6 rows)
> +
> + -- with branch, with orderby
> + SELECT * FROM connectby('connectby_text', 'keyid', 'parent_keyid', 'pos', 'row2', 0, '~') AS t(keyid text, parent_keyid text, level int, branch text, pos int) ORDER BY t.pos;
> + keyid | parent_keyid | level | branch | pos
> + -------+--------------+-------+---------------------+-----
> + row2 | | 0 | row2 | 1
> + row5 | row2 | 1 | row2~row5 | 2
> + row9 | row5 | 2 | row2~row5~row9 | 3
> + row4 | row2 | 1 | row2~row4 | 4
> + row6 | row4 | 2 | row2~row4~row6 | 5
> + row8 | row6 | 3 | row2~row4~row6~row8 | 6
> + (6 rows)
> +
> + -- without branch, with orderby
> + SELECT * FROM connectby('connectby_text', 'keyid', 'parent_keyid', 'pos', 'row2', 0) AS t(keyid text, parent_keyid text, level int, pos int) ORDER BY t.pos;
> + keyid | parent_keyid | level | pos
> + -------+--------------+-------+-----
> + row2 | | 0 | 1
> + row5 | row2 | 1 | 2
> + row9 | row5 | 2 | 3
> + row4 | row2 | 1 | 4
> + row6 | row4 | 2 | 5
> + row8 | row6 | 3 | 6
> (6 rows)
>
> -- test connectby with int based hierarchy
> Index: contrib/tablefunc/sql/tablefunc.sql
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/contrib/tablefunc/sql/tablefunc.sql,v
> retrieving revision 1.7
> diff -c -r1.7 tablefunc.sql
> *** contrib/tablefunc/sql/tablefunc.sql 20 Mar 2003 06:46:30 -0000 1.7
> --- contrib/tablefunc/sql/tablefunc.sql 26 Jun 2003 16:37:23 -0000
> ***************
> *** 94,107 ****
> --
>
> -- test connectby with text based hierarchy
> ! CREATE TABLE connectby_text(keyid text, parent_keyid text);
> \copy connectby_text from 'data/connectby_text.data'
>
> ! -- with branch
> SELECT * FROM connectby('connectby_text', 'keyid', 'parent_keyid', 'row2', 0, '~') AS t(keyid text, parent_keyid text, level int, branch text);
>
> ! -- without branch
> SELECT * FROM connectby('connectby_text', 'keyid', 'parent_keyid', 'row2', 0) AS t(keyid text, parent_keyid text, level int);
>
> -- test connectby with int based hierarchy
> CREATE TABLE connectby_int(keyid int, parent_keyid int);
> --- 94,113 ----
> --
>
> -- test connectby with text based hierarchy
> ! CREATE TABLE connectby_text(keyid text, parent_keyid text, pos int);
> \copy connectby_text from 'data/connectby_text.data'
>
> ! -- with branch, without orderby
> SELECT * FROM connectby('connectby_text', 'keyid', 'parent_keyid', 'row2', 0, '~') AS t(keyid text, parent_keyid text, level int, branch text);
>
> ! -- without branch, without orderby
> SELECT * FROM connectby('connectby_text', 'keyid', 'parent_keyid', 'row2', 0) AS t(keyid text, parent_keyid text, level int);
> +
> + -- with branch, with orderby
> + SELECT * FROM connectby('connectby_text', 'keyid', 'parent_keyid', 'pos', 'row2', 0, '~') AS t(keyid text, parent_keyid text, level int, branch text, pos int) ORDER BY t.pos;
> +
> + -- without branch, with orderby
> + SELECT * FROM connectby('connectby_text', 'keyid', 'parent_keyid', 'pos', 'row2', 0) AS t(keyid text, parent_keyid text, level int, pos int) ORDER BY t.pos;
>
> -- test connectby with int based hierarchy
> CREATE TABLE connectby_int(keyid int, parent_keyid int);
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073