Re: contrib/tablefunc update - Mailing list pgsql-patches
From | Bruce Momjian |
---|---|
Subject | Re: contrib/tablefunc update |
Date | |
Msg-id | 200209020543.g825hrt14156@candle.pha.pa.us Whole thread Raw |
In response to | contrib/tablefunc update (Joe Conway <mail@joeconway.com>) |
List | pgsql-patches |
Patch applied. Thanks. --------------------------------------------------------------------------- Joe Conway wrote: > Attached is an update to contrib/tablefunc. It introduces a new > function, connectby(), which can serve as a reference implementation for > the changes made in the last few days -- namely the ability of a > function to return an entire tuplestore, and the ability of a function > to make use of the query provided "expected" tuple description. > > Description: > > 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. > > 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 > -------+--------------+-------+--------------------- > row2 | | 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 > (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 > -------+--------------+------- > row2 | | 0 > row4 | row2 | 1 > row6 | row4 | 2 > row8 | row6 | 3 > row5 | row2 | 1 > row9 | row5 | 2 > (6 rows) > > SELECT * FROM > connectby('connectby_tree', 'keyid', 'parent_keyid', 'row2', 1, '~') > AS t(keyid text, parent_keyid text, level int, branch text); > keyid | parent_keyid | level | branch > -------+--------------+-------+----------- > row2 | | 0 | row2 > row4 | row2 | 1 | row2~row4 > row5 | row2 | 1 | row2~row5 > (3 rows) > > Notes: > 1. keyid and parent_keyid must be the same data type > 2. The column definition *must* include a third column of type INT4 > for the level value output > 3. If the branch field is not desired, omit both the branch_delim > input parameter *and* the branch field in the query column > definition > 4. If the branch field is desired, it must be the forth column in the > query column definition, and it must be type TEXT > > Seems to work pretty well. I have a "bill of material" (BOM) table with > about 220000 rows of part relationship data for assemblies (this is old, > but real, data from where I work). Starting with one top level assembly > (i.e. a system that we ship) the function builds a full BOM "explosion" > with about 3500 parts in 1.1 seconds. YMMV. > > If there are no objections, please commit. > > Thanks, > > Joe > > > Index: contrib/tablefunc/README.tablefunc > =================================================================== > RCS file: /opt/src/cvs/pgsql-server/contrib/tablefunc/README.tablefunc,v > retrieving revision 1.2 > diff -c -r1.2 README.tablefunc > *** contrib/tablefunc/README.tablefunc 15 Aug 2002 02:51:26 -0000 1.2 > --- contrib/tablefunc/README.tablefunc 31 Aug 2002 19:51:24 -0000 > *************** > *** 60,65 **** > --- 60,71 ---- > - 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. > + > Documentation > ================================================================== > Name > *************** > *** 323,328 **** > --- 329,437 ---- > test1 | val2 | val3 | > test2 | val6 | val7 | > (2 rows) > + > + ================================================================== > + 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 > + > + relname > + > + Name of the source relation > + > + keyid_fld > + > + Name of the key field > + > + parent_keyid_fld > + > + Name of the key_parent field > + > + start_with > + > + root value of the tree input as a text value regardless of keyid_fld type > + > + max_depth > + > + zero (0) for unlimited depth, otherwise restrict level to this depth > + > + branch_delim > + > + if optional branch value is desired, this string is used as the delimiter > + > + Outputs > + > + Returns setof record, which must defined with a column definition > + in the FROM clause of the SELECT statement, e.g.: > + > + SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'row2', 0, '~') > + AS t(keyid text, parent_keyid text, level int, branch text); > + > + - or - > + > + SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'row2', 0) > + AS t(keyid text, parent_keyid text, level int); > + > + Notes > + > + 1. keyid and parent_keyid must be the same data type > + > + 2. The column definition *must* include a third column of type INT4 for > + the level value output > + > + 3. If the branch field is not desired, omit both the branch_delim input > + parameter *and* the branch field in the query column definition > + > + 4. If the branch field is desired, it must be the forth column in the query > + column definition, and it must be type TEXT > + > + 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 > + -------+--------------+-------+--------------------- > + row2 | | 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 > + (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 > + -------+--------------+------- > + row2 | | 0 > + row4 | row2 | 1 > + row6 | row4 | 2 > + row8 | row6 | 3 > + row5 | row2 | 1 > + row9 | row5 | 2 > + (6 rows) > > ================================================================== > -- Joe Conway > Index: contrib/tablefunc/tablefunc-test.sql > =================================================================== > RCS file: /opt/src/cvs/pgsql-server/contrib/tablefunc/tablefunc-test.sql,v > retrieving revision 1.2 > diff -c -r1.2 tablefunc-test.sql > *** contrib/tablefunc/tablefunc-test.sql 15 Aug 2002 02:51:26 -0000 1.2 > --- contrib/tablefunc/tablefunc-test.sql 31 Aug 2002 19:07:51 -0000 > *************** > *** 1,9 **** > -- > - -- show_all_settings() > - -- > - SELECT * FROM show_all_settings(); > - > - -- > -- normal_rand() > -- > SELECT * FROM normal_rand(100, 250, 5, EXTRACT(SECONDS FROM CURRENT_TIME(0))::int); > --- 1,4 ---- > *************** > *** 47,49 **** > --- 42,85 ---- > select * from crosstab('select rowid, attribute, value from ct where rowclass = ''group1'' order by 1,2;', 2) as c(rowidtext, att1 text, att2 text); > select * from crosstab('select rowid, attribute, value from ct where rowclass = ''group1'' order by 1,2;', 3) as c(rowidtext, att1 text, att2 text, att3 text); > select * from crosstab('select rowid, attribute, value from ct where rowclass = ''group1'' order by 1,2;', 4) as c(rowidtext, att1 text, att2 text, att3 text, att4 text); > + > + -- test connectby with text based hierarchy > + DROP TABLE connectby_tree; > + 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); > + > + -- without branch > + SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'row2', 0) AS t(keyid text, parent_keyid text, levelint); > + > + -- test connectby with int based hierarchy > + DROP TABLE connectby_tree; > + CREATE TABLE connectby_tree(keyid int, parent_keyid int); > + > + INSERT INTO connectby_tree VALUES(1,NULL); > + INSERT INTO connectby_tree VALUES(2,1); > + INSERT INTO connectby_tree VALUES(3,1); > + INSERT INTO connectby_tree VALUES(4,2); > + INSERT INTO connectby_tree VALUES(5,2); > + INSERT INTO connectby_tree VALUES(6,4); > + INSERT INTO connectby_tree VALUES(7,3); > + INSERT INTO connectby_tree VALUES(8,6); > + INSERT INTO connectby_tree VALUES(9,5); > + > + -- with branch > + SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', '2', 0, '~') AS t(keyid int, parent_keyid int, levelint, branch text); > + > + -- without branch > + SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', '2', 0) AS t(keyid int, parent_keyid int, level int); > + > Index: contrib/tablefunc/tablefunc.c > =================================================================== > RCS file: /opt/src/cvs/pgsql-server/contrib/tablefunc/tablefunc.c,v > retrieving revision 1.3 > diff -c -r1.3 tablefunc.c > *** contrib/tablefunc/tablefunc.c 29 Aug 2002 17:14:32 -0000 1.3 > --- contrib/tablefunc/tablefunc.c 31 Aug 2002 19:11:31 -0000 > *************** > *** 32,47 **** > > #include "fmgr.h" > #include "funcapi.h" > ! #include "executor/spi.h" > #include "utils/builtins.h" > #include "utils/guc.h" > #include "utils/lsyscache.h" > > #include "tablefunc.h" > > ! static bool compatTupleDescs(TupleDesc tupdesc1, TupleDesc tupdesc2); > static void get_normal_pair(float8 *x1, float8 *x2); > ! static TupleDesc make_crosstab_tupledesc(TupleDesc spi_tupdesc, int num_catagories); > > typedef struct > { > --- 32,73 ---- > > #include "fmgr.h" > #include "funcapi.h" > ! #include "executor/spi.h" > ! #include "miscadmin.h" > #include "utils/builtins.h" > #include "utils/guc.h" > #include "utils/lsyscache.h" > > #include "tablefunc.h" > > ! static void validateConnectbyTupleDesc(TupleDesc tupdesc, bool show_branch); > ! static bool compatCrosstabTupleDescs(TupleDesc tupdesc1, TupleDesc tupdesc2); > ! static bool compatConnectbyTupleDescs(TupleDesc tupdesc1, TupleDesc tupdesc2); > static void get_normal_pair(float8 *x1, float8 *x2); > ! static TupleDesc make_crosstab_tupledesc(TupleDesc spi_tupdesc, > ! int num_catagories); > ! static Tuplestorestate *connectby(char *relname, > ! char *key_fld, > ! char *parent_key_fld, > ! char *branch_delim, > ! char *start_with, > ! int max_depth, > ! bool show_branch, > ! MemoryContext per_query_ctx, > ! AttInMetadata *attinmeta); > ! static Tuplestorestate *build_tuplestore_recursively(char *key_fld, > ! char *parent_key_fld, > ! char *relname, > ! char *branch_delim, > ! char *start_with, > ! char *branch, > ! int level, > ! int max_depth, > ! bool show_branch, > ! MemoryContext per_query_ctx, > ! AttInMetadata *attinmeta, > ! Tuplestorestate *tupstore); > ! static char *quote_ident_cstr(char *rawstr); > > typedef struct > { > *************** > *** 68,73 **** > --- 94,102 ---- > } \ > } while (0) > > + /* sign, 10 digits, '\0' */ > + #define INT32_STRLEN 12 > + > /* > * normal_rand - return requested number of random values > * with a Gaussian (Normal) distribution. > *************** > *** 358,364 **** > * from ret_relname, at least based on number and type of > * attributes > */ > ! if (!compatTupleDescs(tupdesc, spi_tupdesc)) > elog(ERROR, "crosstab: return and sql tuple descriptions are" > " incompatible"); > > --- 387,393 ---- > * from ret_relname, at least based on number and type of > * attributes > */ > ! if (!compatCrosstabTupleDescs(tupdesc, spi_tupdesc)) > elog(ERROR, "crosstab: return and sql tuple descriptions are" > " incompatible"); > > *************** > *** 559,568 **** > } > > /* > * Check if two tupdescs match in type of attributes > */ > static bool > ! compatTupleDescs(TupleDesc ret_tupdesc, TupleDesc sql_tupdesc) > { > int i; > Form_pg_attribute ret_attr; > --- 588,987 ---- > } > > /* > + * connectby_text - produce a result set from a hierarchical (parent/child) > + * table. > + * > + * 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); > + > + #define CONNECTBY_NCOLS 4 > + #define CONNECTBY_NCOLS_NOBRANCH 3 > + > + Datum > + connectby_text(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 *start_with = GET_STR(PG_GETARG_TEXT_P(3)); > + int max_depth = PG_GETARG_INT32(4); > + char *branch_delim = NULL; > + bool show_branch = false; > + ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo; > + TupleDesc tupdesc; > + AttInMetadata *attinmeta; > + MemoryContext per_query_ctx; > + MemoryContext oldcontext; > + > + if (fcinfo->nargs == 6) > + { > + branch_delim = GET_STR(PG_GETARG_TEXT_P(5)); > + show_branch = true; > + } > + > + 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); > + > + /* 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, > + branch_delim, > + start_with, > + max_depth, > + show_branch, > + 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() > + */ > + static Tuplestorestate * > + connectby(char *relname, > + char *key_fld, > + char *parent_key_fld, > + char *branch_delim, > + char *start_with, > + int max_depth, > + bool show_branch, > + MemoryContext per_query_ctx, > + AttInMetadata *attinmeta) > + { > + Tuplestorestate *tupstore = NULL; > + int ret; > + MemoryContext oldcontext; > + > + /* Connect to SPI manager */ > + if ((ret = SPI_connect()) < 0) > + elog(ERROR, "connectby: SPI_connect returned %d", ret); > + > + /* switch to longer term context to create the tuple store */ > + oldcontext = MemoryContextSwitchTo(per_query_ctx); > + > + /* initialize our tuplestore */ > + tupstore = tuplestore_begin_heap(true, SortMem); > + > + MemoryContextSwitchTo(oldcontext); > + > + /* now go get the whole tree */ > + tupstore = build_tuplestore_recursively(key_fld, > + parent_key_fld, > + relname, > + branch_delim, > + start_with, > + start_with, /* current_branch */ > + 0, /* initial level is 0 */ > + max_depth, > + show_branch, > + per_query_ctx, > + attinmeta, > + tupstore); > + > + SPI_finish(); > + > + oldcontext = MemoryContextSwitchTo(per_query_ctx); > + tuplestore_donestoring(tupstore); > + MemoryContextSwitchTo(oldcontext); > + > + return tupstore; > + } > + > + static Tuplestorestate * > + build_tuplestore_recursively(char *key_fld, > + char *parent_key_fld, > + char *relname, > + char *branch_delim, > + char *start_with, > + char *branch, > + int level, > + int max_depth, > + bool show_branch, > + MemoryContext per_query_ctx, > + AttInMetadata *attinmeta, > + Tuplestorestate *tupstore) > + { > + TupleDesc tupdesc = attinmeta->tupdesc; > + MemoryContext oldcontext; > + 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", > + quote_ident_cstr(key_fld), > + quote_ident_cstr(parent_key_fld), > + quote_ident_cstr(relname), > + quote_ident_cstr(parent_key_fld), > + start_with, > + quote_ident_cstr(key_fld)); > + > + /* Retrieve the desired rows */ > + ret = SPI_exec(sql->data, 0); > + proc = SPI_processed; > + > + /* Check for qualifying tuples */ > + if ((ret == SPI_OK_SELECT) && (proc > 0)) > + { > + HeapTuple tuple; > + HeapTuple spi_tuple; > + SPITupleTable *tuptable = SPI_tuptable; > + TupleDesc spi_tupdesc = tuptable->tupdesc; > + int i; > + char *current_key; > + char *current_key_parent; > + char current_level[INT32_STRLEN]; > + char *current_branch; > + char **values; > + > + 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) > + { > + /* > + * Check that return tupdesc is compatible with the one we got > + * from the query, but only at level 0 -- no need to check more > + * than once > + */ > + > + if (!compatConnectbyTupleDescs(tupdesc, spi_tupdesc)) > + elog(ERROR, "connectby: return and sql tuple descriptions are " > + "incompatible"); > + > + /* root value is the one we initially start with */ > + values[0] = start_with; > + > + /* root value has no parent */ > + values[1] = NULL; > + > + /* root level is 0 */ > + sprintf(current_level, "%d", level); > + values[2] = current_level; > + > + /* root branch is just starting root value */ > + if (show_branch) > + values[3] = start_with; > + > + /* construct the tuple */ > + tuple = BuildTupleFromCStrings(attinmeta, values); > + > + /* switch to long lived context while storing the tuple */ > + oldcontext = MemoryContextSwitchTo(per_query_ctx); > + > + /* now store it */ > + tuplestore_puttuple(tupstore, tuple); > + > + /* now reset the context */ > + MemoryContextSwitchTo(oldcontext); > + > + /* increment level */ > + level++; > + } > + > + for (i = 0; i < proc; i++) > + { > + StringInfo branchstr = NULL; > + > + /* start a new branch */ > + if (show_branch) > + { > + branchstr = makeStringInfo(); > + appendStringInfo(branchstr, "%s", branch); > + } > + > + /* get the next sql result tuple */ > + spi_tuple = tuptable->vals[i]; > + > + /* get the current key and parent */ > + current_key = SPI_getvalue(spi_tuple, spi_tupdesc, 1); > + current_key_parent = pstrdup(SPI_getvalue(spi_tuple, spi_tupdesc, 2)); > + > + /* get the current level */ > + sprintf(current_level, "%d", level); > + > + /* extend the branch */ > + if (show_branch) > + { > + appendStringInfo(branchstr, "%s%s", branch_delim, current_key); > + current_branch = branchstr->data; > + } > + else > + current_branch = NULL; > + > + /* build a tuple */ > + values[0] = pstrdup(current_key); > + values[1] = current_key_parent; > + values[2] = current_level; > + if (show_branch) > + values[3] = current_branch; > + > + tuple = BuildTupleFromCStrings(attinmeta, values); > + > + xpfree(current_key); > + xpfree(current_key_parent); > + > + /* switch to long lived context while storing the tuple */ > + oldcontext = MemoryContextSwitchTo(per_query_ctx); > + > + /* store the tuple for later use */ > + tuplestore_puttuple(tupstore, tuple); > + > + /* now reset the context */ > + MemoryContextSwitchTo(oldcontext); > + > + heap_freetuple(tuple); > + > + /* recurse using current_key_parent as the new start_with */ > + tupstore = build_tuplestore_recursively(key_fld, > + parent_key_fld, > + relname, > + branch_delim, > + values[0], > + current_branch, > + level + 1, > + max_depth, > + show_branch, > + per_query_ctx, > + attinmeta, > + tupstore); > + } > + } > + > + return tupstore; > + } > + > + /* > + * 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"); > + } > + > + /* check that the types of the first two columns match */ > + if (tupdesc->attrs[0]->atttypid != tupdesc->attrs[1]->atttypid) > + elog(ERROR, "Query-specified return tuple not valid for Connectby: " > + "first two columns must be the same type"); > + > + /* check that the type of the third column is INT4 */ > + if (tupdesc->attrs[2]->atttypid != INT4OID) > + elog(ERROR, "Query-specified return tuple not valid for Connectby: " > + "third column must be type %s", format_type_be(INT4OID)); > + > + /* check that the type of the forth column is TEXT if applicable */ > + if (show_branch && tupdesc->attrs[3]->atttypid != TEXTOID) > + elog(ERROR, "Query-specified return tuple not valid for Connectby: " > + "third column must be type %s", format_type_be(TEXTOID)); > + > + /* OK, the tupdesc is valid for our purposes */ > + } > + > + /* > + * Check if spi sql tupdesc and return tupdesc are compatible > + */ > + static bool > + compatConnectbyTupleDescs(TupleDesc ret_tupdesc, TupleDesc sql_tupdesc) > + { > + Oid ret_atttypid; > + Oid sql_atttypid; > + > + /* check the key_fld types match */ > + ret_atttypid = ret_tupdesc->attrs[0]->atttypid; > + sql_atttypid = sql_tupdesc->attrs[0]->atttypid; > + if (ret_atttypid != sql_atttypid) > + elog(ERROR, "compatConnectbyTupleDescs: SQL key field datatype does " > + "not match return key field datatype"); > + > + /* check the parent_key_fld types match */ > + ret_atttypid = ret_tupdesc->attrs[1]->atttypid; > + sql_atttypid = sql_tupdesc->attrs[1]->atttypid; > + if (ret_atttypid != sql_atttypid) > + elog(ERROR, "compatConnectbyTupleDescs: SQL parent key field datatype " > + "does not match return parent key field datatype"); > + > + /* OK, the two tupdescs are compatible for our purposes */ > + return true; > + } > + > + /* > * Check if two tupdescs match in type of attributes > */ > static bool > ! compatCrosstabTupleDescs(TupleDesc ret_tupdesc, TupleDesc sql_tupdesc) > { > int i; > Form_pg_attribute ret_attr; > *************** > *** 574,580 **** > ret_atttypid = ret_tupdesc->attrs[0]->atttypid; > sql_atttypid = sql_tupdesc->attrs[0]->atttypid; > if (ret_atttypid != sql_atttypid) > ! elog(ERROR, "compatTupleDescs: SQL rowid datatype does not match" > " return rowid datatype"); > > /* > --- 993,999 ---- > ret_atttypid = ret_tupdesc->attrs[0]->atttypid; > sql_atttypid = sql_tupdesc->attrs[0]->atttypid; > if (ret_atttypid != sql_atttypid) > ! elog(ERROR, "compatCrosstabTupleDescs: SQL rowid datatype does not match" > " return rowid datatype"); > > /* > *************** > *** 643,645 **** > --- 1062,1081 ---- > return tupdesc; > } > > + /* > + * Return a properly quoted identifier. > + * Uses quote_ident in quote.c > + */ > + static char * > + quote_ident_cstr(char *rawstr) > + { > + text *rawstr_text; > + text *result_text; > + char *result; > + > + rawstr_text = DatumGetTextP(DirectFunctionCall1(textin, CStringGetDatum(rawstr))); > + result_text = DatumGetTextP(DirectFunctionCall1(quote_ident, PointerGetDatum(rawstr_text))); > + result = DatumGetCString(DirectFunctionCall1(textout, PointerGetDatum(result_text))); > + > + return result; > + } > Index: contrib/tablefunc/tablefunc.h > =================================================================== > RCS file: /opt/src/cvs/pgsql-server/contrib/tablefunc/tablefunc.h,v > retrieving revision 1.2 > diff -c -r1.2 tablefunc.h > *** contrib/tablefunc/tablefunc.h 15 Aug 2002 02:51:26 -0000 1.2 > --- contrib/tablefunc/tablefunc.h 31 Aug 2002 05:47:51 -0000 > *************** > *** 34,38 **** > --- 34,39 ---- > */ > extern Datum normal_rand(PG_FUNCTION_ARGS); > extern Datum crosstab(PG_FUNCTION_ARGS); > + extern Datum connectby_text(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.2 > diff -c -r1.2 tablefunc.sql.in > *** contrib/tablefunc/tablefunc.sql.in 15 Aug 2002 02:51:26 -0000 1.2 > --- contrib/tablefunc/tablefunc.sql.in 31 Aug 2002 18:58:02 -0000 > *************** > *** 37,40 **** > > CREATE OR REPLACE FUNCTION crosstab(text,int) > RETURNS setof record > ! AS 'MODULE_PATHNAME','crosstab' LANGUAGE 'c' STABLE STRICT; > \ No newline at end of file > --- 37,48 ---- > > CREATE OR REPLACE FUNCTION crosstab(text,int) > RETURNS setof record > ! AS 'MODULE_PATHNAME','crosstab' LANGUAGE 'c' STABLE STRICT; > ! > ! CREATE OR REPLACE FUNCTION connectby(text,text,text,text,int,text) > ! RETURNS setof record > ! AS 'MODULE_PATHNAME','connectby_text' LANGUAGE 'c' STABLE STRICT; > ! > ! CREATE OR REPLACE FUNCTION connectby(text,text,text,text,int) > ! RETURNS setof record > ! AS 'MODULE_PATHNAME','connectby_text' LANGUAGE 'c' STABLE STRICT; > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
pgsql-patches by date: