As per the suggestions received, the syntax for the DATACUBE has 
been modified to

SELECT <field list><aggregate list>
INTO <destination table>
FROM <table expression>
WHERE <search condition>
GROUP BY CUBE <aggregate list>
HAVING <search condition>;

        Kindly note that the file datacube.c should be placed in src/backend/tcop/ 
and datacube.h should be in src/include. 

        Please let us know of further modifications that may be necessary.

Srikanth M
Sumit Kumar Mukherjee
*** ./src/backend/executor/spi.c.orig   2003-06-30 00:48:37.000000000 +0530
--- ./src/backend/executor/spi.c        2003-06-30 00:51:06.000000000 +0530
***************
*** 1046,1051 ****
--- 1046,1052 ----
                        case T_UpdateStmt:
                                origCmdType = CMD_UPDATE;
                                break;
+                       case T_CubeSelectStmt:          /*** DATACUBE ***/
                        case T_SelectStmt:
                                origCmdType = CMD_SELECT;
                                break;
*** ./src/backend/nodes/copyfuncs.c.orig        2003-06-30 00:48:48.000000000 +0530
--- ./src/backend/nodes/copyfuncs.c     2003-06-30 00:51:17.000000000 +0530
***************
*** 2944,2949 ****
--- 2944,2950 ----
                case T_UpdateStmt:
                        retval = _copyUpdateStmt(from);
                        break;
+               case T_CubeSelectStmt:                  /*** DATACUBE ***/
                case T_SelectStmt:
                        retval = _copySelectStmt(from);
                        break;
*** ./src/backend/nodes/equalfuncs.c.orig       2003-06-30 00:48:51.000000000 +0530
--- ./src/backend/nodes/equalfuncs.c    2003-06-30 00:51:25.000000000 +0530
***************
*** 2149,2154 ****
--- 2149,2155 ----
                case T_UpdateStmt:
                        retval = _equalUpdateStmt(a, b);
                        break;
+               case T_CubeSelectStmt:                  /*** DATACUBE ***/
                case T_SelectStmt:
                        retval = _equalSelectStmt(a, b);
                        break;
*** ./src/backend/nodes/outfuncs.c.orig 2003-06-30 00:48:55.000000000 +0530
--- ./src/backend/nodes/outfuncs.c      2003-06-30 00:51:31.000000000 +0530
***************
*** 1555,1560 ****
--- 1555,1561 ----
                        case T_NotifyStmt:
                                _outNotifyStmt(str, obj);
                                break;
+                       case T_CubeSelectStmt:          /*** DATACUBE ***/
                        case T_SelectStmt:
                                _outSelectStmt(str, obj);
                                break;
*** ./src/backend/parser/analyze.c.orig 2003-06-30 00:49:02.000000000 +0530
--- ./src/backend/parser/analyze.c      2003-06-30 01:12:44.000000000 +0530
***************
*** 304,309 ****
--- 304,310 ----
                        break;
  
                case T_SelectStmt:
+               case T_CubeSelectStmt:                  /*** DATACUBE ***/
                        if (((SelectStmt *) parseTree)->op == SETOP_NONE)
                                result = transformSelectStmt(pstate,
                                                                                       
  (SelectStmt *) parseTree);
*** ./src/backend/parser/gram.y.orig    2003-06-30 00:49:06.000000000 +0530
--- ./src/backend/parser/gram.y 2003-07-01 04:17:24.000000000 +0530
***************
*** 212,218 ****
                                any_operator expr_list dotted_name attrs
                                target_list update_target_list insert_column_list
                                insert_target_list def_list opt_indirection
!                               group_clause TriggerFuncArgs select_limit
                                opt_select_limit opclass_item_list trans_options
                                TableFuncElementList
                                prep_type_clause prep_type_list
--- 212,218 ----
                                any_operator expr_list dotted_name attrs
                                target_list update_target_list insert_column_list
                                insert_target_list def_list opt_indirection
!                               group_clause TriggerFuncArgs select_limit 
cube_group_clause     /*** DATACUBE ***/
                                opt_select_limit opclass_item_list trans_options
                                TableFuncElementList
                                prep_type_clause prep_type_list
***************
*** 331,337 ****
        CLUSTER COALESCE COLLATE COLUMN COMMENT COMMIT
        COMMITTED CONSTRAINT CONSTRAINTS CONVERSION_P CONVERT COPY CREATE CREATEDB
        CREATEUSER CROSS CURRENT_DATE CURRENT_TIME
!       CURRENT_TIMESTAMP CURRENT_USER CURSOR CYCLE
  
        DATABASE DAY_P DEALLOCATE DEC DECIMAL DECLARE DEFAULT
        DEFERRABLE DEFERRED DEFINER DELETE_P DELIMITER DELIMITERS
--- 331,337 ----
        CLUSTER COALESCE COLLATE COLUMN COMMENT COMMIT
        COMMITTED CONSTRAINT CONSTRAINTS CONVERSION_P CONVERT COPY CREATE CREATEDB
        CREATEUSER CROSS CURRENT_DATE CURRENT_TIME
!       CURRENT_TIMESTAMP CURRENT_USER CURSOR CYCLE CUBE                /*** DATACUBE 
***/
  
        DATABASE DAY_P DEALLOCATE DEC DECIMAL DECLARE DEFAULT
        DEFERRABLE DEFERRED DEFINER DELETE_P DELIMITER DELIMITERS
***************
*** 4234,4239 ****
--- 4234,4254 ----
                        into_clause from_clause where_clause
                        group_clause having_clause
                                {
+                                       SelectStmt *n = makeNode(SelectStmt); 
+                                       n->distinctClause = $2;
+                                       n->targetList = $3;
+                                       n->into = $4;
+                                       n->intoColNames = NIL;
+                                       n->fromClause = $5;
+                                       n->whereClause = $6;
+                                       n->groupClause = $7;
+                                       n->havingClause = $8;
+                                       $$ = (Node *)n;
+                               }
+                       | SELECT opt_distinct target_list
+                         into_clause from_clause where_clause
+                         cube_group_clause having_clause               /*** DATACUBE 
***/
+                               {
                                        SelectStmt *n = makeNode(SelectStmt);
                                        n->distinctClause = $2;
                                        n->targetList = $3;
***************
*** 4243,4248 ****
--- 4258,4264 ----
                                        n->whereClause = $6;
                                        n->groupClause = $7;
                                        n->havingClause = $8;
+                                       n->type = T_CubeSelectStmt; /*** DATACUBE ***/
                                        $$ = (Node *)n;
                                }
                        | select_clause UNION opt_all select_clause
***************
*** 4471,4476 ****
--- 4487,4502 ----
                        | /*EMPTY*/                                                    
         { $$ = NIL; }
                ;
  
+ /* -------------------------------------------------------
+  * cube_group_clause
+  *    
+  *                    The GROUP BY CUBE (...) clause for CUBE queries.
+  * -------------------------------------------------------
+  */
+ cube_group_clause:
+                       GROUP_P BY CUBE '(' expr_list ')'               { $$ = $5; }
+               ;
+ 
  having_clause:
                        HAVING a_expr                                                  
 { $$ = $2; }
                        | /*EMPTY*/                                                    
         { $$ = NULL; }
***************
*** 6995,7000 ****
--- 7021,7027 ----
                        | COPY
                        | CREATEDB
                        | CREATEUSER
+                       | CUBE          /*** DATACUBE ***/
                        | CURSOR
                        | CYCLE
                        | DATABASE
*** ./src/backend/parser/keywords.c.orig        2003-06-30 00:49:12.000000000 +0530
--- ./src/backend/parser/keywords.c     2003-06-30 00:51:47.000000000 +0530
***************
*** 88,93 ****
--- 88,94 ----
        {"createdb", CREATEDB},
        {"createuser", CREATEUSER},
        {"cross", CROSS},
+       {"cube", CUBE},                 /*** DATACUBE ***/
        {"current_date", CURRENT_DATE},
        {"current_time", CURRENT_TIME},
        {"current_timestamp", CURRENT_TIMESTAMP},
*** ./src/backend/tcop/Makefile.orig    2003-06-30 00:50:07.000000000 +0530
--- ./src/backend/tcop/Makefile 2003-06-30 00:52:28.000000000 +0530
***************
*** 12,18 ****
  top_builddir = ../../..
  include $(top_builddir)/src/Makefile.global
  
! OBJS= dest.o fastpath.o postgres.o pquery.o utility.o
  
  all: SUBSYS.o
  
--- 12,18 ----
  top_builddir = ../../..
  include $(top_builddir)/src/Makefile.global
  
! OBJS= dest.o fastpath.o postgres.o pquery.o utility.o datacube.o
  
  all: SUBSYS.o
  
*** ./src/backend/tcop/postgres.c.orig  2003-06-30 00:49:21.000000000 +0530
--- ./src/backend/tcop/postgres.c       2003-06-30 14:50:46.000000000 +0530
***************
*** 18,23 ****
--- 18,24 ----
   */
  
  #include "postgres.h"
+ #include "datacube.h"         /*** DATACUBE ***/
  
  #include <unistd.h>
  #include <signal.h>
***************
*** 69,74 ****
--- 70,82 ----
  extern int    optind;
  extern char *optarg;
  
+ /* ----------------
+  *            Variables for DATACUBE
+  * ----------------
+  */
+ int dropTable = 0;
+ char refTableName[50];
+ 
  char     *debug_query_string; /* for pgmonitor and
                                                                 * 
log_min_error_statement */
  
***************
*** 637,642 ****
--- 645,651 ----
                        case T_UpdateStmt:
                                origCmdType = CMD_UPDATE;
                                break;
+                       case T_CubeSelectStmt:          /*** DATACUBE ***/
                        case T_SelectStmt:
                                origCmdType = CMD_SELECT;
                                break;
***************
*** 803,808 ****
--- 812,852 ----
                                 */
                                Plan       *plan;
  
+                               /* -------------------------------------------------
+                                * DATACUBE:
+                                *      Depending on whether the original query is a 
+                                *      CUBE statement, the function constructCube is
+                                *      called. 
+                                *
+                                *      cubeTable is the temporary table generated 
using
+                                *      the gettimeofday() function and is used for
+                                *      displaying the final result.
+                                *
+                                *      dropTable is a flag which decides whether or 
not
+                                *      the temporary table is to be dropped.
+                                *
+                                * -------------------------------------------------
+                                */
+                               
+                               if(nodeTag(parsetree) == T_CubeSelectStmt && 
querytree->hasAggs == false)
+                                       elog(ERROR, "CUBE operator requires atleast 
one measure attribute [SUM, MAX, MIN, COUNT, AVG]");
+                               else if (nodeTag(parsetree) == T_CubeSelectStmt && 
querytree->hasAggs == true)
+                               {
+                                       char *cubeTable;
+                                       
+                                       cubeTable = palloc(sizeof(char)*30);
+                                       cubeTable = getTableName();
+                                  strcpy(refTableName, cubeTable);
+                                       dropTable = 1;
+ 
+                                       constructCube (querytree, TopMemoryContext, 
cubeTable);
+ #ifdef MEMORY_CONTEXT_CHECKING
+                             MemoryContextCheck(TopMemoryContext);
+ #endif
+ 
+                                       return;
+                               }
+ 
                                /*
                                 * Initialize snapshot state for query.  This has to
                                 * be done before running the planner, because it might
***************
*** 1924,1932 ****
--- 1968,1994 ----
                QueryCancelPending = false;
                CHECK_FOR_INTERRUPTS();
  
+               /* --------------------------------------------------
+                * DATACUBE:
+                *      The following if condition checks whether the
+                *      flag dropTable is set and if it is, drops the
+                *      temporary table created for the CUBE.
+                *
+                * --------------------------------------------------
+                */
+               if (dropTable == 1)
+               {
+                                 dropCubeTable (refTableName, TopMemoryContext);
+                                 dropTable = 0;
+ #ifdef MEMORY_CONTEXT_CHECKING
+                            MemoryContextCheck(TopMemoryContext);
+ #endif
+               }
+ 
                /*
                 * (3) read a command (loop blocks here)
                 */
+ 
                firstchar = ReadCommand(parser_input);
  
                /*
***************
*** 2204,2209 ****
--- 2266,2272 ----
                        tag = "UPDATE";
                        break;
  
+               case T_CubeSelectStmt:          /*** DATACUBE ***/
                case T_SelectStmt:
                        tag = "SELECT";
                        break;
*** ./src/backend/utils/adt/ruleutils.c.orig    2003-06-30 00:52:36.000000000 +0530
--- ./src/backend/utils/adt/ruleutils.c 2003-06-30 15:37:38.000000000 +0530
***************
*** 127,133 ****
                                                         StringInfo buf);
  static void make_ruledef(StringInfo buf, HeapTuple ruletup, TupleDesc rulettc);
  static void make_viewdef(StringInfo buf, HeapTuple ruletup, TupleDesc rulettc);
! static void get_query_def(Query *query, StringInfo buf, List *parentnamespace,
                          TupleDesc resultDesc);
  static void get_select_query_def(Query *query, deparse_context *context,
                                         TupleDesc resultDesc);
--- 127,133 ----
                                                         StringInfo buf);
  static void make_ruledef(StringInfo buf, HeapTuple ruletup, TupleDesc rulettc);
  static void make_viewdef(StringInfo buf, HeapTuple ruletup, TupleDesc rulettc);
! void get_query_def(Query *query, StringInfo buf, List *parentnamespace,
                          TupleDesc resultDesc);
  static void get_select_query_def(Query *query, deparse_context *context,
                                         TupleDesc resultDesc);
***************
*** 165,171 ****
                                 StringInfo buf);
  static bool tleIsArrayAssign(TargetEntry *tle);
  static char *generate_relation_name(Oid relid);
! static char *generate_function_name(Oid funcid, int nargs, Oid *argtypes);
  static char *generate_operator_name(Oid operid, Oid arg1, Oid arg2);
  static char *get_relid_attribute_name(Oid relid, AttrNumber attnum);
  
--- 165,171 ----
                                 StringInfo buf);
  static bool tleIsArrayAssign(TargetEntry *tle);
  static char *generate_relation_name(Oid relid);
! char *generate_function_name(Oid funcid, int nargs, Oid *argtypes);
  static char *generate_operator_name(Oid operid, Oid arg1, Oid arg2);
  static char *get_relid_attribute_name(Oid relid, AttrNumber attnum);
  
***************
*** 1262,1268 ****
   * the view represented by a SELECT query.
   * ----------
   */
! static void
  get_query_def(Query *query, StringInfo buf, List *parentnamespace,
                          TupleDesc resultDesc)
  {
--- 1262,1268 ----
   * the view represented by a SELECT query.
   * ----------
   */
! void
  get_query_def(Query *query, StringInfo buf, List *parentnamespace,
                          TupleDesc resultDesc)
  {
***************
*** 3146,3152 ****
   *
   * The result includes all necessary quoting and schema-prefixing.
   */
! static char *
  generate_function_name(Oid funcid, int nargs, Oid *argtypes)
  {
        HeapTuple       proctup;
--- 3146,3152 ----
   *
   * The result includes all necessary quoting and schema-prefixing.
   */
! char *
  generate_function_name(Oid funcid, int nargs, Oid *argtypes)
  {
        HeapTuple       proctup;
*** ./src/include/nodes/nodes.h.orig    2003-06-30 00:49:40.000000000 +0530
--- ./src/include/nodes/nodes.h 2003-06-30 00:50:40.000000000 +0530
***************
*** 203,208 ****
--- 203,209 ----
        T_PrepareStmt,
        T_ExecuteStmt,
        T_DeallocateStmt,
+       T_CubeSelectStmt,               /*** DATACUBE ***/
  
        T_A_Expr = 700,
        T_ColumnRef,
DATACUBE operator for PostgreSQL.
 Copyright 2003, Srikanth M <[EMAIL PROTECTED]> and Sumit Kumar Mukherjee <[EMAIL 
PROTECTED]>

DATACUBE is an operator which generalizes relational aggregates. Since this is useful 
in building datawarehouses for OLAP (Online Analytical Processing), we felt the need 
to implement this in PostgreSQL. Oracle and MySQL already have one.

In keeping with this, we first define the syntax of a CUBE statement as follows:

SELECT <field list><aggregate list>
INTO <destination table>
FROM <table expression>
WHERE <search condition>
GROUP BY CUBE <aggregate list>
HAVING <search condition>;

Of the above, SELECT, FROM and GROUP BY CUBE fields are mandatory. Depending on 
whether the querytree has an INTO structure, we redirect the result of the CUBE query 
to the required table.

Example:
--------

Let the fact table be pets:

type  | store  | number | cost
--------+--------+--------+------
Turtle | Tampa  |      4 |   20
Dog    | Tampa  |     14 |   20
Cat    | Naples |      9 |   17
Dog    | Naples |      5 |   20
Turtle | Naples |      1 |   20
Dog    | Miami  |     12 |   13
Cat    | Miami  |     18 |   13

The CUBE query can be given as:

db=# SELECT type, store, sum(number) FROM pets GROUP BY CUBE (type, store);
type  | store  | sum
--------+--------+-----
Cat    | Miami  |  18
Cat    | Naples |   9
Cat    |        |  27
Dog    | Miami  |  12
Dog    | Naples |   5
Dog    | Tampa  |  14
Dog    |        |  31
Turtle | Naples |   1
Turtle | Tampa  |   4
Turtle |        |   5
       | Miami  |  30
       | Naples |  15
       | Tampa  |  18
       |        |  63
(14 rows)

NOTE:
-----

a)      As of now the CUBE operator works only for queries with a CUBE operator in the 
outermost level. That is, a query like

        SELECT type, store, max(number) FROM pets GROUP BY CUBE (type, store); 

        is valid. But a query with a nested CUBE operator like in

        SELECT * FROM pets WHERE (type, store, number) IN (SELECT type, store, 
avg(number) FROM pets GROUP BY CUBE (type, store));

        is invalid.
 
b) Also, the NULL fields that can be seen in the result actually represent ALL. So one 
should set null to ALL before running the query to get an exact idea. We would be glad 
if someone could come up with a modification so that the null display is set to ALL 
when a CUBE query is called and then reset to normal once execution is done.

c) The current algorithm follows a naive method of generating a UNION of SELECT 
queries to obtain the CUBE. An even more efficient method is currently being devised 
and we hope to complete it by the next release.

The Implementation:
-------------------

        This section is for those who are interested in the logic behind the 
implementation of the CUBE operator. Further changes/suggestions are welcome.
        
        Just before the execution of the query starts (in postgres.c), we check if the 
current querytree has the T_CubeSelectStmt nodeTag. If it does, then we invoke the 
function constructCube defined in src/backend/tcop/datacube.c
        
        constructCube is thhe main routine which handles the generation of the 
DATACUBE.        This function takes in as input the querytree, the TopMemoryContext 
(used while invoking pg_exec_query_string), and a temporary table name generated using 
gettimeofday(). The main processes which take place in this routine are:
 
                a) Retrieve the actual query string from the querytree using the 
function get_query_def defined in ruleutils.c
                b) Create a table with the generated name with the result of the 
actual query WITHOUT the CUBE part.
                c) Find out the attributes in the targetlist and extract the 
aggregatelist into aggregateAttrsList.
                d)      Generate the possible combinations of attributes possible 
using the function generateCombinations().
                e) Using these generated combinations, construct the actual query to 
be run on the table generated in (b).
                f) Execute this query. If the original query had an 'into',     save 
the result of the CUBE query to that particular table.
                        Else display the result to the user.

        For further details, please have a look at the source code in datacube.c
/*********************************************************
 * datacube.c 
 *              Utility to build a datacube in PostgreSQL. 
 *
 *              Currently does not support nested CUBE queries.
 *
 ********************************************************/

#include <sys/time.h>
#include "postgres.h"
#include "datacube.h"
#include "utils/memutils.h"
#include "tcop/tcopprot.h"
#include "parser/parse_expr.h"

/* ----------------
 * Global variables
 * ----------------
 */
int m, Ind = 0, numAtts = 0, **Combinations;
char **c, **projectedAttrs, cubeTableName[30];

/* -----------------------------------------------
 * nCr
 * 
 *      Function to calculate the number of ways of
 *      selecting r objects from n objects. 
 *
 * ----------------------------------------------
 */
int nCr (int n, int r)
{
        int num = 1, den = 1, i;
        for (i = n; i >= n-r+1; i--)
                num *= i;
        for (i = r; i >= 1; i--)
                den *= i;
        
        return num/den;
}

/* ----------------------------------------------
 * getMaxCombinations
 *
 *              This calls the function nCr for values
 *              of r ranging from 0 to n. The total value
 *              of their sum is the maximum number of
 *              combinations possible for given values of
 *              n and r.
 *
 * This value is required to decide the size of the
 * array required to store the different combinations.
 * 
 * ----------------------------------------------
 */
int getMaxCombinations(int n)
{
        int r = 0;
        int NumCombinations = 0;
        for (r = 0; r <= n; r++)
                NumCombinations += nCr(n,r);

        return NumCombinations;
}

/* ----------------------------------------------
 * Combination
 *
 *      The actual function where the combinations
 *      are built and stored into the global array
 *      called Combinations. This array is later on
 *      used to generate the actual query for the
 *      DATACUBE.
 *      
 * ----------------------------------------------
 */
void Combination(char **arr ,int n, int x, int r)
{
        int i, j;
        static int y = -1;
        y++;
        for(i = x; i < n; ++i)
        {
                c[y] = (char *) malloc( sizeof (arr[i]) );
                strcpy (c[y], arr[i]);
                
                if(n == m)
                {
                        for (j = 0; j < r; j++)
                                Combinations[Ind][getIndex(c[j])] = 1;
                        Ind++;
                }
                else
                        Combination (arr+1, n-1, i, r);
        }
        y--;
}

/* ----------------------------------------------
 * getIndex
 *
 *      A small function to find the index of the
 *      location of the generated combination in the
 *      actual array.
 *
 * Never mind if you didnt understand what is
 * written above.
 *
 * ----------------------------------------------
 */
int getIndex(char *str)
{
        int i = 0;
        for( i = 0; i <= numAtts; i++)
                if ( strcmp(projectedAttrs[i],str) == 0 )
                        return i;
        return -1;
}

/* ----------------------------------------------
 * generateCombinations
 *
 *      A simple function which initialises the global
 *      array Combinations
 *      
 * ----------------------------------------------
 */
int generateCombinations (void)
{
        int length, i, j;
        
        int NumCombinations = getMaxCombinations(numAtts);
        
        Combinations = (int **)malloc(sizeof(int*) * NumCombinations);

        for(i = 0; i < NumCombinations; i++)
        {
                Combinations[i] = (int *) malloc(sizeof(int)*numAtts);
                for(j = 0; j < numAtts; j++)
                        Combinations[i][j] = 0;
        }
        
        for(length = 0; length <= numAtts; length++)
        {
                m = numAtts-length+1;
                c = (char **) malloc(length*sizeof(char*));
                Combination(projectedAttrs,numAtts,0,length);
        }

        return NumCombinations;
}

/* ----------------------------------------------
 * generateQuery
 *
 *      This is the function where the actual query
 *      generation takes place. Depending on the
 *      current combination in the Combinations array,
 *      the query is generated taking the attribute
 *      name if the value for that particular attribute
 *      is 1 or using the same attribute as NULL if 0.
 *
 * ex: Suppose the attributes are type, store. Let
 *      the current combination be 01. Then the targetlist
 *      is NULL AS type, store. Similarly, for 10 it is
 *      type, NULL AS store
 *
 *      Returns the generated query to the main loop.
 *
 * ----------------------------------------------
 */
StringInfo generateQuery(int numSelect, char *aggregateAttrsList)
{
        StringInfo cubeQueryString = makeStringInfo();
        int i, j, k, flag = 0;

        for (i = 0; i < numSelect; i++)
        {
                flag = 0;
                appendStringInfo(cubeQueryString, "SELECT ");

                for (j = 0; j < numAtts; j++)
                {
                        if (Combinations[i][j] == 1)
                                appendStringInfo(cubeQueryString, "%s, ", 
projectedAttrs[j]);
                        else if (Combinations[i][j] == 0)
                                appendStringInfo(cubeQueryString, "NULL AS %s, ", 
projectedAttrs[j]);

                }
                appendStringInfo(cubeQueryString, "%s FROM %s", aggregateAttrsList, 
cubeTableName);

                for (j = 0; j < numAtts; j++)
                        if (Combinations[i][j] == 1)
                                flag = 1;
                if (flag != 0)
                {
                        appendStringInfo(cubeQueryString, " GROUP BY ");
                        for (j = 0; j < numAtts; j++)
                        {
                                flag = 0;
                                if (Combinations[i][j] == 1)
                                {
                                        appendStringInfo(cubeQueryString, 
projectedAttrs[j]);

                                        for (k = j+1; k < numAtts; k++)
                                                if (Combinations[i][k] == 1)
                                                        flag = 1;
                                        if (flag == 1)
                                                appendStringInfo(cubeQueryString, ", 
");
                                }
                        }
                }
                if (i != numSelect-1)
                        appendStringInfo(cubeQueryString, " UNION ");
        }
        
        return cubeQueryString;
}

/* ----------------------------------------------
 * executeQuery
 * 
 *      Routine to handle the execution of the query
 *      whose query string is passed along with the
 *      TopMemoryContext.
 *
 *      This invokes a call to pg_exec_query_string.
 *
 * ----------------------------------------------
 */
void executeQuery(StringInfo buf, MemoryContext context)
{
        MemoryContext CubeContext;
        
        CubeContext = AllocSetContextCreate(context, "CubeContext", 
ALLOCSET_DEFAULT_MINSIZE, 
                                                ALLOCSET_DEFAULT_INITSIZE, 
ALLOCSET_DEFAULT_MAXSIZE);

        pg_exec_query_string(buf, 2, CubeContext);
}

/* ----------------------------------------------
 * getTableName
 *
 *      Our function needs to create a temporary table
 *      to generate the final result. To avoid any
 *      clashes/errors, a table name is generated using
 *      the function gettimeofday().
 *
 *      The name of the table so generated is returned.
 *
 * ----------------------------------------------
 */
char* getTableName(void)
{
        struct timeval tval;
        struct timezone tz;
        char *timestr;

        timestr = palloc(sizeof(char)*30);
        
        tz.tz_minuteswest = 0;
        tz.tz_dsttime = 0;

        if(gettimeofday(&tval, &tz) != 0)
                          elog(ERROR, "Could not get time of day");
        else
                          sprintf(timestr, "cube_%ld_%ld",tval.tv_sec,tval.tv_usec);

        return timestr;
}

/* ------------------------------------------------------------------
 * constructCube
 *
 *      The main routine which handles the generation of the DATACUBE. 
 *      This function is invoked from   postgres.c if it is found that the 
 *      current query has a CubeSelectStmt (defined by us) tag.
 *
 *      The main processes which take place are
 *
 *              a) Retrieve the actual query string from the querytree using
 *                      the function get_query_def defined in ruleutils.c
 *              b) Create a table with the generated name with the result of
 *                      the actual query WITHOUT the CUBE part.
 *              c) Find out the attributes in the targetlist and extract the
 *                      aggregatelist into aggregateAttrsList.
 *              d)      Generate the possible combinations of attributes possible
 *                      using the function generateCombinations().
 *              e) Using these generate combinations, construct the actual 
 *                      query to be run on the table generated in (b).
 *              f) Execute this query. If the original query had an 'into',
 *                      save the result of the CUBE query to that particular table.
 *                      Else display the result to the user.
 *
 * ------------------------------------------------------------------
 */
void constructCube(Query *querytree, MemoryContext context, char *cubeTable)
{
        StringInfo buf = makeStringInfo();
        StringInfo createTableQuery = makeStringInfo();
        StringInfo cubeQuery = makeStringInfo();
        StringInfo intoCubeQuery = makeStringInfo();

        List *l;

        char *aggregateAttrsList, intoRelation[30];
        int i = 0, count = 0, numSelect = 0;
        numAtts = 0, Ind = 0;

        //Retrieve query string from querytree
        get_query_def(querytree, buf, NIL, NULL);       

        strcpy (cubeTableName,cubeTable);
        
        appendStringInfo(createTableQuery, "CREATE TABLE %s AS %s", cubeTableName, 
buf->data);
        executeQuery(createTableQuery, context);
        
        foreach(l, querytree->targetList)
        {
                TargetEntry *tle = (TargetEntry *) lfirst(l);
                if(tle->resdom->ressortgroupref > 0)
                        numAtts++;
        }

        projectedAttrs = palloc( sizeof(char *) * numAtts );
        aggregateAttrsList = palloc (sizeof (char) * 1000);

        foreach(l, querytree->targetList)
        {
                char *funcname;
                TargetEntry *tle = (TargetEntry *) lfirst(l);
                if(tle->resdom->ressortgroupref > 0)
                {
                        projectedAttrs[i] = (char *)malloc(sizeof(char) * 
sizeof(tle->resdom->resname));
                        projectedAttrs[i] = tle->resdom->resname;
                        i++;
                }
                
                if(nodeTag(tle->expr) == T_Aggref ) 
                {
                        Aggref *aggref = (Aggref *) tle->expr;
                        Oid argtype = exprType(aggref->target);
                        
                        funcname = generate_function_name(aggref->aggfnoid, 1, 
&argtype);
                        
                        if(count == 0)
                                sprintf(aggregateAttrsList,"%s(%s)", 
funcname,tle->resdom->resname);
                        else
                                sprintf(aggregateAttrsList,"%s, %s(%s)", 
aggregateAttrsList, funcname,tle->resdom->resname);
                        count++;
                }
        }

        numSelect = generateCombinations();
        cubeQuery = generateQuery(numSelect, aggregateAttrsList);

        if (querytree->into != NULL)
        {
                          strcpy (intoRelation, querytree->into->relname);
                          intoCubeQuery->data = palloc(sizeof(char));
                          appendStringInfo(intoCubeQuery, "CREATE TABLE %s AS %s", 
intoRelation, cubeQuery->data);
                          executeQuery(intoCubeQuery, context);
        }
        else
                          executeQuery(cubeQuery, context);
}

/* ----------------------------------------------
 * dropCubeTable
 *
 *      This routine is used to drop the temporary table
 *      created using getTableName().
 *
 * ----------------------------------------------
 */
void dropCubeTable (char *tableName, MemoryContext context)
{
                  StringInfo dropTableQuery = makeStringInfo();

                  appendStringInfo(dropTableQuery, "DROP TABLE %s", tableName);
                  executeQuery(dropTableQuery, context);
}
#ifndef DATACUBE_H
#define DATACUBE_H

#include "nodes/parsenodes.h"
#include "lib/stringinfo.h"
#include "access/tupdesc.h"

extern int nCr (int n, int r);
extern int getMaxCombinations(int n);
extern void Combination(char **arr ,int n, int x, int r);
extern int getIndex(char *str);
extern int generateCombinations (void);
extern StringInfo generateQuery(int numSelect, char *aggregateAttrsList);
extern void executeQuery(StringInfo buf, MemoryContext context);
extern char* getTableName(void);
extern void constructCube(Query *querytree, MemoryContext context, char *cubeTable);
extern void dropCubeTable(char *tableName, MemoryContext context);
extern void get_query_def(Query *query, StringInfo buf, List *parentnamespace, 
TupleDesc resultDesc);
extern char *generate_function_name(Oid funcid, int nargs, Oid *argtypes);

#endif  /* DATACUBE_H */
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Reply via email to